Search conditions in UltraLite

A search condition is the criteria 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 comparison-operator expression
| expression IS [ NOT ] NULL 
| expression [ NOT ] BETWEEN expression AND expression
| expression [ NOT ] IN ( expression, ... )
| expression [ NOT ] IN ( subquery )
| expression [ NOT ] { ANY | ALL } ( subquery ) 
| expression [ NOT ] EXISTS ( subquery ) 
| expression [ NOT ] LIKE ( pattern ) 
| NOT search-condition
| search-condition AND search-condition
| search-condition OR search-condition
| ( search-condition IS [ NOT ] {TRUE | FALSE | UNKNOWN }

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

Remarks

In UltraLite, search conditions can appear in the:

WHERE clause
HAVING clause
ON phrase
SQL queries

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.