15.00 - SQL Predicates - Teradata Database

SQL Predicates

SQL predicates, also referred to as logical predicates, are types of conditional expressions. They specify a condition of a row or group that has one of three possible states:

TRUE

FALSE

NULL (or unknown)

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 expressions in stored procedures

Types of Logical 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 Predicates

NOT

AND

OR

Predicate Quantifiers

SOME

ANY

ALL

Examples of Predicates

Predicate	Description
SELECT * FROM Employee WHERE Salary < 40000;	Predicate in a WHERE clause specifying a condition for selecting rows from the Employee table.
SELECT SUM(CASE   WHEN part BETWEEN 100 AND 199   THEN 0   ELSE cost   END) FROM Orders;	Predicate in a CASE expression specifying a condition that determines the value passed to the SUM function for a particular row in the Orders table.