Datasheet

32 Chapter 1

Introducing SQL
Name COMMISSION_PCT
------------------------------------------ --------------
John Russell .4
Janette King .35
Patrick Sully .35
Allan McEwen .35
ANY or SOME
You can use the ANY or SOME operator to compare a value to each value in a list or subquery.
The
ANY and SOME operators always must be preceded by one of the following comparison
operators:
=, !=, <, >, <=, or >=.
SELECT first_name || ‘ ‘ || last_name “Name”, department_id
FROM employees
WHERE department_id <= ANY (10, 15, 20, 25);
Name DEPARTMENT_ID
------------------------------------------- -------------
Jennifer Whalen 10
Michael Hartstein 20
Pat Fay 20
ALL
You can use the ALL operator to compare a value to every value in a list or subquery. The
ALL operator must always be preceded by one of the following comparison operators: =, !=,
<, >, <=, or >=.
SELECT first_name || ‘ ‘ || last_name “Name”, department_id
FROM employees
WHERE department_id >= ALL (80, 90, 100);
Name DEPARTMENT_ID
------------------------------------------- -------------
Nancy Greenberg 100
Daniel Faviet 100
John Chen 100
Ismael Sciarra 100
Jose Manuel Urman 100
Luis Popp 100
Shelley Higgins 110
William Gietz 110
8 rows selected.
For all the comparison operators discussed, if one side of the operator is NULL, the result is NULL.
95127c01.indd 32 2/18/09 6:37:10 AM