A Special Kind of Subquery: WHERE EXISTS

The WHERE EXISTS test allows you to verify the existence of a row in a table and take action only if a matching row exists.

The EXISTS test is a type of correlated subquery. A correlated subquery runs once for every row that might be selected by the outer query. Don’t worry, an example’s coming!

Suppose that you want to know which business titles have sold books. To verify this, you need to look in titles for the book type, then in sales to see if there is a row for that title_id. You don’t want to select anything from sales; and if there are lots of sales for the book, you don’t want more than one row. WHERE EXISTS allows you to simply verify, “Yes, a row like that exists in this table.” Here’s the query:

select      title_id
from        titles
where       exists (select *
           from sales
             where     sales.title_id   =   titles.title_id)

title_id
--------
BU1032
BU1111
BU2075
BU7832
(4  row(s)  affected)

A correlated subquery is a subquery that references a table in the outer query. For every row in titles that matches the WHERE clause (type = ‘business’), the inner query is evaluated. If the inner query is true (a row exists in sales that joins to titles), the row is included in the result set.

This EXISTS test is nice when you don’t want the multiple rows that a join would produce. Consider this solution to the previous problem:

select      t.title_id
from       titles t, sales s
where        t.title_id = s.title_id
            and t.type = ‘business’

title_id
--------
BU1032
BU1032
BU1111
BU2075
BU7832
(5  row(s)  affected)

Because the sales table contains two rows for the BU1032 title, both appear in this result set. You could use the DISTINCT operator to eliminate duplicate rows, but that is much less efficient, performance-wise, than using WHERE EXISTS.

Note that when you use WHERE EXISTS, you must always use an asterisk in the SELECT’s column list.

The CASE Expression

I’d like to inject some personal enthusiasm: the CASE expression, defined by the ANSI SQL-92 standard, is my favorite new addition to MS SQL v6.x. The CASE expression is really neat. I love things that give me great performance, and CASE gives me the opportunity to design smoking SQL code. This small addition to T-SQL solves lots of problems that previously took multiple steps, temporary tables, and lots of smart coding to handle. Now, all you need is smart coding.

That’s Great, But What Does It Do?

The CASE expression evaluates a list of possibilities against each row in the table that satisfies the WHERE conditions and returns different results depending on which WHEN expression is matched.

There are two ways to use the CASE keyword. The first way is the simple method, which allows you to take different actions against the same expression. The second method, called “the searched CASE expression,” allows you to evaluate any boolean expression and take different actions when the expression is true.

OK, enough abstract definition. I’m sure you’d really like to see some examples so this all becomes clear.

A Simple CASE Example

Suppose that you would like to search through the employee table and create a report that shows the following:

•  The employee’s last name

•  An introductory greeting column named Greeting that is based on the employee’s job_lvl and uses the following values:

if job_lvl < 100, “Dear Publishing Industry Drone:”
if job_lvl >=100 and < 200, “Dear Honored Sir/Madam:”
if job_lvl > 200, “Dear Overpaid Executive:”

You would like the report to be sorted based on the new greeting; within the greeting, you want it sorted by last name. Here’s how the CASE expression does this in a single report, and by scanning the employee table only once, to boot:

select  lname,  CASE
            when job_lvl < 100
                  then “Dear Publishing Industry Drone:”
            when job_lvl < 200
                then “Dear Honored Sir/Madam:”
              else  “Dear Overpaid Executive:”
            end “Greeting”
from    employee
order   by   Greetings,   lname

lname               Greeting
---------------                ------------------------------
Ashworth            Dear Honored Sir/Madam:
Brown               Dear Honored Sir/Madam:
Domingues           Dear Honored Sir/Madam:
[… ]
Saveley             Dear Honored Sir/Madam:
Snyder              Dear Honored Sir/Madam:
Sommer              Dear Honored Sir/Madam:
Thomas              Dear Honored Sir/Madam:
Chang                Dear Overpaid Executive:
Cramer               Dear Overpaid Executive:
Devon                Dear Overpaid Executive:
Hernadez             Dear Overpaid Executive:
Karttunen            Dear Overpaid Executive:
Pontes               Dear Overpaid Executive:
Accorti              Dear Publishing Industry Drone:
Afonso               Dear Publishing Industry Drone:
[…]
Schmitt              Dear Publishing Industry Drone:
Smith                Dear Publishing Industry Drone:
Tonini               Dear Publishing Industry Drone:
(43   row(s) affected)

The CASE expression takes the place of a single column in an otherwise quite ordinary query. For each row in the employee table, the CASE expression is evaluated. The order of the WHEN expressions is significant: if a row satisfies the boolean WHEN expression, the code following the WHEN’s THEN is performed, and the next row is evaluated. This is why I can say WHEN < 100 for the first WHEN, and WHEN < 200 for the second.

If the row matches neither the first nor the second WHEN, the ELSE is evaluated. To specify a “catchall” CASE, put the expression after the ELSE. In this case, anybody with a job_lvl of 200 or better gets the ELSE expression.

When the CASE is done, after all the rows have been evaluated, there is a result set containing the names of employees and one of three greetings. This set is ordered by the greeting, then the employee’s last name. Note that if I ordered on job_lvl, I would get a much different ordering, and not what I wanted.