Check Whether Related Data Exists by Using Subqueries

The previous section showed how to retrieve related data by using joins. But sometimes you don’t want to retrieve data, you only want to check for the presence or absence of related data. In this case, you can use the EXISTS or NOT EXISTS operators. These operators are part of the WHERE clause that precedes a subquery. A subquery is a query that appears inside another query statement.

These query examples all use a subquery to check on related records to accounts.

• Which accounts don’t have a phone number?
• Which accounts have a phone number but no email address?
• How many accounts have a phone number but no email address?

For example, this query uses a subquery to return the accounts that don’t have a phone number.

This output shows the first few rows of the sample results.

You can use these predicates with subqueries.

• EXISTS
• NOT EXISTS
• IN