Search Conditions

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.

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.

Subqueries form an important class of expression that is used in many search conditions.

The different types of search conditions are discussed in the following sections.

You specify a search condition for a WHERE clause, a HAVING clause, a CHECK clause, a JOIN clause, or an IF expression.

Syntax

expression compare expression
| expression compareANY | SOME| ALL } ( subquery )
| expression ISNOT ] DISTINCT FROM
| expression ISNOT ] NULL expression
| expressionNOT ] BETWEEN expression AND expression
| expressionNOT ] LIKE expressionESCAPE expression ]
| expressionNOT ] IN ( { expression | subquery |
... value-expr1 , value-expr2 [, value-expr3 ] … } )
| column-nameNOT ] CONTAINS ( … word1 [ , word2, ] [ , word3 ] … )
| CONTAINS column-name,...], contains-query string)
| EXISTSsubquery )
| NOT condition
| condition AND condition
| condition OR condition
| ( condition )
| ( condition , estimate )
| condition ISNOT ] { TRUE | FALSE | UNKNOWN }

Parameters

compare:
{  = | > | < | >= | <= | <>!=!<!> }

Usage

Anywhere

Authorization

Must be connected to the database

Example

For example, the following query retrieves the names and birth years of the oldest employees:
SELECT Surname, BirthDate 
FROM Employees 
WHERE BirthDate <= ALL (SELECT BirthDate FROM Employees);

The subqueries that provide comparison values for quantified comparison predicates might retrieve multiple rows but can have only one column.

Side Effects

None

Related concepts
SQL Operators
Subqueries in Search Conditions
Related reference
Comparison Conditions
Expressions
NULL Value
Strings
Three-Valued Logic