Purpose
Filters rows that satisfy a conditional expression in SELECT, DELETE, INSERT, UPDATE, ABORT, and ROLLBACK statements.
Syntax Elements
- WHERE
- An introduction to the search condition in the SELECT statement.
-
search_condition
- A conditional search expression, also referred to as a conditional expression or predicate, that must be satisfied by the row or rows returned by the statement.
- The arguments can be any valid SQL expression, including individual values, user-defined functions, DEFAULT functions, and subqueries, but the overall expression must be of a form that returns a single boolean (TRUE or FALSE) result.
- Logical expressions include comparisons of numeric values, character strings, and partial string comparisons.
- You cannot specify an expression that returns an ARRAY or VARRAY data type in a WHERE clause search condition. For more information about relational operators and expressions, see
Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145.
- Scalar subqueries are valid search conditions.
- You cannot specify expressions that contain LOBs in a search condition unless you first cast them to another data type (for example, casting a BLOB to BYTE or VARBYTE or a CLOB to CHARACTER or VARCHAR) or pass them to a function whose result is not a LOB.
- Subqueries in a search condition cannot specify SELECT AND CONSUME.
- You can only specify a scalar UDF for search_condition if it is invoked within an expression and returns a value expression.
- If you specify the value for a row-level security constraint in a search condition, it must be expressed in its encoded form.
ANSI Compliance
The WHERE clause is ANSI SQL:2011-compliant.
Related Topics
For more information about the use of the WHERE clause, see: