Subqueries with IN

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 is 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.