To specify a search condition for a WHERE clause, a HAVING clause, a CHECK clause, a JOIN clause, or an IF expression.
{ expression compare expression
| expression compare { ANY | SOME| ALL } ( subquery )
| expression IS [ NOT ] NULL
| expression [ NOT ] BETWEEN expression AND expression
| expression [ NOT ] LIKE expression [ESCAPE expression]
| expression [ NOT ] IN ( { expression | subquery |
... value-expr1 , value-expr2 [, value-expr3 ]... } )
| column-name [ NOT ] CONTAINS ( ... word1 [, word2,] [, word3 ]... )
| EXISTS ( subquery )
| NOT condition
| condition AND condition
| condition OR condition
| ( condition )
| ( condition , estimate )
| condition IS [ NOT ] { TRUE | FALSE | UNKNOWN }
compare:
{ = | > | < | >= | <= | <> | != | !< | !> }
Anywhere
Must be connected to the database.
For example, the following query retrieves the names and birth years of the oldest employees:
SELECT name, year_of_birth FROM employees WHERE year_of_birth <= ALL (SELECT year_of_birth FROM employees);
The subqueries that provide comparison values for quantified comparison predicates may retrieve multiple rows but can only have one column.
None
Conditions are used to choose a subset of the rows from a table, or in a control statement such as an IF statement to determine control of flow.
SQL conditions do not follow boolean logic, where conditions are either true or false. In SQL, every condition evaluates as one of TRUE, FALSE, or UNKNOWN. This is called three-valued logic. The result of a comparison is UNKNOWN if either value being compared is the NULL value. For tables showing how logical operators combine in three-valued logic, see “Three-valued logic”.
Rows satisfy a search condition if and only if the result of the condition is TRUE. Rows for which the condition is UNKNOWN do not satisfy the search condition. For more information about NULL, see “NULL value”.
Subqueries form an important class of expression that is used in many search conditions. For information about using subqueries in search conditions, see “Subqueries in search conditions”.
The different types of search condition are discussed in the following sections.
