Datasheet

36 Chapter 1

Introducing SQL
FIRST_NAME LAST_NAME DEPARTMENT_ID
-------------------- ---------------------- -------------
Michael Hartstein 20
Pat Fay 20
Hermann Baer 70
SQL>
When using the NOT IN operator, if any value in the list or the result
returned from the subquery is
NULL, the NOT IN condition is evaluated to
FALSE. For example, last_name not in (‘Smith’, ‘Thomas’, NULL)
evaluates to
last_name != ‘Smith’ AND last_name != ‘Thomas’ AND
last_name != NULL
. Any comparison on a NULL value results in NULL. So,
the previous condition does not return any row even through there may
be some rows with
LAST_NAME as Smith or Thomas.
BETWEEN
You can use the BETWEEN operator to test a range. BETWEEN A AND B evaluates to TRUE if the
value is greater than or equal to
A and less than or equal to B. If NOT is used, the result is the
reverse. The following example lists all the employees whose salary is between $5,000 and
$6,000:
SELECT first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 5000 AND 6000;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Bruce Ernst 6000
Kevin Mourgos 5800
Pat Fay 6000
EXISTS
The EXISTS operator is always followed by a subquery in parentheses. EXISTS evaluates to
TRUE if the subquery returns at least one row. The following example lists the employees
who work for the administration department. Here is an example of using
EXISTS. Don’t
worry if you do not understand the SQL for now; subqueries are discussed in detail in
Chapter 4, “Using Joins and Subqueries.”
SELECT last_name, first_name, department_id
FROM employees e
WHERE EXISTS (select 1 FROM departments d
95127c01.indd 36 2/18/09 6:37:10 AM