You can combine two or more simple WHERE expressions, connected by AND and/or OR operators, to create a compound expression.
By default, when multiple WHERE phrases are evaluated, logical ANDs are processed before logical ORs. In compound expressions, you can use parentheses to change the order of evaluation. All AND and OR operators enclosed in parentheses are evaluated first, followed by AND and OR operators outside of parentheses.
You should always use parentheses in complex expressions to ensure that the expression is evaluated correctly. For example:
WHERE (SEATS EQ 2) AND (SEATS NOT-FROM 3 TO 4)
This is especially useful when mixing literal OR tests with logical AND and OR tests:
WHERE (LAST_NAME EQ 'CROSS') OR (LAST_NAME EQ 'JONES')
WHERE (CURR_SAL GT 20000) AND (DEPARTMENT IS 'MIS') AND (CURR_JOBCODE CONTAINS 'A')
WHERE (LAST_NAME EQ 'CROSS' OR 'JONES')
This example illustrates the impact of parentheses on the evaluation of literal ORs and logical ANDs.
In this request, each expression enclosed in parentheses is evaluated first in the order in which it appears. Notice that the first expression contains a literal OR. The result of each expression is then evaluated using the logical AND.
If parentheses are excluded, the logical AND is evaluated before the literal OR.
TABLE FILE EMPLOYEE PRINT CURR_SAL BY LAST_NAME WHERE (LAST_NAME EQ 'CROSS' OR 'JONES') AND (CURR_SAL GT 22000) END
The output is:
LAST_NAME CURR_SAL --------- -------- CROSS $27,062.00