Set Compare

Learn about the Set compare pattern in SQL, which helps identify differences, matches, and missing data between two datasets for effective business analysis.

We'll cover the following

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:

Level up your interview prep. Join Educative to access 70+ hands-on prep courses.