So far, the WHERE clauses we have
discussed have been pretty darn simple. And as such, they are not
all that useful in real world situations in which multiple conditions
affect our needs. Actually, SQL provides a set of
operators that allow you to combine multiple predicate statements.
Specifically, a WHERE clause can be made up of multiple
predicates by chaining them together with the AND, OR, and
NOT operators.
These operators work as you might expect.
The AND operator specifies multiple conditions which a column
must match in order to be returned.
The OR operator specifies multiple acceptable conditions.
And the NOT operator specifies negativity in a match.
However, These are best understood by example.
Consider the case in which we want
to return only rows from the SALES
table in which the salesman was salesman number
"101", but we only want sales in which the price was greater than
$100.00
SELECT P_NUM, S_AMOUNT, E_NUM
FROM SALES
WHERE E_NUM = 101 AND S_AMOUNT > 100;
In this case, our database would return:
P_NUM S_AMOUNT E_NUM
------------------------------
002 865.99 101
------------------------------
In this case, the row was returned
because S_AMOUNT was greater than "100" "AND" E_NUM was
equal to "101". Now consider the same query using OR:
SELECT P_NUM, S_AMOUNT, E_NUM
FROM SALES
WHERE E_NUM = 101 OR S_AMOUNT > 100;
In this case, we would get the
following response:
P_NUM S_AMOUNT E_NUM
------------------------------
001 99.99 101
001 199.98 102
003 865.99 101
------------------------------
Notice that the first row satisfied
the condition because E_NUM was equal to "101" even
though S_AMOUNT was less than "100". Likewise, the
second row was selected because S_AMOUNT was greater than "100"
even though E_NUM was not equal to "101"
Another important thing to note about AND
and OR is that if you decide to combine multiple conditions, you
must be very careful about how you compose your predicate.
AND and OR conditions can be defined logically using parentheses
as in most languages, but the order of the parentheses
has meaning. Thus
SEX = "M" AND (WEIGHT > 150 OR AGE < 35)
is much different than saying
(SEX = 'M' AND WEIGHT > 150) OR AGE < 35
In the first case, no female subject
could be returned whereas in the second case, a female could
be returned if she was younger than 35.
Finally, the NOT operator is
used to specify the reverse condition.
Thus, if you want a list of
all the employees other than Lim Sing Yuen
from the sample database,
you would use:
SELECT EMP_NUM, EMP_NAME
FROM EMPLOYEES
WHERE NOT (EMP_NAME = 'Lim Sing Yuen');
In this case, the database would
return the following:
EMP_NUM EMP_NAME
--------------------
001 Lim Li Chuen
003 Loo Soon Keat
---------------------
