WHERE Clause
Purpose
Filters rows that satisfy a conditional expression in SELECT, DELETE, INSERT, UPDATE, ABORT, and ROLLBACK statements.
Syntax
where:
|
Syntax Element … |
Specifies … |
|
search_condition |
a search condition, also referred to as a conditional expression or predicate. 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. See SQL Functions, Operators, Expressions, and Predicates for more information about relational operators and expressions. 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. If you specify the value for a row‑level security constraint in a search condition, that value must be expressed in its encoded form. |
ANSI Compliance
The WHERE clause is ANSI SQL:2011-compliant.
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
If a WHERE clause search condition specifies a UDT comparison, then:
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.
|
IF a DEFAULT function is used with … |
THEN the comparison is … |
|
IS NULL |
|
|
IS NOT NULL |
|
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 8: Invoking an SQL UDF Within a Search Condition” on page 128.
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 (see SQL Functions, Operators, Expressions, and Predicates) for testing the result of subquery search conditions. See “Specifying Subqueries in Search Conditions” on page 129. 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#Ln
You can specify a system-derived column PARTITION or PARTITION#Ln (n between 1 and 62) in a WHERE clause if the referenced table does not have a user‑defined column named partition or PARTITION#Ln, 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#Ln, 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 column‑partitioned table. See “NoPI Tables, Column‑Partitioned Tables, and WHERE Clause Search Conditions” on page 124 for suggestions about how to work around this potential problem.
For all-AMP tactical queries against PPI tables, you should specify a constraint on the partitioning column set in the WHERE clause.
If a query joins PPI 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 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 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 (see “NoPI Tables, Column‑Partitioned Tables, and SELECT Statements” on page 33 for details).
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 (see SQL Request and Transaction Processing for information about how the Optimizer uses join indexes).
Defining join indexes on a NoPI or column‑partitioned table can slow down the loading of rows into the table.
Unconstrained Joins
An unconstrained join (see “Cross Join” on page 251) is one for which a WHERE clause is not specified for the tables that are joined.
The result of an unconstrained join is a Cartesian product, which is almost never 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.
Example : Simple WHERE Clause Predicate
The following statement can be used to select the name and job title of every employee
in Department 100. In this statement, the predicate dept_no=100 is the conditional expression.
SELECT name, jobtitle
FROM employee
WHERE dept_no = 100;
Example : Using the table_name.* Syntax
The following statement returns only those rows from the employee table whose values in the empno column match the values in the mgr_no column of the department table.
Note the use of the table_name.* form of the select list.
SELECT employee.*
FROM employee, department
WHERE employee.empno=department.mgr_no;
Example : Ensuring That Predicate Domains Are Consistent
If empno is defined as CHARACTER(5), then empno values should be compared with character values in the WHERE condition rather than numeric values, as indicated in this predicate, where the value 12345 is specified as a character string rather than as a numeric value.
…
WHERE emp_no = '12345'
…
Example : Delete from a Row-Partitioned Table Using the System‑Defined PARTITION Column in the Search Condition
This statement deletes all orders in the orders table from partition 1 of the SLPPI table orders.
DELETE FROM orders
WHERE orders.PARTITION=1;
If orders was a multilevel partitioned table, you might substitute PARTITION#L1 for PARTITION. However, this substitution would delete all orders in partition 1 of level 1 from the table, which is probably not the result you anticipated.
A DELETE statement for the same multilevel partitioned table specifying PARTITION in place of PARTITION#L1 would only delete the rows in combined partition 1.
Example : INSERT … SELECT and DELETE Operations from a Row-Partitioned Table Using the System‑Defined PARTITION Column as a Search Condition
This example performs an INSERT … SELECT to copy orders from partitions 1 and 2 of the SLPPI orders table into the old_orders table, and then deletes them from orders in the second part of the multistatement request.
INSERT INTO old_orders
SELECT *
FROM orders
WHERE orders.PARTITION IN (1,2)
;DELETE FROM orders
WHERE orders.PARTITION IN (1,2);
Example : DEFAULT Function in a WHERE Clause Search Condition
The following set of examples shows the DEFAULT function in a WHERE clause search condition.
The examples assume these table definitions:
CREATE TABLE table14 (
col1 INTEGER,
col2 INTEGER DEFAULT 10,
col3 INTEGER DEFAULT 20,
col4 CHARACTER(60) );
CREATE TABLE table15 (
col1 INTEGER ,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL DEFAULT NULL,
col4 INTEGER CHECK (col4 > 10) DEFAULT 9 );
CREATE TABLE table16 (
col1 INTEGER,
col2 INTEGER DEFAULT 10,
col3 INTEGER DEFAULT 20,
col4 CHARACTER(60) );
In this statement, the DEFAULT function evaluates to the default value of col2, which is 10.
SELECT col2, col3
FROM table16
WHERE col1 < DEFAULT(col2);
Following is an equivalent statement that uses an explicitly defined search condition in the WHERE clause.
SELECT col2, col3
FROM table16
WHERE col1 < 10;
You can specify a DEFAULT function with a column name argument within a predicate.
In this statement, the DEFAULT function evaluates to the default value of col3, which is 20.
SELECT col2, col3
FROM table16
WHERE col1 + 9 > DEFAULT(col3)+ 8;
Following is an equivalent statement that uses an explicitly defined search condition in the WHERE clause.
SELECT col2, col3
FROM table16
WHERE col1 + 9 > 20 + 8;
In this example, the DEFAULT function evaluates to the default value of col3, compares the returned value with the result of a subquery, and returns the result.
SELECT col2, col3
FROM table16
WHERE DEFAULT(col3) < ANY (SELECT col2
FROM table14);
You can specify a DEFAULT function without a column name argument in a comparison predicate when there is only one column specification and one DEFAULT function as the terms on the each side of the comparison operator within the expression.
In this example, there is only one column reference, and the DEFAULT function is compared directly with it; therefore, the DEFAULT function evaluates to the default value of col2.
SELECT col2, col3
FROM table16
WHERE col2 > DEFAULT;
The following two statements are semanatically equivalent.
SELECT col2, col3
FROM table16
WHERE col2 > DEFAULT
AND DEFAULT > col3;
SELECT col2, col3
FROM table16
WHERE col2 > DEFAULT(col2)
AND DEFAULT(col3) > col3;
Following the 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.
Assume this table definition.
CREATE TABLE table17 (
col1 INTEGER,
col2 INTEGER NOT NULL DEFAULT 10,
col3 INTEGER DEFAULT NULL );
In this example, the DEFAULT function evaluates to NULL, the predicate is UNKNOWN, and the WHERE condition is FALSE; therefore, the query returns no rows.
SELECT col1
FROM table17
WHERE col1 = DEFAULT;
In this example, the DEFAULT function evaluates to NULL. The first condition, DEFAULT(col3) > 5, is UNKNOWN, but the second condition is TRUE; therefore, the WHERE condition is
TRUE and the statement returns all of the rows from table17.
SELECT col1, col2
FROM table17
WHERE DEFAULT(col3) > 5
OR DEFAULT(col3) IS NULL;
In this example, the DEFAULT function evaluates to NULL, the predicate is UNKNOWN, and the WHERE condition is FALSE. Therefore, the statement does not return any rows.
SELECT col1
FROM table17
WHERE col2 < DEFAULT(col3) + 3;
In this example, the DEFAULT function evaluates to 10 and the first condition is TRUE.
The second condition, DEFAULT(col2) IS NULL, is FALSE; therefore, the WHERE condition is TRUE and the statement returns all of
the rows from table17.
SELECT col1, col2
FROM table17
WHERE DEFAULT(col2) > 5
OR DEFAULT(col2) IS NULL;
Example : Scalar Subquery in the WHERE Clause of a SELECT Statement
You can specify a scalar subquery as an operand of a scalar predicate in the WHERE, HAVING, and QUALIFY clauses of a query.
The following example specifies a scalar subquery (SELECT AVG(price)…) as an operand in its WHERE clause.
SELECT category, title, price
FROM movie_titles AS t2
WHERE (SELECT AVG(price)
FROM movie_titles AS t1
WHERE t1.category = t2.category)<(SELECT AVG(price)
FROM movie_titles);
Example : Invoking an SQL UDF Within a Search Condition
You can invoke an SQL UDF within a WHERE clause search condition if the UDF returns a value expression.
This example shows a correct invocation of the SQL UDF value_expression within a WHERE clause.
SELECT test.value_expression(t1.a1, t2.a2)
FROM t1, t2
WHERE t1.b1 = t2.b2
AND test.value_expression(t1.a1, t2.a2) = 10;
Example : Specifying a Row‑Level Security Constraint in a Search Condition
If you have the required security credentials to read all rows in the table or have override select constraint privileges on the table, this statement returns emp_name and group_membership row‑level security constraint value name and value code for all managers. If you do not have the required credentials or override privileges on the table, no rows are returned.
You cannot specify a row‑level security constraint value name as the WHERE clause search condition. You must specify a value code. In this example, the value code 90 represents the value name manager.
SELECT emp_name, group_membership
FROM emp_record
WHERE group_membership=90;
For More Information
For more information about the use of the WHERE clause, see: