A condition comprises one or more predicates, possibly prefixed by NOT and connected together by one of the following:
AND OREach predicate may be a comparison, using the operators <, >, =, <>, <=, >= or one of the following:
<expression> [NOT] BETWEEN <expression> AND <expression>
<field> IS [NOT] NULL<R>
<field> [NOT] LIKE <string-constant> [ESCAPE <string-constant>]
<expression> [NOT] IN (<constant>,<constant>, … <constant>)
<expression> [NOT] IN (<subselect>)
EXISTS (<subselect>)The LIKE operator does wildcard matching; the character '_' in the right-hand string matches any one character on the left, and '%' matches zero or more characters. The ESCAPE clause, if present, specifies an escape character which may precede '_' or '%' to make them match only themselves. For example:
NAME LIKE 'ab%cd\%' ESCAPE '\'
is true if NAME is 'abcd%' or 'abxyzcd%' but false if NAME is 'abcdx'. From version 4.523 DP4 SQL allows the right operand of LIKE to be any character expression and does not restrict you to a constant.
Conditions can only be used in WHERE clauses and HAVING clauses of the SELECT statement; it is not possible (for example) to use a condition to calculate the value of a yes/no field.
The IN and EXISTS predicates take a nested select statement as one of their operands. A nested select statement may also be used on the right-hand side of a comparison. In this case the select must return exactly one record, for example:
SELECT name, grade FROM employee
WHERE grade = (SELECT grade FROM employee WHERE name = 'John Brown');
unless it is preceded by one of the words ANY or ALL, for example:
SELECT name, grade FROM employee
WHERE grade < ANY (SELECT grade FROM employee)
The word SOME is a synonym for ANY.
Note that, for the purpose of comparisons, all null values are different and they are neither greater than nor less than any other value. A comparison will always return false if one of the values being compared is null. The only way of testing for null values is to use the IS NULL predicate.