SQL predicates, also referred to as conditional expressions, specify a condition of a row or group that has one of three possible states: TRUE, FALSE NULL (or unknown). SQL Predicates are found on the tail end of clauses, functions, and SQL expressions inside of existing query statements
As a rule, a query will return the same result regardless of the sequence in which the various predicates are specified. However, note the following:
SQL provides the following logical predicates:
Employee Table Emp ID Dept Name Dept ID Salary OldEmpID 1 Sales 10 5000 10 2 Sales 10 10000 11 3 IT 20 8000 20 4 Marketing 30 12000 30 5 IT 20 15000 21 6 HR 40 15000 40 7 CSR IT 50 4000 50 8 IT 20 8000 -- 9 IT 20 20000 23 10 Marketing 30 8000 --
ANY: Compare operator with the ANY quantifier to select the employee ID and department name of anyone in department ID 10, 20, and 30.
SELECT EmpID, DeptName FROM Employee WHERE DeptID = ANY (10,20,30) WITH UR; OR SELECT EmpID, DeptName FROM Employee WHERE (DeptID = 10) OR (DeptID = 20) OR (DeptID = 30) WITH UR; OR SELECT EmpID, DeptName FROM Employee WHERE DeptID IN (10,20,30) WITH UR; Result: Emp ID Dept Name 1 Sales 2 Sales 3 IT 4 Marketing 5 IT 8 IT 9 IT 10 Marketing
ALL: Compare operator with the ALL quantifier to select the employee ID and department name.
SELECT EmpID, DeptName FROM Employee WHERE (Salary) >= ALL (SELECT Salary FROM Employee WHERE EmpID = 2) WITH UR;
SOME: Compare operator with the SOME quantifier to select the employee ID and department name.
SELECT EmpID, DeptName FROM Employee WHERE (Salary) > = SOME (SELECT Salary FROM Employee WHERE EmpID = 2) WITH UR;
HAVING: Search condition in a HAVING clause to select from the Employee table those departments with the numbers 10, 20, and 30 and with a salary average of at least $10,000 but not more than $20,000.
SELECT AVG(Salary) FROM Employee WHERE DeptID IN (10,20,30) GROUP BY DeptID HAVING AVG(Salary) BETWEEN 10000 AND 20000 WITH UR;
CONTAINS: This operator is used in the WHERE clause
SELECT * FROM employee WHERE DeptID CONTAINS OldEmpID; Result: Emp ID Dept Name Dept ID Salary OldEmpID 1 Sales 10 5000 10 3 IT 20 8000 20 4 Marketing 30 12000 30 6 HR 40 15000 40 7 CSR IT 50 4000 50
LIKE :
SELECT EmpID, DeptName, DeptID FROM Employee WHERE DeptName LIKE '%IT%' ; Result: Emp ID Dept Name Dept ID 3 IT 20 7 CSR IT 50
[NOT] EXISTS: Consider Employee_Master doesn’t have entries for EmpID 6-10
SELECT * FROM Employee_Master WHERE EXISTS (SELECT * FROM Employee WHERE Employee_Master.EmpID=Employee.EmpID) WITH UR; Result: Emp ID Dept Name Dept ID Salary OldEmpID 1 Sales 10 5000 10 2 Sales 10 10000 11 3 IT 20 8000 20 4 Marketing 30 12000 30 5 IT 20 15000 21 SELECT * FROM Employee WHERE NOT EXISTS (SELECT * FROM Employee_Master WHERE Employee.EmpID=Employee_Master.EmpID) WITH UR; Result: Emp ID Dept Name Dept ID Salary OldEmpID 6 HR 40 15000 40 7 CSR IT 50 4000 50 8 IT 20 8000 22 9 IT 20 20000 23 10 Marketing 30 8000 31
[NOT] IN :
SELECT * FROM Employee_Master WHERE IN (SELECT * FROM Employee WHERE Employee_Master.EmpID=Employee.EmpID) WITH UR; Result: Emp ID Dept Name Dept ID Salary OldEmpID 1 Sales 10 5000 10 2 Sales 10 10000 11 3 IT 20 8000 20 4 Marketing 30 12000 30 5 IT 20 15000 21 SELECT * FROM Employee_Master WHERE NOT IN (SELECT * FROM Employee WHERE Employee_Master.EmpID=Employee.EmpID) WITH UR; Result: Emp ID Dept Name Dept ID Salary OldEmpID 6 HR 40 15000 40 7 CSR IT 50 4000 50 8 IT 20 8000 22 9 IT 20 20000 23 10 Marketing 30 8000 31
IS [NOT] NULL :
SELECT EmpID FROM Employee WHERE OldEmpID IS NULL; Result: Emp ID Dept Name Dept ID Salary OldEmpID 8 IT 20 8000 -- 10 Marketing 30 8000 -- SELECT EmpID FROM Employee WHERE OldEmpID IS NOT NULL; Result: Emp ID Dept Name Dept ID Salary OldEmpID 1 Sales 10 5000 10 2 Sales 10 10000 11 3 IT 20 8000 20 4 Marketing 30 12000 30 5 IT 20 15000 21 6 HR 40 15000 40 7 CSR IT 50 4000 50 9 IT 20 20000 23
Optimize Outer Join queries is a powerful tool for retrieving data from multiple tables. However,…
OUTER JOIN Queries are a valuable tool in SQL, allowing you to retrieve data from…
Mastering impediments is crucial to the Scrum Master's role in fostering a high-performing Scrum Team.…
A Sprint Goal is a short, clear, and motivating target the Scrum Team aims to…
Context switching refers to changing focus or shifting attention from one task or activity to…
Embracing change is never easy, especially when it involves a shift in work methodology. Motivate…