Combining WHERE Clauses

All the WHERE clauses introduced in Lesson 4, "Filtering Data," filter data using a single criteria. For a greater degree of filter control, SQL lets you specify multiple WHERE clauses. These clauses may be used in two ways: as AND clauses or as OR clauses.

Using the AND Operator

To filter by more than one column, you use the AND operator to append conditions to your WHERE clause. The following code demonstrates this:

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

The above SQL statement retrieves the product name and price for all products made by vendor DLL01 as long as the price is $4 or less. The WHERE clause in this SELECT statement is made up of two conditions, and the keyword AND is used to join them. AND instructs the database management system software to return only rows that meet all the conditions specified. If a product is made be vendor DLL01, but it costs more than $4, it is not retrieved. Similarly, products that cost less than $4 that are made by a vendor other than the one specified are not to be retrieved. The output generated by this SQL statement is as follows:

prod_name              prod_price
-------------------   ----------
Fish bean bag toy     3.4900
Bird bean bag toy     3.4900
Rabbit bean bag toy   3.4900

Using the OR Operator

The OR operator is exactly the opposite of AND. The OR operator instructs the database management system software to retrieve rows that match either condition. In fact, most better DBMSs will not even evaluate the second condition in an OR WHERE clause if the first condition has already been met. (If the first condition was met, the row would be retrieved regardless of the second condition.)

Look at the following SELECT statement:

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

The above SQL statement retrieves the product name and price for any products made by either of the two specified vendors. The OR operator tells the DBMS to match either condition, not both. If an AND operator is used here, no data is returned. The output generated by this SQL statement is as follows:

prod_name             prod_price
-------------------   ----------
Fish bean bag toy     3.4900
Bird bean bag toy     3.4900
Rabbit bean bag toy   3.4900
8 inch teddy bear     5.9900
12 inch teddy bear    8.9900
18 inch teddy bear    11.9900
Raggedy Ann           4.9900

Understanding Order of Evaluation

WHERE clauses can contain any number of AND and OR operators. Combining the two enables you to perform sophisticated and complex filtering.

But combining AND and OR operators presents an interesting problem. To demonstrate this, look at an example. You need a list of all products costing $10 or more made by vendors DLL01 and BRS01. The following SELECT statement uses a combination of AND and OR operators to build a WHERE clause:

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10;

prod_name                prod_price
-------------------      ----------
Fish bean bag toy        3.4900
Bird bean bag toy        3.4900
Rabbit bean bag toy      3.4900
18 inch teddy bear       11.9900
Raggedy Ann              4.9900

Look at the results above. Four of the rows returned have prices less than $10—so, obviously, the rows were not filtered as intended. Why did this happen? The answer is the order of evaluation. SQL (like most languages) processes AND operators before OR operators. When SQL sees the above WHERE clause, it reads any products costing $10 or more made by vendor BRS01, and any products made by vendor DLL01 regardless of price. In other words, because AND ranks higher in the order of evaluation, the wrong operators were joined together.

The solution to this problem is to use parenthesis to explicitly group related operators. Take a look at the following SELECT statement and output:

SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;

prod_name              prod_price
-------------------   ----------
18 inch teddy bear    11.9900

The only difference between this SELECT statement and the earlier one is that, in this statement, the first two WHERE clause conditions are enclosed within parenthesis. As parenthesis have a higher order of evaluation than either AND or OR operators, the DBMS first filters the OR condition within those parenthesis. The SQL statement then becomes any products made by either vendor DLL01 or vendor BRS01 costing $10 or greater, which is exactly what we want.