Suppose you need a chronological list of orders and the company that placed them, but would like the company name instead of their Customers ID. You can get this result using a join.
To list the order ID, date, and company name for each order since the beginning of 2001, execute the following query:
SELECT SalesOrders.ID, SalesOrders.OrderDate, Customers.CompanyName FROM SalesOrders KEY JOIN Customers WHERE OrderDate > '2001/01/01' ORDER BY OrderDate;
|2488||1/15/2001||North Land Trading|
|2513||2/05/2001||The Hat Company|
The following statement obtains the same results using a subquery instead of a join:
SELECT SalesOrders.ID, SalesOrders.OrderDate, ( SELECT CompanyName FROM Customers WHERE Customers.ID = SalesOrders.CustomerID ) FROM SalesOrders WHERE OrderDate > '2001/01/01' ORDER BY OrderDate;
The subquery refers to the CustomerID column in the SalesOrders table even though the SalesOrders table is not part of the subquery. Instead, the SalesOrders.CustomerID column refers to the SalesOrders table in the main body of the statement. This is called an outer reference. Any subquery that contains an outer reference is called a correlated subquery.
A subquery can be used instead of a join whenever only one column is required from the other table. (Recall that subqueries can only return one column.) In this example, you only needed the CompanyName column, so the join could be changed into a subquery.
To list all customers in Washington state, together with their most recent order ID, execute the following query:
SELECT CompanyName, State, ( SELECT MAX( ID ) FROM SalesOrders WHERE SalesOrders.CustomerID = Customers.ID ) FROM Customers WHERE State = 'WA';
|It's a Hit!||WA||(NULL)|
The It's a Hit! company placed no orders, and the subquery returns NULL for this customer. Companies who have not placed an order are not listed when inner joins are used.
You could also specify an outer join explicitly. In this case, a GROUP BY clause is also required.
SELECT CompanyName, State, MAX( SalesOrders.ID ) FROM Customers KEY LEFT OUTER JOIN SalesOrders WHERE State = 'WA' GROUP BY CompanyName, State;