WHERE Clauses
The syntax of the WHERE
clause is:
WHERE search_condition
where search_condition
is any value expression that returns a value of type boolean.
After the processing of the FROM
clause is done, each row of the derived virtual table is checked against the search condition. If the result of the condition is true
, the row is kept in the output table, otherwise (that is to say, if the result is false or null) it is discarded. The search condition typically references at least one column of the table generated in the FROM
clause; this is not required, but otherwise the WHERE
clause will be fairly useless.
The join condition of an inner join can be written either in the WHERE
clause or in the JOIN
clause. For example, these table expressions are equivalent:
FROM a, b WHERE a.id = b.id AND b.val > 5
and:
FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5
or perhaps even:
FROM a NATURAL JOIN b WHERE b.val > 5
Which one of these you use is mainly a matter of style. The JOIN
syntax in the FROM
clause is probably not as portable to other SQL database management systems, even though it is in the SQL standard. For outer joins there is no choice: they must be done in the FROM
clause. The ON
or USING
clause of an outer join is not equivalent to a WHERE
condition, because it results in the addition of rows (for unmatched input rows) as well as the removal of rows in the final result.
Here are some examples of WHERE clauses:
SELECT ... FROM fdt WHERE c1 > 5
SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)
SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)
SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)
SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100
SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)
fdt
is the table derived in the FROM
clause. Rows that do not meet the search condition of the WHERE
clause are eliminated from fdt
. Notice the use of scalar subqueries as value expressions. Just like any other query, the subqueries can employ complex table expressions. Notice also how fdt
is referenced in the subqueries. Qualifying c1
as fdt.c1
is only necessary if c1
is also the name of a column in the derived input table of the subquery. But qualifying the column name adds clarity even when it is not needed. This example shows how the column naming scope of an outer query extends into its inner queries.