You may have noticed as you read through this chapter that many of the queries that were written using subqueries could also have been written as multitable queries, or joins. This is often the case, and SQL allows you to write the query either way. This example illustrates the point:
List the names and ages of salespeople who work in offices in the Western region.
SELECT NAME, AGE
FROM SALESREPS
WHERE REP_OFFICE IN (SELECT OFFICE
FROM OFFICES
WHERE REGION = ‘Western’)
NAME AGE
————– —-
Sue Smith 48
Larry Fitch 62
Nancy Angelli 49
This form of the query closely parallels the stated request. The subquery yields a list of offices in the Western region, and the main query finds the salespeople who work in one of the offices in the list. Here is an alternative form of the query, using a two-table join:
List the names and ages of salespeople who work in offices in the Western region.
SELECT NAME, AGE
FROM SALESREPS, OFFICES
WHERE REP_OFFICE = OFFICE
AND REGION = ‘Western’
NAME AGE
————– —-
Sue Smith 48
Larry Fitch 62
Nancy Angelli 49
This form of the query joins the SALESREPS table to the OFFICES table to find the region where each salesperson works, and then eliminates those who do not work in the Western region.
Either of the two queries will find the correct salespeople, and neither one is right or wrong. Many people will find the first form (with the subquery) more natural, because the English request doesn’t ask for any information about offices, and because it seems a little strange to join the SALESREPS and OFFICES tables to answer the request. Of course if the request is changed to ask for some information from the OFFICES table:
List the names and ages of the salespeople who work in offices in the Western region and the cities where they work.
the subquery form will no longer work, and the two-table query must be used. Conversely, many queries with subqueries cannot be translated into an equivalent join. Here is a simple example:
List the names and ages of salespeople who have above average quotas.
SELECT NAME, AGE FROM SALESREPS
WHERE QUOTA > (SELECT AVG(QUOTA)
FROM SALESREPS)
NAME AGE
————- —-
Bill Adams 37
Sue Smith 48
Larry Fitch 62
In this case, the inner query is a summary query and the outer query is not, so there is no way the two queries can be combined into a single join.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.