Existence Check

Sometimes, we need to find out whether something exists, not necessarily what or how many. For example, let’s say we want to email all customers who haven’t placed any orders yet. We don’t care about the details of missing orders, just the fact that they’re missing.

Or, we may want to highlight products that do have at least one supplier assigned. These types of questions appear all the time when exploring or cleaning data.

In this lesson, we’ll explore the Existence check pattern, a fundamental approach for filtering records based on the presence or absence of related data. This pattern is especially powerful in real-world situations where relationships between tables matter more than individual values.

By the end of this lesson, we will:

• Understand the core concept of existence checks in SQL.

• Learn how to use EXISTS, NOT EXISTS, and alternatives like IN, NOT IN, and LEFT JOIN ... IS NULL.

• Explore practical examples from our course database (e.g., finding customers without orders or products with suppliers).

• Practice the pattern on real-world problems.

Let’s begin by understanding where this pattern fits into the broader SQL landscape.

Pattern overview

Category:

• Filtering patterns

Intent:

To filter records based on whether related data exists or does not exist in another table.

Motivation:

In relational databases, many decisions hinge on the presence or absence of related records. Whether we’re identifying customers who haven’t ordered yet, finding products with suppliers, or excluding items that appear in another list, we often need to ask “Does a related record exist?”—and that’s exactly what this pattern helps us solve.

Also known as:

• Semi join

• Anti join

• Subquery filter

• Relationship filter

Structure

We typically use the EXISTS or NOT EXISTS clauses inside a WHERE condition, paired with a correlated subquery.