The WHERE clause is useful for creating sparse join indexes.
- WHERE search_condition
- A conditional expression to eliminate all rows from the SELECT clause query that do not evaluate to TRUE.
- You can base an expression on a UDT column or an expression that references at least one column with the exception of the following: expressions that involve aggregate or OLAP functions, UDF expressions, and built-in functions that are explicitly prohibited such as DEFAULT and PARTITION. For details about these functions, see SQL Functions, Operators, Expressions, and Predicates, B035-1145. The expressions must be aliased. Otherwise, the system returns an error to the requestor.
- Multiple join conditions must be connected using the AND logical operator.
- Data types for any columns used in a join condition must be drawn from the same domain because neither explicit nor implicit data type conversions are permitted.
- You cannot specify the ROWID keyword in the search condition of a sparse join index definition.
- You must drop any join indexes created prior to Teradata Database V2R6.2 that contain the keyword ROWID in their definition, then recreate them using currently valid syntax.
- If you specify a join condition on a UDT column, the specified tables must be joined on an equality condition. Otherwise, Teradata Database returns an error to the requestor.
- The following rules apply to specifying inequality operators in a WHERE clause search condition.
- Inequality conditions between columns from the same table are always valid.
- Inequality conditions between columns from different tables are supported only if they are ANDed to at least one equality condition.
- Inequality conditions can be specified only for columns having the same data type in order to enforce domain integrity.
- You can only specify a join condition using an inequality operator if you specify multiple join conditions and use the AND logical operator to connect the inequality join condition or conditions with an equality join condition.
- You can specify an equality or inequality join condition between two tables based on an expression if you specify multiple join conditions between the two tables and use the AND logical operator to connect the join condition that is specified using an expression or columnar expressions with an equality join condition between columns from the two tables.
- The only valid comparison operators for an inequality condition are the following.
See SQL Data Manipulation Language, B035-1146 for complete documentation of the WHERE clause.