Specifies a search condition for a WHERE clause, a HAVING clause, an ON phrase in a join, or an IF expression. A search condition is also called a predicate.

Syntax

search-condition:
 expression compare expression
| expression IS [ NOT ] { NULL | TRUE | FALSE | UNKNOWN }
| expression [ NOT ] BETWEEN expression AND expression
| expression [ NOT ] IN ( expression, ... )
| expression [ NOT ] IN ( subquery )
| expression [ NOT ] { ANY | ALL } ( subquery ) 
| expression [ NOT ] EXISTS ( subquery ) 
| NOT search-condition
| search-condition AND search-condition
| search-condition OR search-condition
| ( search-condition )

Parameters

compare:
= | > | < | >= | <= | <> | != | !< | !>

Remarks

In UltraLite, search conditions can appear in the:

Search conditions can be used to choose a subset of the rows from a table in a FROM clause in a SELECT statement, or in expressions such as an IF or CASE to select specific values. In UltraLite, every condition evaluates as one of three states: TRUE, FALSE, or UNKNOWN. When combined, these states are referred to as three-valued logic. The result of a comparison is UNKNOWN if either value being compared is the NULL value. Search conditions are satisfied only if the result of the condition is TRUE.

The different types of search conditions supported by UltraLite include:

• ALL conditions
• ANY conditions
• BETWEEN conditions
• EXISTS conditions
• IN conditions

These conditions are discussed in separate sections that follow.

See also

• Comparison operators
• Three-valued logic
• Subqueries in expressions