Subqueries Introduced with IN

The result of a subquery introduced with IN (or with NOT IN, discussed later in this chapter) is a list of zero or more values. Once the subquery returns results, the outer query makes use of them.

This query finds the names of the publishers who have published business books:

SELECT pub_name
FROM publishers
WHERE pub_id IN
    (SELECT pub_id
    FROM titles
    WHERE type = 'business')

pub_name
--------------------
New Moon Books
Algodata Infosystems

(2 row(s) affected)

This statement is evaluated in two steps. First, the inner query returns the identification numbers of the publishers that have published business books (1389 and 0736). Second, these values are substituted into the outer query, which finds the names that go with the identification numbers in the publishers table. This step looks like this:

SELECT pub_name
FROM publishers
WHERE pub_id in ('1389', '0736')

Another difference in using a join rather than a subquery for this and similar problems is that the join lets you show columns from more than one table in the result. For example, if you want to include the titles of the business books in the result, you'd need to use the join version:

SELECT pub_name, title
FROM publishers, titles
WHERE publishers.pub_id = titles.pub_id
    AND type = 'business'

pub_name

title

--------------------

-------------------------------------------

Algodata Infosystems

The Busy Executive's Database Guide

Algodata Infosystems

Cooking with Computers: Surreptitious 
Balance Sheets

New Moon Books

You Can Combat Computer Stress!

Algodata Infosystems

Straight Talk About Computers

(4 row(s) affected)

This query shows that the join produces 4 rows, not just 2 like the preceding subquery.

Here's another example of a statement that can be formulated with either a subquery or a join. This query finds the names of all second authors who live in California and who receive less than 30 percent of the royalties for a book:

SELECT au_lname, au_fname
FROM authors
WHERE state = 'CA'
    AND au_id IN
        (SELECT au_id
        FROM titleauthor
        WHERE royaltyper < 30
            AND au_ord = 2)

au_lname

au_fname

----------

--------

MacFeather

Stearns

(1 row(s) affected)

The inner query is evaluated, producing the ID numbers of the three authors who meet the qualification. The outer query is then evaluated. Notice that you can include more than one condition in the WHERE clause of both the inner and the outer query.

Using a join, the query is expressed like this:

SELECT au_lname, au_fname
FROM authors, titleauthor
WHERE state = 'CA'
    AND authors.au_id = titleauthor.au_id
    AND royaltyper < 30
    AND au_ord = 2

A join can always be expressed as a subquery. A subquery can often, but not always, be expressed as a join. This is because joins are symmetric¾you can join table A to B in either order and get the same answer. Obviously the same is not true if a subquery is involved.