Usage Notes - Teradata Database

Aggregates in a WHERE Clause

Aggregates are not allowed in a WHERE clause except when the clause is part of a correlated subquery inside a HAVING clause, and the aggregate is applied on outer variables.

Expressions Containing LOBs

You cannot specify expressions that contain LOBs in a search condition unless you first cast them to another 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.

UDT Comparisons

ARRAY or VARRAY Data Type

You cannot specify an expression that evaluates to an ARRAY or VARRAY data type in a WHERE clause. For more information about ARRAY operators and expressions, see SQL Functions, Operators, Expressions, and Predicates .

Row-Level Security Constraint Values

If you specify the value for a row-level security constraint in a search condition, that value must be expressed in its encoded form.

SELECT AND CONSUME Subqueries

Subqueries in a search condition cannot contain SELECT AND CONSUME.

DEFAULT Function in a Search Condition

The DEFAULT function takes a single argument that identifies a relation column by name. The function evaluates to a value equal to the current default value for the column. For cases where the default value of the column is specified as a current built-in system function, the DEFAULT function evaluates to the current value of system variables at the time the statement is executed.

The following rules apply to the use of the DEFAULT function as part of the search condition within a WHERE clause.

DEFAULT Function Data Type

The resulting data type of the DEFAULT function is the data type of the constant or built-in function specified as the default unless the default is NULL. If the default is NULL, the resulting date type of the DEFAULT function is the same as the data type of the column or expression for which the default is being requested.

DEFAULT Function Column Name

The DEFAULT function can be specified as DEFAULT or DEFAULT ( column_name ). When you do not specify a column name, the system derives the column based on context. If the column context cannot be derived, the system returns an error.

You can specify a DEFAULT function with a column name argument within a predicate. The system evaluates the DEFAULT function to the default value of the column specified as its argument. Once the system has evaluated the DEFAULT function, it treats it like a constant in the predicate.

You can specify a DEFAULT function without a column name argument within a predicate only if there is one column specification and one DEFAULT function as the terms on each side of the comparison operator within the expression.

DEFAULT Function Comparison Operators

Following existing comparison rules, a condition with a DEFAULT function used with comparison operators other than IS NULL or IS NOT NULL is unknown if the DEFAULT function evaluates to null.

A condition other than IS NULL or IS NOT NULL with a DEFAULT function compared with a null evaluates to unknown.

For more information about the DEFAULT function, see SQL Functions, Operators, Expressions, and Predicates .

AND and OR Logical Operators

You can specify the AND and OR logical operators in the WHERE clause to create more complex search conditions. For example, this statement selects employees who are in department 100 AND who have either a college degree OR at least 5 years of experience:

     SELECT *
     FROM employee
     WHERE dept = 100
     AND  (edlev >= 16
     OR    yrsexp >= 5);

Using WHERE to Filter Character Data

You can use the WHERE clause to filter character data by searching for a text string.

For example, this statement is processed by searching the employee table for every row that satisfies the condition: the job_title column contains the character string “analyst”. The name and dept_no fields for those rows are then listed.

     SELECT name, dept_no
     FROM employee
     WHERE UPPER (job_title) LIKE '%ANALYST%';

Scalar UDFs

You can specify a scalar UDF in a WHERE clause search condition if the UDF returns a value expression. See Example: Invoking an SQL UDF Within a Search Condition.

Scalar Subqueries

You can specify a scalar subquery as an operand of a scalar predicate in the WHERE, HAVING, and QUALIFY clauses of a query.

SAMPLE Clause in a Subquery

You cannot specify a SAMPLE clause in a subquery used as a WHERE clause predicate.

WHERE Clause Defines Condition for Joining Table Rows

A WHERE clause can define a condition for joining table rows, typically for a situation in which the values in a common column must match in both tables.

The following statement, which asks for the name of each employee from the employee table and the location of the department for each employee from the department table, is processed by joining the employee and department tables on the WHERE clause equality condition employee.dept_no=department.dept_no.

     SELECT name, loc
     FROM employee, department
     WHERE employee.dept_no = department.dept_no;

EXISTS Quantifier

SQL supports the EXISTS (∃) logical quantifier for testing the result of subquery search conditions. See Specifying Subqueries in Search Conditions. For more detail about the EXISTS quantifier, see SQL Functions, Operators, Expressions, and Predicates .

