Syntax:
condition [AND | OR condition]
where each condition may be one of the following (note that ø is any comparison operator, i.e. "=", "<", "<=", ">", ">=", or "<>"):
condition [AND | OR condition]
or
field1 ø field2
or
field1 ø ALL | SOME singlefieldselect
or
field1 [NOT] IN singlefieldselect
or
field1 IS [NOT] NULL
or
field1 [NOT] BETWEEN value1 AND value2
or
field1 [NOT] LIKE wildcardspec
The parts of the where predicate have the following meaning:
Part | Meaning |
condition | a boolean condition as detailed above |
field1, field2 | the fields being compared |
singlefieldselect | a select statement returning a table with only one column of data type compatible with the compared field |
value1, value2 | Inclusive values between which field1 must lie |
ALL | The predicate is only true if the comparison is true for every row in the singlefieldselect table |
SOME | The predicate is true if the comparison is true for one or more rows in the singlefieldselect table. |
IN | The predicate is true if a field1 matches one or more rows of singlefieldselect |
IS NULL | The predicate is true if the field1 contains the null value |
LIKE | The field1 is compared against a wildcard specification and matching rows are returned. |
NOTE: Only use NULL with the IS operator. This is because every expression evaluates to false
if the tested field is null, e.g., field1 = NULL will always be false, no matter what field1 contains