SQL Predicates: use in COBOL DB2 Queries

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

Read DB2 blogs: Click Here IBM DB2 Manual: Click Here