Create a Subquery with EXISTS
When combined with EXISTS, an outer query checks for the existence of values within the inner query's result set. Now, using the Northwind sample database that comes with Access, suppose you want to learn which companies have placed orders. The solution is simpleyou don't need a complex EXISTS solution just yet. The following query returns a unique list of companies with records in the Orders table (see Figure 1
SELECT DISTINCT Orders.CustomerID
There are 89 companies that have placed at least one order (see Figure 1
). The Access query displays the company name instead of the CustomerID value because the CustomerID field in the Orders table is a lookup field. A lookup field is an Access data type that displays a value other than the value that's actually stored (SQL Server 2000 also supports lookup fields via an ADP front-end).
If you'd prefer to see both the CustomerID and the CompanyName, use the following join:
SELECT DISTINCT Customers.CustomerID, Customers.CompanyName
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
An EXISTS predicate could produce the same results, although there's no reason to do so. The following subquery can produce the same results as the previous join:
SELECT Customers.CustomerID, Customers.CompanyName
(SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID)
The EXISTS predicate is overkill for the job at hand, but it's useful to see the similarities between the EXISTS predicate and the join solution.
As a side note, you'll often find that a subquery can be eliminated by a join. Usually, joins are easier to write and often (but not always) perform better than a subquery so you might as well try a join first.