Combining Conditions with OR and AND

You will often need to combine conditions in the WHERE clause to find rows that meet multiple criteria. For example, you might want to see the price of several products, as in this query:

select ProductName, UnitPrice
  from Products
 where ProductName = 'Konbu'
    or ProductName = 'Cream Cheese'
    or ProductName = 'Tofu'
    or ProductName = 'Pavlova'

Results:

ProductName                              UnitPrice
---------------------------------------- ---------------------
Konbu                                                   6.0000
Pavlova                                                17.4500
Tofu                                                   23.2500

The four comparisons in the query are joined with the keyword OR. As in all programming environments, when you use OR, the condition is true when any of the joined conditions is true. For each row, the server tests each of the conditions. As long as the row matches any one condition, it will be included in a result set. (Notice that no row came back for 'Cream Cheese'. This company doesn't sell cream cheese.)

You can also combine conditions affecting different columns. Here is a query combining a price condition and a product name condition:

select ProductName, UnitPrice
  from Products
 where ProductName = 'Konbu'
    or UnitPrice = 10.00

Results:

ProductName                              UnitPrice
---------------------------------------- ---------------------
Aniseed Syrup                                          10.0000
Konbu                                                   6.0000
Sir Rodney's Scones                                    10.0000
Longlife Tofu                                          10.0000

This query returns four rows from either the ProductName column or UnitPrice column, or both. There is one row matching the 'Konbu' condition, but the other three match only the price condition. If a row matches both conditions, it still appears in the result set only one time.

The AND Condition

Like the OR condition, AND works as it works in all programming environments. When conditions are connected with the AND keyword, all the conditions must be met for the row to appear in the result set. Each row will be compared to all the conditions in turn; only when all conditions are true will the SQL Server include the row.

In the previous query, we connected the conditions with OR. Rebuilding the same query with AND, we find that there are no rows whose ProductName is 'Konbu' and whose UnitPrice is 10.00:

select ProductName, UnitPrice
  from Products
 where ProductName = 'Konbu'
   and UnitPrice = 10.00

Results:

ProductName                              UnitPrice
---------------------------------------- ---------------------

The query returns an empty result set.

The next example combines the CategoryID and SupplierID to provide a narrow list. There are 12 products where the CategoryID is 2 and three products whose SupplierID is 1. When the conditions are combined, we find the only row where both conditions are true:

select ProductName, CategoryID, SupplierID
  from products
 where CategoryID = 2
   and SupplierID = 1

Results:

ProductName                              CategoryID  SupplierID
---------------------------------------- ----------- -----------
Aniseed Syrup                                      2           1
						

Combining AND and OR

What happens when the AND and OR conditions appear in the same query? For example, what if you wanted to see everything with a CategoryID of 2 from suppliers with an ID of 1 and 3. Here's how you might write it:

select ProductName, CategoryID, SupplierID
  from products
 where CategoryID = 2
   and SupplierID = 1
    or SupplierID = 3

Results:

ProductName                              CategoryID  SupplierID
---------------------------------------- ----------- -----------
Aniseed Syrup                                      2           1
Grandma's Boysenberry Spread                       2           3
Uncle Bob's Organic Dried Pears                    7           3
Northwoods Cranberry Sauce                         2           3

The third row in the results is in the wrong category. I wanted rows in category 2, but somehow something from category 7 appeared in the list.

The problem occurs because of operator precedence or, in English, because AND conditions are evaluated before OR conditions. In this case, the server first evaluated each row for the combination of category 2 and supplier 1 (using AND), and then used OR to include any rows from supplier 3 (regardless of category).

To override the default precedence, use parentheses to group together conditions you want evaluated first. In this example, wrap parentheses around the two SupplierID conditions, forcing them to be evaluated before the CategoryID condition, like this:

select ProductName, CategoryID, SupplierID
  from products
 where CategoryID = 2
   and (SupplierID = 1
    or SupplierID = 3)

Results:

ProductName                              CategoryID  SupplierID
---------------------------------------- ----------- -----------
Aniseed Syrup                                      2           1
Grandma's Boysenberry Spread                       2           3
Northwoods Cranberry Sauce                         2           3

The parentheses allow the server to correctly remove the incorrect row. Sometimes it's difficult to remember the rules for precedence. In general, when combining OR and AND conditions, use parentheses to clarify what you want. Your code will work more often on the first try, and the logic will be easier for others to understand and modify later.