Browse DevX
Sign up for e-mail newsletters from DevX


Using SQL's EXISTS Predicate to Identify Missing Data

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

inding data in a relational database is usually a simple task, especially if you're simply comparing entries to a specific value, but queries rapidly become more complicated when you start hunting for data that's missing, in other words, data that could exist, but doesn't. Missing data is data related to existing data that simply doesn't occur. You could find missing values using a two-query solution, but SQL's EXISTS predicate can help you quickly find missing values with just one query—a subquery to be exact.

For example, you may create a query to find active customers by counting recent orders. However, finding inactive customers may be just as important; perhaps you can learn why they're inactive and perhaps turn them into active customers. In this article, you'll see how to use the SQL EXISTS predicate to find missing data. This article is aimed at the Jet and Transact-SQL (T-SQL) audience. We'll offer specific instructions when syntax or rules differ between the two dialects.

Fast Facts
This article refers to Jet and Transact-SQL. The examples work in Access 97, 2000, and 2002. The T-SQL syntax is correct for SQL Server 2000.

SQL's EXISTS predicate specifies a subquery and then compares a value against the existence of one or more rows in that subquery. The subquery returns True when the subquery contains any rows and False when it doesn't. However, all of that is probably clear as mud if you're not familiar with subqueries.

A subquery is a SELECT query within another SELECT, INSERT, UPDATE, DELETE query, or another subquery. In other words, the subquery returns a result set, which is then subject to the main query's conditions and criteria. Think of the subquery as a filter. The results of the embedded SELECT, also known as the inner query or inner select, become part of the search condition for the main query, otherwise known as the outer query or outer select.

The subquery can take many forms:

SELECT field1…, (subquery) AS alias FROM datasource SELECT fieldlist FROM datasource WHERE field comparison operator (subquery) SELECT fieldlist FROM datasource WHERE field ANY|SOME|ALL (subquery) WHERE expression [NOT] EXISTS (subquery)

In the preceding code, comparison operator equals one of the following: =, <>, <, >, >=, <, !>, !lt;, or <=. When you use one of these comparison operators, the subquery must return a single value. In this article, you'll work with the last form—the one that includes the EXISTS predicate.

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