You can think of a conditional expression as a collection of logical predicate primitives where the order of evaluation is controlled by the use of the logical operators AND, OR, and NOT and by the placement of parentheses.
Superficially similar conditional expressions can produce radically different results depending on how you group their component primitives, so use caution in planning the logic of any conditional expressions.
SQL supports the logical predicate primitives listed in the following table. Note that Match and Unique conditions are not supported.
| Logical Predicate Primitive Condition | SQL Logical Predicate | Function |
|---|---|---|
| Comparison | For a complete list of SQL comparison operators, see Supported Comparison Operators. | Tests for equality, inequality, or magnitude difference between two data values. |
| Range | BETWEEN NOT BETWEEN |
Tests whether a data value is included within (or excluded from) a specified range of column data values. |
| Like | LIKE | Tests for a pattern match between a specified character string and a column data value. |
| In | IN NOT IN |
Tests whether a data value is (or is not) a member of a specified set of column values. IN is equivalent to = ANY. NOT IN is equivalent to <> ALL. |
| All | ALL | Tests whether a data value compares TRUE to all column values in a specified set. |
| Any | ANY SOME | Tests whether a data value compares TRUE to any column value in a specified set. |
| Exists | EXISTS NOT EXISTS |
Tests whether a specified table contains at least one row. |
| Period predicates | For a complete list of period predicate operators, see "Period Functions and Operators" in Teradata Vantage™ - Data Types and Literals, B035-1143. | Operates on:
Evaluates to TRUE, FALSE, or UNKNOWN. |
| OVERLAPS | Tests whether two time periods, including derived periods, overlap | |
| IS UNTIL_CHANGED IS NOT UNTIL_CHANGED |
Tests whether the ending bound of a Period expression, including a derived period is (or is not) UNTIL_CHANGED. |