Search conditions

A search condition is the criteria specified for a WHERE clause, a HAVING clause, a CHECK clause, an ON phrase in a join, or an IF expression. A search condition is also known as a predicate.

Syntax

search-condition :
 expression comparison-operator expression
| expression comparison-operator { [ ANY | SOME ] | ALL } ( subquery )
| expression IS [ NOT ] DISTINCT FROM expression
| expression IS [ NOT ] NULL
| expression [ NOT ] BETWEEN expression AND expression
| expression [ NOT ] LIKE pattern [ ESCAPE expression ]
| expression [ NOT ] SIMILAR TO pattern [ ESCAPE escape-expression ]
| expression [ NOT ] REGEXP pattern [ ESCAPE escape-expression ]
| expression [ NOT ] IN ( { expression
     | subquery
     | value-expression1 , ... } )
| CONTAINS (column-name [,... ] , query-string )
| EXISTS ( subquery )
| NOT condition
| search-condition [ { AND | OR } search-condition ] [ ... ]
| ( search-condition )
| ( search-condition , estimate )
|  search-condition IS [ NOT ] { TRUE | FALSE | UNKNOWN }
| expression IS [ NOT ] OF ( [ ONLY type-name ,... )
| trigger-operation

comparison-operator :
 =   
| >  
| <  
| >=  
| <=  
| <>  
| !=  
| !< 
| !>

trigger-operation :
INSERTING 
| DELETING
| UPDATING [ ( column-name-string ) ] 
| UPDATE( column-name )

Parameters

ALL search condition See ALL search condition.
ANY and SOME search conditions See ANY and SOME search conditions.
IS [NOT] DISTINCT FROM search condition See IS DISTINCT FROM and IS NOT DISTINCT FROM search conditions.
BETWEEN search condition See BETWEEN search condition.
CONTAINS search condition See CONTAINS search condition.
EXISTS search condition See EXISTS search condition.
LIKE search condition See LIKE search condition.
SIMILAR TO search condition See SIMILAR TO search condition.
REGEXP search condition See REGEXP search condition.
IS OF type-expression, and IS NOT OF type-expression This type predicate was added for support of spatial geometries, but it can be used for any existing data type as well. See Spatial data type syntax based on ANSI SQL UDTs.

Remarks

Search 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.

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 displaying 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 or FALSE 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.

The LIKE, SIMILAR TO, and REGEXP search conditions are very similar. To understand similarities and differences between them, see LIKE, REGEXP, and SIMILAR TO search conditions.