Using the Relational Data Framework › Use Comparison Predicates in WHERE Clauses

Use Comparison Predicates in WHERE Clauses

A comparison predicate is an expression that compares a column name to one of these values:

• Another column name
• A character string
• A numeric string
• A hexadecimal string
• A host variable name
• NULL

Operators for Comparison Predicates

A WHERE clause containing a comparison predicate uses this syntax:

WHERE colname relationaloperator value

The relationaloperator can be any of the following:

• = (equal)
• <> (not equal)
• > (greater than)
• >= (greater than or equal to)
• < (less than)
• <= (less than or equal to)

Example: Comparison Predicate

Suppose that you invoke this SELECT statement:

SELECT * FROM SYSTEMS WHERE CURRENT_STATE = 'UP'

In this statement, the search criteria is the predicate CURRENT_STATE = 'UP'. This predicate instructs SQL to select all rows in the SYSTEMS table where the predicate is true-that is, where the value in the CURRENT_STATE column equals UP. In the SYSTEMS table, the predicate is true only for the row containing information about IMS, so only that row is selected.

Now suppose that you alter the predicate in the SELECT statement above so that it becomes true when the value in the CURRENT_STATE column is not UP. Your revised statement might look like this:

SELECT * FROM SYSTEMS WHERE CURRENT_STATE <> 'UP'

The predicate in this new statement tells SQL to select all rows where the CURRENT_STATE column contains a value other than UP. In the SYSTEMS table, this predicate is true only for the row containing CICS information. Therefore, SQL selects only that row when it executes the revised SELECT statement.