Using Logical Operators in Expressions
Go Up to Understanding SQL Expressions
Logical operators calculate a Boolean value, True, False, or Unknown, based on comparing previously calculated simple search conditions immediately to the left and right of the operator. InterBase recognizes three logical operators, NOT, AND, and OR.
NOT reverses the search condition in which it appears, while AND and OR are used to combine simple search conditions. For example, the following query returns any employee whose last name is not “Smith”:
DECLARE NOSMITH CURSOR FOR SELECT LAST_NAME INTO :lname FROM EMPLOYEE WHERE NOT LNAME = 'Smith';
When AND appears between search conditions, both search conditions must be true if a row is to be retrieved. The following query returns any employee whose last name is neither “Smith” nor “Jones”:
DECLARE NO_SMITH_OR_JONES CURSOR FOR SELECT LAST_NAME INTO :lname FROM EMPLOYEE WHERE NOT LNAME = 'Smith' AND NOT LNAME = 'Jones';
OR stipulates that one search condition or the other must be true. For example, the following query returns any employee named “Smith” or “Jones”:
DECLARE ALL_SMITH_JONES CURSOR FOR SELECT LAST_NAME, FIRST_NAME INTO :lname, :fname FROM EMPLOYEE WHERE LNAME = 'Smith' OR LNAME = 'Jones';
The order in which combined search conditions are evaluated is dictated by the precedence of the operators that connect them. A NOT condition is evaluated before AND, and AND is evaluated before OR. Parentheses can be used to change the order of evaluation. For more information about precedence and using parentheses for grouping, see Determining Precedence of Operators.