If the subquery would return response rows, then the WHERE condition is considered to be satisfied. Specifying the NOT qualifier for the EXISTS predicate inverts the test.

A subquery used in a search condition does not return any rows. It returns a boolean value to indicate whether responses would or would not be returned.

The subquery can be correlated with an outer query.

Specifying a Column PARTITION or PARTITION#L n

You can specify a system-derived column PARTITION or PARTITION#L n (n between 1 and 62) in a WHERE clause if the referenced table does not have a user-defined column named partitionor PARTITION#L n, respectively. You can specify these system-derived columns for a table that does not have partitioning, but the value returned for such a system-derived column is always 0, so the only reason to do so is to determine if a nonempty table is partitioned.

PARTITION is equivalent to a value expression where the expression is identical to the combined partitioning expression defined for the table with column references appropriately qualified as needed.

PARTITION#L n, where n ranges from 1 to 62, inclusive, is equivalent to a value expression where the expression is identical to the partitioning expression at the corresponding level (or 1 if this is a column partitioning level), or zero if the table is not partitioned.

Therefore, a query made on a partitioned table that specifies the predicate WHERE PARTITION <> combined_partitioning_expression should always return 0 rows. If any rows are returned, then they are not partitioned properly, and the table should be revalidated immediately.

Use Consistent Predicate Domains

You should always be consistent with the predicate domains you specify in a WHERE clause search condition. Specifying appropriate predicate domains enables Teradata Database to avoid having to perform an implicit conversion on a value before comparing it.

For example, if emp_no is defined as CHARACTER(5), then you should compare emp_no values using character values in the WHERE clause search condition rather than numeric values, as indicated in this predicate, where the value 12345 is correctly specified as a character string (‘12345’) rather than as a numeric value (12345).

     …
     WHERE emp_no = '12345'
     …

Join Efficiency and Indexes

The efficiency of a join operation depends on whether the WHERE condition uses values for columns on which primary, secondary, or multitable join indexes are defined.

If indexes are defined on the dept_no columns in both the employee and department tables, specifying an equality condition between the values in each indexed column, as in the preceding example, allows the rows in the two tables to be matched using the values in both indexed columns.

Efficiency is increased if a primary index is defined on one or both of these columns. For example, define dept_no as the unique primary index for the department table. This is not possible if one or both of the tables being joined is an ordinary NoPI table or a NoPI column-partitioned table. See NoPI Tables, Column-Partitioned Tables, and WHERE Clause Search Conditions for suggestions about how to work around this potential problem.

For all-AMP tactical queries against row-partitioned tables, you should specify a constraint on the partitioning column set in the WHERE clause.

If a query joins row-partitioned tables that are partitioned identically, using their common partitioning column set as a constraint enhances join performance still more if you also include an equality constraint between the partitioning columns of the joined tables.

NoPI Tables, Column-Partitioned Tables, and WHERE Clause Search Conditions

NoPI and NoPI column-partitioned tables require a full-table scan to retrieve rows. Column partition elimination or row partition elimination may occur that make the scans less than full-table scans.

Because NoPI and NoPI column-partitioned tables support USIs and NUSIs, consider specifying USIs for conditions designed to retrieve single rows and NUSIs for conditions designed to retrieve row sets. For details, see NoPI Tables and SELECT Statements.

Although you cannot specify join indexes in WHERE clause conditions, the Optimizer uses them if they exist and can be used to avoid a full-table scan. For information about how the Optimizer uses join indexes, see SQL Request and Transaction Processing .

Defining join indexes on a NoPI or NoPI column-partitioned table can slow down the loading of rows into the table.

Unconstrained Joins

An unconstrained join is one for which a WHERE clause is not specified for the tables that are joined. See Cross Join.

The result of an unconstrained join is a Cartesian product, which is rarely the desired result. A Cartesian product is the product of the number of rows in each table that is joined. An unconstrained join can produce a great many rows, returning a result that not only is not desired, but one that places a large performance burden on the system.

If a SELECT statement specifies correlation and real names (for example, correlation names in a WHERE clause and real names in the select list), the Optimizer may specify an unconstrained join, depending on what the conditions of the query are and how they are specified.