Subqueries used with in

Subqueries that are introduced with the keyword in return a list of zero or more results. For example, 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 Age Books 
Algodata Infosystems 

This statement is evaluated in two steps. The inner query returns the identification numbers of the publishers who have published business books, 1389 and 0736. These values are then substituted in the outer query, which finds the names that go with the identification numbers in the publishers table. The query looks like this:

select pub_name 
from publishers 
where pub_id in ("1389", "0736") 

Another way to formulate this query using a subquery is:

select pub_name 
from publishers 
where "business" in 
   (select type 
    from titles 
    where pub_id = publishers.pub_id) 

The expression following the where keyword in the outer query can be a constant as well as a column name. You can use other types of expressions, such as combinations of constants and column names.

The preceding queries, like many other subqueries, can be alternatively formulated as a join query:

select distinct pub_name 
from publishers, titles 
where publishers.pub_id = titles.pub_id 
and type = "business" 

Both this query and the subquery versions find publishers who have published business books. All are equally correct and produce the same results, though you may need to use the distinct keyword to eliminate duplicates.

However, one advantage of using a join query rather than a subquery is that a join query shows columns from more than one table in the result. For example, to include the titles of the business books in the result, 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 Age Books          You Can Combat Computer Stress!
Algodata Infosystems   Straight Talk About Computers 

Here is another example of a statement that you can formulate with either a subquery or a join query: “Find the names of all second authors who live in California and receive less than 30 percent of the royalties on a book.” Using a subquery, the statement is:

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 

The outer query produces a list of the 15 authors who live in California. The inner query is then evaluated, producing a list of the IDs of the authors who meet the qualifications.

More than one condition can be included 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 be expressed as a join.