| Previous | Table of Contents | Next |
Compound WHERE Clauses Using Boolean Logic
If you want to specify more than one column in a SELECT, each column is separated by a comma. But you may only have one WHERE clause in a query. That one clause must resolve to a single TRUE or FALSE result when compared to a row value, and the row is returned to the client based upon that result.
Although you must have no more than one WHERE clause, you may specify many conditions in the WHERE clause. The boolean operators provide a method for combining many conditions into a single boolean statement.
An Example Using OR
Suppose my publisher is considering a new advertising campaign and wants a list of books to promote. She has narrowed the list of books down to two categories: business books, and books that cost more than $18. She would like a report listing the title ID, the type of book, and the price.
Select title_id, type, price
from titles
where type = ‘business’
or price > $18
title_id type price ----- ---------- -------------------- BU1032 business 19.99 BU1111 business 11.95 BU2075 business 2.99 BU7832 business 19.99 MC2222 mod_cook 19.99 PC1035 popular_comp 22.95 PC8888 popular_comp 20.00 PS1372 psychology 21.59 PS3333 psychology 19.99 TC3218 trad_cook 20.95 (10 row(s) affected)
The results I want must match at least one of two conditions: they must either be business books, in which case type = ‘business’, or they must cost more than $18, in which case price > $18. The first book in the list happens to satisfy both conditions, and that’s OK. It still qualifies for inclusion.
The second and third books in the result set satisfy only the type condition. This is OK, too, because I asked for rows that satisfy type or price.
An Example Using AND
My publisher looked at the list and decided to narrow it down a bit. Ten books were too many to promote, so now she wants a list of books that qualify for both criteria; that is, she wants books that are business books and that cost more than $18.
To satisfy this query, I’ll use nearly the same code, replacing the OR in the WHERE clause with an AND:
select title_id, type, price
from titles
where type = ‘business’
and price > $18
In this query, rows must match both the type and price criteria.
title_id type price -------- -------- -------------------- BU1032 business 19.99 BU7832 business 19.99 ( 2 row(s) affected)
This query specifies two conditions joined with the AND keyword, which means that both criteria must be met for the rows to be returned. Only two rows in the table are business books costing over $18.
These examples chained two conditions together using OR and AND. More complicated queries may require a lot of conditions, using combinations of AND and OR. T-SQL does not impose an upper limit on the number of conditions in a WHERE clause, although in very dramatic cases the server may run out of memory to process it!
Order of Operations in Boolean Expressions
Sometimes, you must use parentheses with boolean expressions to make the server interpret the expression correctly.
If you wanted to get an author who either lived outside of California or lived in California but did not have a contract, your WHERE clause would be this:
…where (state <> ‘CA’) or (state = ‘CA’ and contract = 0)
Without the parentheses, this query would return authors in California or not in California, as long as they did not have a contract. Without parentheses, the query would be resolved like this:
….where (state <> ‘CA’ or state = ‘CA’) and (contract = 0)
| Previous | Table of Contents | Next |