Datasheet

Writing Simple Queries 35
Table 1.9 is the truth table for the NOT operator.
TABLE 1.9 NOT Truth Table
NOT
TRUE FALSE
FALSE TRUE
UNKNOWN UNKNOWN
Other Operators
In the following sections, I will discuss all the operators that can be used in the WHERE
clause of the SQL statement that were not discussed earlier.
IN and NOT IN
You can use the IN and NOT IN operators to test a membership condition. IN is equivalent
to the
=ANY operator, which evaluates to TRUE if the value exists in the list or the result set
from a subquery. The
NOT IN operator is equivalent to the !=ALL operator, which evaluates
to
TRUE if the value does not exist in the list or the result set from a subquery. The following
examples demonstrate how to use these two operators:
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id IN (10, 20, 90);
FIRST_NAME LAST_NAME DEPARTMENT_ID
-------------------- ------------------------- ----------
Steven King 90
Neena Kochhar 90
Lex De Haan 90
Jennifer Whalen 10
Michael Hartstein 20
Pat Fay 20
6 rows selected.
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id NOT IN
(10, 30, 40, 50, 60, 80, 90, 110, 100);
95127c01.indd 35 2/18/09 6:37:10 AM