Conditions with AND or OR

You can combine simple conditions with the logical operators AND or OR to form complex conditions.

The following SELECT statements contain examples of complex conditions in their WHERE clauses:

SELECT customer_num, order_date FROM orders
   WHERE paid_date > '1/1/97' OR paid_date IS NULL;
SELECT order_num, total_price FROM items
   WHERE total_price > 200.00 AND manu_code LIKE 'H
SELECT lname, customer_num FROM customer
   WHERE zipcode BETWEEN '93500' AND '95700' 
   OR state NOT IN ('CA', 'WA', 'OR');

The following truth tables show the effect of the AND and OR operators. The letter T represents a TRUE condition, F represents a FALSE condition, and the question mark (?) represents an UNKNOWN value. An UNKNOWN value can occur when part of an expression that uses a logical operator is NULL.

begin figure description - This figure is described in the surrounding text - end figure description

The marginal values at the left represent the first operand, and values in the top row represent the second operand. Values within each 3x3 matrix show the returned value after the operator is applied to operands of those values.

If the Boolean expression evaluates to UNKNOWN, the condition is not satisfied.

Consider the following example within a WHERE clause:

WHERE ship_charge/ship_weight < 5
   AND order_num = 1023

The row where order_num = 1023 is a row where ship_weight is NULL. Because ship_weight is NULL, ship_charge/ship_weight is also NULL; therefore, the truth value of ship_charge/ship_weight < 5 is UNKNOWN. Because order_num = 1023 is TRUE, the AND table states that the truth value of the entire condition is UNKNOWN. Consequently, that row is not chosen. If the condition used an OR in place of the AND, the condition would be TRUE.