And, Or, and Not

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) 

(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
    ---------------------

Where
Introduction to Databases for the Web | Table of Contents
Between