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
SQL Predicates Categories
- Basic Predicate – A basic predicate compares two values or compares a set of values with another set of values. ( = , <>,<,>,<=,>=)
- Quantified Predicate – A quantified predicate compares a value or values with a collection of values. (ALL, SOME, ANY)
- ARRAY_EXISTS Predicate – The ARRAY_EXISTS predicate tests for the existence of an array element with the specified index in an array. (array-expression, array-index)
- BETWEEN Predicate – The BETWEEN predicate determines whether a given value lies between two other given values that are specified in ascending order. (BETWEEN, NOT BETWEEN)
- DISTINCT Predicate – A distinct predicate compares a value with another value or a set of values with another set of values. (IS DISTINCT, IS NOT DISTINCT FROM)
- EXISTS Predicate – The EXISTS predicate tests for the existence of certain rows. The full-select can specify any number of columns and can result in true or false. (EXISTS, NOT EXISTS)
- IN Predicate – The IN predicate compares a value or values with a set of values. (IN, NOT IN)
- LIKE Predicate – The LIKE predicate searches for strings that have a certain pattern. (LIKE)
- NULL Predicate – The NULL predicate tests for null values. (IS NULL, IS NOT NULL)
SQL Predicates can appear in the following
- WHERE, ON, or HAVING clause to qualify or disqualify rows in a SELECT statement.
- WHEN clause search condition of a searched CASE expression
- CASE_N function
- IF, WHILE, REPEAT, and CASE statements in stored procedures
SQL Predicate Rules & Precedence
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:
- Predicates are evaluated after the expressions that are operands of the predicate.
- All values that are specified in the same predicate must be compatible.
Except for the EXISTS predicate, a subquery in a predicate must specify a single column unless the operand on the other side of the comparison operator is a fullselect. - The value of a host variable can be null (that is, the variable can have a negative indicator variable).
- Predicates separated by an OR may need parenthesis.
- Checks specified in a CASE statement are done in the order written
Types of Logical SQL Predicates
SQL provides the following logical predicates:
- Comparison operators
- [NOT] BETWEEN
- LIKE
- [NOT] IN
- [NOT] EXISTS
- OVERLAPS
- IS [NOT] NULL
Logical Operators that Operate on SQL Predicates
- NOT
- AND
- OR
SQL Predicate Quantifiers
- SOME
- ANY
- ALL
SQL Predicate Examples
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