Subqueries with IN

The result of a subquery introduced with IN (or with NOT IN) is a list of zero or more values. After the subquery returns results, the outer query makes use of them.

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

USE pubs

SELECT pub_name

FROM publishers

WHERE pub_id IN

    (SELECT pub_id

    FROM titles

    WHERE type = 'business')

Here is the result set:

pub_name                                

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

Algodata Infosystems                    

New Moon Books                          

(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 publishers.

USE pubs

SELECT pub_name

FROM publishers

WHERE pub_id in ('1389', '0736')

One 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 must use a join version.

USE pubs

SELECT pub_name, title

FROM publishers INNER JOIN titles ON publishers.pub_id = titles.pub_id

    AND type = 'business'

Here is the result set:

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 the join produces four rows, not two as in the preceding subquery.

Here’s another example of a query 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.

USE pubs

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)

Here is the result set:

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 subquery qualifications. 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 same query is expressed like this:

USE pubs

SELECT au_lname, au_fname

FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id

WHERE state = 'CA'

    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. The same is not true if a subquery is involved.