Browse DevX
Sign up for e-mail newsletters from DevX


Using SQL's EXISTS Predicate to Identify Missing Data  : Page 2

Sometimes, knowing that data is missing is just as important as finding data that exists. Use SQL's EXISTS predicate to select data based on the presence (or absence) of other values.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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 simple—you 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):


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 FROM Customers WHERE EXISTS (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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date