You use the WHERE clause to specify a search condition for selecting the rows for the derived table. The derived table contains only the rows that satisfy the search condition (i.e. the search condition is true). Rows for which the search condition returns the value false or unknown are not included in the derived table.
SELECT ...
WHERE search_condition
search_condition
Condition that the selected rows must satisfy.
Examples
The predicates are described in detail in chapter "Compound language constructs". Here, the most important types of search condition are illustrated using simple examples.
Comparison with constants: =, <, <=, >, > =, <>
SELECT cust_num, company FROM customers WHERE zip = 81739
Comparison with string pattern: [NOT] LIKE
SELECT * FROM customers WHERE company LIKE 'Sie%'
Range query: [NOT] BETWEEN
SELECT cust_num, company FROM customers WHERE zip BETWEEN 80000 AND 89999
Comparison with NULL value: IS [NOT] NULL
SELECT service_num, order_num, service_text FROM service WHERE inv_num IS NULL
Comparison with several values: [NOT] IN
SELECT cust_num, company FROM customers WHERE zip IN (81739, 80469)
Inner SELECT statement: [NOT] EXISTS
SELECT company FROM customers
WHERE NOT EXISTS (SELECT * FROM orders WHERE customers.cust_num =
orders.cust_num)
Subquery (see section "Subquery"):
Subquery that returns a derived column: ALL, ANY, SOME, [NOT] IN
SELECT company FROM customers WHERE customers.cust_num =
SOME (SELECT cust_num FROM orders WHERE order_date = DATE '<date>')
Correlated subquery:
Select for each order, the service that is at least double the average service price for this order:
SELECT s1.service_num, s1.order_num, s1.service_text FROM service s1
WHERE s1.service_total * s1.service_price > 2 *
(SELECT AVG (s2.service_total*s2.service_price)
FROM service s2 WHERE s2.order_num = s1.order_num)
Condition: AND, OR, NOT
SELECT service_num, order_num, service_date, service_text FROM service
WHERE service_text = 'Training' AND service_date > = DATE '<date>'