Conditional Expressions as a Collection of Logical Primitives

Teradata Vantage™ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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:
  • Two Period expressions
  • Two derived periods
  • One Period expression and one derived period
  • One Period expression and one DateTime expression

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.