Replacing joins with subqueries

A join returns a result table constructed from data from multiple tables. You can also retrieve the same result table using a subquery. A subquery is simply a SELECT statement within another select statement. This is a useful tool in building more complex and informative queries.

For example, suppose you need a chronological list of orders and the company that placed them, but would like the company name instead of their customer ID. You can get this result using a join as follows:

Using a join

To list the order_id, OrderDate, and CompanyName for each order from the beginning of 1994, type:

SELECT	  SalesOrders.ID,
SalesOrders.OrderDate,
Customers.CompanyName
FROM SalesOrders 
KEY JOIN Customers
WHERE OrderDate > '1994/01/01'
ORDER BY OrderDate

ID	OrderDate	CompanyName
2131	2000-01-02	BoSox Club
2126	2000-01-03	Leisure Time
2065	2000-01-03	Bloomfields
2127	2000-01-06	Creative Customs Inc.
2135	2000-01-06	East Coast Traders
2129	2000-01-07	Hospital Gifts
2132	2000-01-08	The Pep Squad
2136	2000-01-09	Divas Design
2133	2000-01-10	The Road Side Inn
2083	2000-01-13	Pollys Custom Design

Using an outer join

The join in previous sections of the tutorial is more fully called an inner join.

You specify an outer join explicitly. In this case, a GROUP BY clause is also required:

SELECT  CompanyName,
MAX( SalesOrders.ID ),State
FROM Customers
KEY LEFT OUTER JOIN SalesOrders 
WHERE State = 'WA'
GROUP BY CompanyName, State

CompanyName	MAX(SalesOrders.ID)	State
Its a Hit!	(NULL)	WA
Custom Designs	2547	WA

Using a subquery

To list order items for products low in stock, type:

SELECT *
FROM SalesOrderItems
WHERE ProductID IN
 ( SELECT ID
FROM Products
WHERE Quantity < 20 )
ORDER BY ShipDate DESC

ID	LineID	ProductID	Quantity	ShipDate
2082	1	401	48	2001-07-09
2053	1	401	60	2001-06-30
2125	2	401	36	2001-06-28
2027	1	401	12	2001-06-17
2062	1	401	36	2001-06-17
2023	1	401	24	2001-06-09
2031	2	401	48	2001-06-02
2073	1	401	24	2001-06-02
2647	1	401	36	2001-05-26
2639	1	401	36	2001-05-19

The subquery in the statement is the phrase enclosed in parentheses:

(  SELECT ID
FROM Products
WHERE Quantity < 20 )

By using a subquery, the search can be carried out in just one query, instead of using one query to find the list of low-stock products and a second to find orders for those products.

The subquery makes a list of all values in the id column in the product table satisfying the WHERE clause search condition.

Remember the following notes about subqueries:

Subqueries may also be useful in cases where you may have trouble constructing a join, such as queries that use the NOT EXISTS predicate.
Subqueries can only return one column.
Subqueries are allowed only as arguments of comparisons, IN, or EXISTS clauses.
Subqueries cannot be used inside an outer join ON clause.