Ultimate Guide to SQL Patterns in Technical Interviews/

...

/

Existence Check

Existence Check

Learn about the Existence check pattern to check the data's availability.

We'll cover the following...

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.

-- EXISTS form
SELECT ...
FROM   MainTable m
WHERE  EXISTS (
        SELECT 1
        FROM RelatedTable r
        WHERE r.foreign_key = m.primary_key
       );
-- NOT EXISTS form
SELECT ...
FROM   MainTable m
WHERE  NOT EXISTS (
        SELECT 1
        FROM RelatedTable r
        WHERE r.foreign_key = m.primary_key
       );
Syntax of Existence check pattern

Alternatives:

  • IN / NOT IN

  • LEFT JOIN ... IS NULL (for NOT EXISTS)

Keywords

EXISTS, NOT EXISTS, IN, NOT IN, LEFT JOIN, IS NULL, correlated subquery

Problem structure

We use the Existence Check pattern when:

  • We want to test whether related records exist, rather than retrieve their details.

  • We’re checking for the presence or absence of related data, typically using EXISTS, NOT EXISTS, or a LEFT JOIN followed by a NULL check.

  • We’re answering questions like:

    • “Which items have matching records in another table?”

    • “Which records lack associated data?”

    • “Are there any rows in this group that meet a related condition?”

  • Look for keywords like: “has/doesn’t have,” “exists,” “no matching record,” “missing relationship,” or “any related row” to identify when this pattern applies.

Example use cases

1. Find customers who have placed at least one order.

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed.

Also, the following structure of the Customers table:

Field

Type

CustomerID

int

CustomerName

varchar(50)

Email

varchar(50)

Phone

varchar(15)

Address

varchar(100)

LastLogin

date

CreatedAt

timestamp

CustomerTier

enum('New','Regular','VIP')

ReferralID

int

LoyaltyPoints

int

LastPurchaseDate

date

IsChurnRisk

tinyint(1)

Where CustomerID is the primary key and ReferralID is self-referencing to CustomerID. The table contains information about customers.

Write an SQL query to return the names of customers who have placed at least one order.

Press + to interact
SELECT CustomerName
FROM   Customers c
WHERE  EXISTS (
        SELECT 1
        FROM Orders o
        WHERE o.CustomerID = c.CustomerID
       );

2. Find customers who have never placed an order.

Given the following structure of the Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and ...