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 about the use of the WHERE clause, see: