Subqueries that are introduced with the keyword in return a list of zero or more results.
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.