Set Compare

Imagine we’re launching a new campaign targeting products that were once available but are now out of stock. To do this, we need to compare the current stock status with a snapshot from last month to identify which products have gone out of stock.

This is a classic example where comparing two datasets reveals valuable insights.

In this lesson, we’ll explore the Set compare pattern, which helps us identify what’s different, missing, or new between two datasets. We’ll use this pattern to solve practical problems like identifying customers who haven’t reordered, products missing from a supplier’s catalog, or pending orders versus those already shipped.

By the end of this lesson, we’ll be able to:

• Understand the purpose of set comparison in SQL.

• Compare two sets using joins, subqueries, or set operators.

• Identify matching, missing, or differing rows across datasets.

• Write reusable queries for real-world comparison problems.

Pattern overview

Category:

• Comparison Patterns

Intent:

To compare two sets of rows, either from the same table or different tables, to find:

• Matches

• Differences

• Items present in one but not the other

Motivation:

We often need to answer questions like:

• Which customers bought last month but not this month?

• Which products have changed prices?

• Which orders haven’t been shipped yet?

In all these cases, we’re comparing two sets and looking for overlap or gaps between them.

Also known as:

• Data siff

• Set delta

• A vs. B Compare

Structure

There are three common structures for set comparison:

1. EXISTS / NOT EXISTS Compare two sets using EXISTS to test presence.

2. JOINs with NULL check Use LEFT JOIN or FULL OUTER JOIN to find mismatches.

3. EXCEPT / INTERSECT (if supported) Use set operators for clean, intuitive comparison between queries.

Keywords

EXISTS, NOT EXISTS, IN, NOT IN, LEFT JOIN, IS NULL, INTERSECT, EXCEPT, UNION, DISTINCT

Problem structure

We use the Set compare pattern when:

• We’re working with two versions of data (e.g., current vs. past).

• We’re comparing two related datasets (e.g., products vs. orders).

• We want to analyze differences or similarities between two groups (e.g., referred vs. non-referred customers).

• We’re answering questions like:

  • What’s changed?

  • What’s missing?

  • What’s the overlap?

• Look for keywords like: “compare with,” “differences,” “changes,” “missing data,” or “intersection/overlap” to identify when this pattern applies.

Example use cases

1. Products ordered but have a limited quantity in stock.

Given the following structure of the Products table:

Where ProductID is the primary key and CategoryID is the foreign key from the Categories table. The table contains information about products.

Also, the following structure of the Order_Details table:

Where OrderDetailID is the primary key, OrderID is foreign key from Order table, and ProductID is foreign key from Products table. The table contains information about orders.

Write an SQL query to list all products that have less than 70 pieces in stock but have been ordered at least once.

Return the ...