Browse DevX
Sign up for e-mail newsletters from DevX


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

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 NOT EXISTS
As long as you're looking for existing data, the solution should be relatively simple. However, looking for data that's not there can complicate things. Let's suppose you want to know which customers haven't placed an order. The earlier query returned a unique list of 89 customers. Because the Customers table (SELECT COUNT(*) FROM Customers) contains 91 records, you know that two customers have not placed even one order—but which two? You could visually compare the unique list of 89 customers to all the entries in the Customers table, but that would be tedious, and fortunately for us, unnecessary.

Finding customers with orders required only a unique list of customers from the Orders table. There's no such one-table solution for returning a list of non-ordering customers. Even a simple join won't help in this case. The solution is very similar to the previous EXISTS statement with one exception—the inclusion of SQL's NOT operator:

SELECT Customers.CustomerID, Customers.CompanyName FROM Customers WHERE NOT EXISTS (SELECT * FROM Orders WHERE Orders.CustomerID = Customers.CustomerID)

This subquery returns two rows—the two customers who haven't ordered anything (see Figure 2).

Figure 2: Combining the EXISTS predicate with the NOT operator returns the two customers who haven't placed an order.
The inner query compares the CustomerID value for each record in the Orders table to the CustomerID values in the Customers table. When a matching value is found, the inner query returns True. A True value means the customer has placed an order, but the NOT operator preceding the EXISTS predicate eliminates that particular value from the outer query's result set.

When the inner query doesn't find any matching records, it returns False. Remember, a False value means the customer hasn't ordered. The NOT operator then negates that False value, so the outer query can include that record in its result set. The EXISTS predicate finds values that do exist; preceding that predicate with the NOT operator finds those that don't.

Missing data can often be just as informative as data that's available. Unfortunately, learning what's missing isn't always as easy as finding existing data. Combining SQL's EXISTS predicate with the NOT operator solves the problem and quickly turns up missing information.

Susan Sales Harkins is an independent consultant with an expertise in Access. SQL: Access to SQL Server, her latest book, was released in January 2002. Currently, Susan writes for a number of publishers including Element K Journals, PC Magazine and CNet. You can reach her at harkins@iglou.com.
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