Duplicate Data Management — Learn DISTINCT ON, Window Functions, LAG, and EXCEPT in Practice

QUESTION 1

Get the Representative Row — Retrieve each customer's latest order in one step with DISTINCT ON

DISTINCT ONRepresentative rowORDER BY designPostgreSQL

Background ▼

In Basic Q4, we used CTE + ROW_NUMBER to take one representative row for each key. PostgreSQL also provides DISTINCT ON, which expresses the same operation in a single query. It keeps only the first row for each specified key after the rows are sorted by ORDER BY.

SELECT DISTINCT ON (customer_id)   -- key used to identify duplicates
  customer_id, order_id, ...
FROM orders
ORDER BY customer_id, ordered_at DESC;  -- sort by key → retention priority

ORDER BY determines which row to keep:DISTINCT ON keeps the first row for each key after sorting. Therefore, the leading expressions in ORDER BY must match the DISTINCT ON key, followed by an ordering that puts the row you want to retain first (use DESC for the latest row). ROW_NUMBER's PARTITION BY corresponds to DISTINCT ON's key, and its ORDER BY corresponds directly to the same priority ordering.

Problem ▼

The orders table contains multiple orders for the same customer. Using DISTINCT ON, retrieve only the latest order (the one with the newest ordered_at) for each customer_id.

Table used

▸ orders

order_id	customer_id	amount	ordered_at
1	101	1200	2025-04-01
2	102	3400	2025-04-02
3	101	5600	2025-04-10
4	103	980	2025-04-05
5	102	2100	2025-04-12
6	101	780	2025-04-03

Note: customer_id=101 has 3 orders and 102 has 2. Keep the latest order for each customer (101 → order_id=3, 102 → order_id=5).

Expected Output ▼

customer_id	order_id	amount	ordered_at
101	3	5600	2025-04-10
102	5	2100	2025-04-12
103	4	980	2025-04-05

Model Answer ▼

SELECT DISTINCT ON (customer_id)
  customer_id, order_id, amount, ordered_at
FROM orders
ORDER BY customer_id, ordered_at DESC;  -- ordering with the latest row first within each customer

/*
  Execution order:
  1. FROM orders                            → read the rows
  2. ORDER BY customer_id, ordered_at DESC  → sort so the latest row comes first for each customer
  3. DISTINCT ON (customer_id)              → keep the first row for each customer
  4. SELECT ...                             → project the columns and output the result
  */

Explanation (table transitions & key points) ▼

SELECT DISTINCT ON (customer_id)
  customer_id, order_id, amount, ordered_at
FROM orders
ORDER BY customer_id, ordered_at DESC;

LEGEND ▼

Rows read / loaded

① Source data ▼

FROM ordersRead the 6 rows of the orders table. customer_id=101 has 3 orders and 102 has 2, so multiple orders from the same customer appear. We will extract the latest order for each customer.

1 / 4

order_id	customer_id	amount	ordered_at
1	101	1200	2025-04-01
2	102	3400	2025-04-02
3	101	5600	2025-04-10
4	103	980	2025-04-05
5	102	2100	2025-04-12
6	101	780	2025-04-03

orders: 6 rows (duplicate customer_id values)

LEARNING POINTS

DISTINCT ON

Retrieve the first row for each key after sorting with a single SELECT

The standard ROW_NUMBER + WHERE rn=1 pattern in its shortest form
DISTINCT ON (key) ... ORDER BY key, priority

ORDER BY is the deduplication rule itself:With DISTINCT ON, designing ORDER BY means designing which row to keep. Put the key column (customer_id) first, followed by the priority (ordered_at DESC), to express “keep the latest row for each customer.” Switch to ASC to keep the oldest or amount DESC to keep the highest amount.

When to use ROW_NUMBER instead:DISTINCT ON is shorter and easier to read when you need only rn=1. Choose the ROW_NUMBER method from Basic Q4 when you also need rn=2 or later (for example, the second-newest order or a list of deletion candidates), or when you need portability across databases. The two methods are the abbreviated and general-purpose forms of the same pattern.

Tie-breaking for equal priorities:If a customer has two orders at the same time, which one comes first is undefined. In production, add a tie-breaker such as ORDER BY customer_id, ordered_at DESC, order_id DESC so that a unique column always determines the order and the query is reproducible.

ANTI-PATTERNS

The leading ORDER BY expression does not match the DISTINCT ON key:Writing DISTINCT ON (customer_id) ... ORDER BY ordered_at DESC makes PostgreSQL return the error “SELECT DISTINCT ON expressions must match initial ORDER BY expressions.” The syntactic rule is to put the key columns first in ORDER BY.

Forgetting that DISTINCT ON is PostgreSQL-specific:DISTINCT ON is a PostgreSQL extension, not standard SQL. It does not exist in MySQL or SQL Server, so code that must run on multiple databases or through BI tools should use the ROW_NUMBER method instead. Understand the trade-off between convenience and portability when choosing between them.

Field Notes

“The latest row for each key” is one of the most common duplicate-control tasks in practice. Examples include each customer's last login or purchase, the latest price for each product from a price-history table, and the latest status for each device. In PostgreSQL ad hoc analysis, DISTINCT ON is remarkably concise, so learning it as a second tool alongside ROW_NUMBER can make everyday investigation queries much shorter.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 2

List Duplicate Rows Without Collapsing Them — A Duplicate Flag with COUNT(*) OVER (PARTITION BY)

Window aggregationDuplicate detectionPARTITION BYCTE

Background ▼

Basic Q3's GROUP BY + HAVING returns only the duplicated values and their counts, collapsing the IDs and other details of the duplicate rows. With the window aggregate COUNT(*) OVER (PARTITION BY ...), you can attach the number of rows sharing the same key to every row without collapsing a single row.

COUNT(*) OVER (PARTITION BY product_name, maker) AS dup_cnt
-- Unlike GROUP BY, the rows stay intact and the aggregate is added as a column

GROUP BY collapses rows; OVER attaches to rows:GROUP BY reduces each group to one row, while an aggregate function OVER (PARTITION BY ...) adds the group aggregate beside every row while preserving all rows. This is the key shift when you want to list the duplicate rows themselves with all columns.

Problem ▼

The product master products contains duplicate registrations. List every row whose (product_name, maker) pair is duplicated, retaining all original columns plus the duplicate count (dup_cnt).

Table used

▸ products

product_id	product_name	maker	price
1	Eraser	ABC Stationery	120
2	A5 Notebook	XYZ Paper	300
3	Eraser	ABC Stationery	150
4	Ballpoint Pen	ABC Stationery	200
5	A5 Notebook	XYZ Paper	300
6	Eraser	ABC Stationery	110

Note: Eraser / ABC Stationery is a 3-row duplicate with different prices, while A5 Notebook / XYZ Paper is a 2-row exact duplicate. Exclude the non-duplicate product_id=4 and order the result by descending dup_cnt, then product_id.

Expected Output ▼

product_id	product_name	maker	price	dup_cnt
1	Eraser	ABC Stationery	120	3
3	Eraser	ABC Stationery	150	3
6	Eraser	ABC Stationery	110	3
2	A5 Notebook	XYZ Paper	300	2
5	A5 Notebook	XYZ Paper	300	2

Model Answer ▼

WITH flagged AS (
  SELECT
    product_id, product_name, maker, price,
    COUNT(*) OVER (
      PARTITION BY product_name, maker   -- composite duplicate key
    ) AS dup_cnt
  FROM products
)
SELECT
  product_id, product_name, maker, price, dup_cnt
FROM flagged
WHERE dup_cnt > 1            -- filter to duplicate rows only
ORDER BY dup_cnt DESC, product_id;

/*
  Execution order:
  1. CTE(flagged)                       → read products
  2. COUNT(*) OVER (...)                → attach each group's count to every row
  3. FROM flagged                       → reference the derived table
  4. WHERE dup_cnt > 1                  → keep duplicate rows only
  5. SELECT ...                         → project the columns
  6. ORDER BY dup_cnt DESC, product_id  → sort and output
  */

Explanation (table transitions & key points) ▼

WITH flagged AS (
  SELECT
    product_id, product_name, maker, price,
    COUNT(*) OVER (
      PARTITION BY product_name, maker
    ) AS dup_cnt
  FROM products
)
SELECT
  product_id, product_name, maker, price, dup_cnt
FROM flagged
WHERE dup_cnt > 1
ORDER BY dup_cnt DESC, product_id;

LEGEND ▼

Rows read / loaded

① CTE — Source data ▼

FROM productsRead the 6 rows of the products table. By the (product_name, maker) pair, Eraser / ABC Stationery has 3 rows and A5 Notebook / XYZ Paper has 2. The duplicates with different prices (1, 3, 6) are a type of duplicate that DISTINCT cannot remove.

1 / 4

product_id	product_name	maker	price
1	Eraser	ABC Stationery	120
2	A5 Notebook	XYZ Paper	300
3	Eraser	ABC Stationery	150
4	Ballpoint Pen	ABC Stationery	200
5	A5 Notebook	XYZ Paper	300
6	Eraser	ABC Stationery	110

products: 6 rows (duplicate composite keys)

LEARNING POINTS

WINDOW DUPLICATE FLAG

An advanced duplicate detector — attach dup_cnt to every row without collapsing rows

Detect duplicates while preserving the row details lost by GROUP BY + HAVING
COUNT(*) OVER (PARTITION BY key) → WHERE dup_cnt > 1

The decisive difference from GROUP BY:Basic Q3's GROUP BY email HAVING COUNT(*) > 1 shows only which values are duplicated. To see the contents of those duplicate rows, you had tore-JOIN the source table (a self-join)With a window aggregate, you candetect duplicates and preserve the rows in one scanand keep both the query and execution cost simple.

You can expose duplicates where some columns differ:Because product_id=1, 3, and 6 have different prices, DISTINCT, which checks exact row matches, cannot detect or remove them. With the composite key PARTITION BY product_name, maker you define which rows count as the same product for business purposes, producing a list that leads to the next cleansing decision:which price is correct.

Combine it with ROW_NUMBER:In the same CTE, write ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) AS rn to obtain both the duplicate list (dup_cnt>1, for investigation) and the deletion-candidate list (rn>1, for removal)from one CTEThis is a core duplicate-management pattern: detection (this question) leads directly to removal (Basic Q4).

ANTI-PATTERNS

Writing a window function directly in WHERE: WHERE COUNT(*) OVER (...) > 1 is a syntax error. Window functions are evaluated after WHERE (at the SELECT stage), so you must first materialize the value in a CTE or subquery and filter it in the outer query. This two-stage structure is required, just as with ROW_NUMBER in Basic Q4.

Choosing PARTITION BY keys carelessly and causing false positives: Partitioning by product_name alone treats same-named products from different makers (such as OEM products) as duplicates. Conversely, including price in the key misses the duplicates among 1, 3, and 6. Define what counts as the same item for the business before writing the query; the wrong key produces wrong detections even with correct syntax.

Field Notes

This duplicate-flag column pattern is a Swiss Army knife for investigations. Examples include duplicate-candidate reports before master-data consolidation (shareable with all columns), identifying sales details imported twice, and preprocessing for entity-resolution candidate scoring. A GROUP BY summary cannot answer “which record should we fix?”, but a row list with dup_cnt becomesa checklist for the correction work.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 3

Duplicate Payment Investigation Report — Detect with a Composite Key and Aggregate IDs with STRING_AGG

STRING_AGGComposite keyHAVINGInvestigation report

Background ▼

When the same user makes multiple payments for the same amount on the same day, the suspected duplicate payment is detected with a composite key. STRING_AGG can then aggregate the payment IDs in each duplicate group into one comma-separated cell, turning the result directly into an investigation report.

GROUP BY user_id, amount, paid_at        -- Define identity with a composite key
HAVING COUNT(*) > 1
-- an aggregate function that concatenates group values as text
STRING_AGG(payment_id::TEXT, ', ' ORDER BY payment_id)

STRING_AGG is an aggregate function:Just as SUM adds numbers, STRING_AGG aggregates group values into a single string joined with a delimiter. Cast numeric columns with ::TEXT; placing ORDER BY inside the function also controls concatenation order (it corresponds to MySQL's GROUP_CONCAT).

Problem ▼

Investigate suspected duplicate payments in payments. For groups with duplicate (user_id, amount, paid_at) combinations, return the count (cnt) and the matching payment IDs (payment_ids, comma-separated in ascending order).

Table used

▸ payments

payment_id	user_id	amount	paid_at
101	1	5000	2025-05-01
102	2	3000	2025-05-01
103	1	5000	2025-05-01
104	3	8000	2025-05-02
105	2	3000	2025-05-03
106	1	5000	2025-05-01
107	3	8000	2025-05-02

Note: user_id=1 has 3 payments for the same amount on the same day (101, 103, 106), and user_id=3 has 2 (104, 107). The 2 payments for user_id=2 are not duplicates because their dates differ.

Expected Output ▼

user_id	amount	paid_at	cnt	payment_ids
1	5000	2025-05-01	3	101, 103, 106
3	8000	2025-05-02	2	104, 107

Model Answer ▼

SELECT
  user_id, amount, paid_at,
  COUNT(*) AS cnt,
  STRING_AGG(payment_id::TEXT, ', ' ORDER BY payment_id) AS payment_ids
FROM payments
GROUP BY user_id, amount, paid_at   -- define duplicates by the combination of 3 columns
HAVING COUNT(*) > 1            -- 2 or more = suspected duplicate payment
ORDER BY user_id;

/*
  Execution order:
  1. FROM payments                      → read the rows
  2. GROUP BY user_id, amount, paid_at  → group by the 3-column combination
  3. HAVING COUNT(*) > 1                → keep only duplicate groups
  4. COUNT(*) / STRING_AGG(...)         → calculate the count and concatenate payment IDs
  5. SELECT ...                         → project the columns
  6. ORDER BY user_id                   → sort and output
  */

Explanation (table transitions & key points) ▼

SELECT
  user_id, amount, paid_at,
  COUNT(*) AS cnt,
  STRING_AGG(payment_id::TEXT, ', ' ORDER BY payment_id) AS payment_ids
FROM payments
GROUP BY user_id, amount, paid_at
HAVING COUNT(*) > 1
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① Source data ▼

FROM paymentsRead the 7 rows of the payments table. user_id=1 has three 5,000-yen payments on the same day, and user_id=3 has two 8,000-yen payments on the same day. user_id=2 has two 3,000-yen payments on different dates, so they are separate legitimate payments.

1 / 5

payment_id	user_id	amount	paid_at
101	1	5000	2025-05-01
102	2	3000	2025-05-01
103	1	5000	2025-05-01
104	3	8000	2025-05-02
105	2	3000	2025-05-03
106	1	5000	2025-05-01
107	3	8000	2025-05-02

payments: 7 rows

LEARNING POINTS

COMPOSITE KEY + STRING_AGG

Define identity with a composite key and aggregate the target IDs into one cell

A practical form where the detection result is already an investigation report
GROUP BY 3 columns → HAVING > 1 → STRING_AGG(id)

The business defines what counts as a duplicate:Here, duplicate identity is defined as the three columns (user_id, amount, paid_at) matching. Using only user_id would falsely flag repeat customers; using only (user_id, amount) would falsely flag recurring charges on different days. Unlike Basic Q3's single-key detection, the design of the key combination determines detection accuracy.

STRING_AGG connects detection to identification:HAVING alone tells you only that a suspicion exists. Aggregating payment_id with STRING_AGG identifies the specific records to cancel or refund in one query. The ORDER BY payment_id inside the function stabilizes concatenation order and keeps the report reproducible.

Combine aggregate functions:You can also place MIN(payment_id) (the representative to keep), SUM(amount) (the affected amount), and ARRAY_AGG(payment_id) (pass the IDs to a program as an array) in the same SELECT. The idea of putting multiple aggregates on one GROUP BY is the key to consolidating an investigation into one query.

ANTI-PATTERNS

Grouping by raw timestamps and detecting nothing:If paid_at is a TIMESTAMP such as 2025-05-01 10:23:45.120, millisecond differences put every row in a separate group and no duplicates are detected. For day-level matching use paid_at::DATE; for a repeat payment within 5 minutes, use date_trunc('hour', ...) or a time-difference condition—round the granularity before comparing as a standard practice.

Trying to split STRING_AGG output back apart in SQL:If a later query starts parsing the concatenated payment_ids string with LIKE or split, that is a design smell. String aggregation is for final reports that people read. If downstream processing needs rows, keep them as rows with the window method from Q2 instead of aggregating them.

Field Notes

Composite-key detection plus ID aggregation is a standard format for fraud and anomaly investigations. Examples include duplicate-payment and duplicate-billing audit reports, suspected multiple accounts for one person (name × date of birth × phone), and identifying import batches duplicated by a job running twice. The downstream process is much faster with a report that includes target IDs rather than counts alone.carry the information needed for the next action in the detection query as a rule of thumb.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 4

Remove Only Consecutive Duplicates — Extract Change Points with LAG and IS DISTINCT FROM

LAGChange-point extractionIS DISTINCT FROMTime series

Background ▼

When the same value continues through monitoring-log data, you need to remove only rows equal to the immediately preceding value (compress consecutive duplicates), not deduplicate the entire table. DISTINCT would also remove the second OK in “OK → ERROR → OK,” so use LAG to bring in the previous value and compare it.

LAG(status) OVER (ORDER BY logged_at) AS prev_status
-- In time-series order, bring the previous row's status to the current row (the first row is NULL)

WHERE prev_status IS DISTINCT FROM status   -- a “distinct” operator that compares NULL correctly

The NULL comparison trap and IS DISTINCT FROM:The first row's prev_status is NULL. prev_status <> status makes the comparison with NULL UNKNOWN, causing the first row to disappear. IS DISTINCT FROM treats NULL as a value, so NULL IS DISTINCT FROM 'OK' is true and the first row is safely retained.

Problem ▼

The server-monitoring log status_logs records a status every 5 minutes, so identical statuses appear in long runs. Extract only rows where the status changed from the previous row (change points) and compress the log.

Table used

▸ status_logs

log_id	status	logged_at
1	OK	09:00
2	OK	09:05
3	ERROR	09:10
4	ERROR	09:15
5	ERROR	09:20
6	OK	09:25
7	OK	09:30

Note: keep 3 rows: log_id=1 (initial), 3 (OK → ERROR), and 6 (ERROR → OK). The OK at log_id=7 is part of the second OK run, but it is excluded because the previous status is also OK. This differs from whole-table deduplication.

Expected Output ▼

log_id	status	logged_at
1	OK	09:00
3	ERROR	09:10
6	OK	09:25

Model Answer ▼

WITH with_prev AS (
  SELECT
    log_id, status, logged_at,
    LAG(status) OVER (ORDER BY logged_at) AS prev_status  -- previous row's status
  FROM status_logs
)
SELECT
  log_id, status, logged_at
FROM with_prev
WHERE prev_status IS DISTINCT FROM status  -- different from the previous row = change point only (the first row remains)
ORDER BY logged_at;

/*
  Execution order:
  1. CTE(with_prev)                             → read status_logs
  2. LAG(status) OVER (...)                     → attach the previous status
  3. FROM with_prev                             → reference the derived table
  4. WHERE prev_status IS DISTINCT FROM status  → keep only change points
  5. SELECT ...                                 → project the columns
  6. ORDER BY logged_at                         → output in time-series order
  */

Explanation (table transitions & key points) ▼

WITH with_prev AS (
  SELECT
    log_id, status, logged_at,
    LAG(status) OVER (ORDER BY logged_at) AS prev_status
  FROM status_logs
)
SELECT
  log_id, status, logged_at
FROM with_prev
WHERE prev_status IS DISTINCT FROM status
ORDER BY logged_at;

LEGEND ▼

Rows read / loaded

① CTE — Source data ▼

FROM status_logsRead the 7 rows of status_logs. The same status continues as OK twice → ERROR three times → OK twice. We want only the rows where the state changes.

1 / 4

log_id	status	logged_at
1	OK	09:00
2	OK	09:05
3	ERROR	09:10
4	ERROR	09:15
5	ERROR	09:20
6	OK	09:25
7	OK	09:30

status_logs: 7 rows (consecutive duplicates present)

LEARNING POINTS

CONSECUTIVE DEDUP

Compress consecutive duplicates — compare with the previous row and keep only change points

Preserve values that reappear, which DISTINCT would remove, together with their time-series context
LAG(value) OVER (ORDER BY time) → compare with IS DISTINCT FROM

Whole-table duplicates and consecutive duplicates are different: DISTINCT and GROUP BY combine equal values across the entire table, so they can remove the OK after recovery (log_id=6) and make the system appear to remain in ERROR. Deduplicating time-series data means comparing with the previous row based on an explicit order; that shift in thinking is the essence of this question.

LAG / LEAD are the basic tools for comparing rows: LAG(column, n) retrieves the value n rows earlier (1 when omitted), while LEAD retrieves the value n rows later. The order in OVER (ORDER BY ...) defines “previous” and “next,” so omitting ORDER BY is prohibited. Beyond change-point extraction, this applies to analyses of differences between adjacent rows, such as day-over-day comparisons and time since the previous purchase.

IS DISTINCT FROM is a NULL-safe comparison: With ordinary = / <>, a NULL on either side yields UNKNOWN and the row is filtered out by WHERE. IS DISTINCT FROM (negated as IS NOT DISTINCT FROM) behaves intuitively: NULL and NULL are equal; NULL and a value are different. It works especially well with LAG's leading NULL and prevents comparison bugs involving NULL.

ANTI-PATTERNS

Comparing with <> makes the first row disappear:Writing WHERE prev_status <> status makes the comparison for the first row (whose prev_status is NULL) UNKNOWN and silently removes the initial record. This representative NULL-comparison bug is hard to notice because no error is raised. Protect against it with IS DISTINCT FROM or prev_status IS NULL OR prev_status <> status.

Processing multiple series with one LAG:If you forget PARTITION BY when real data contains multiple servers, logs from different servers are compared as “previous rows” and false change points appear. When a series key such as server_id exists, partition by series with LAG(status) OVER (PARTITION BY server_id ORDER BY logged_at).

Field Notes

Compressing consecutive duplicates is a standard operation on logs and history data. Examples include generating status-transition histories for incidents, extracting membership-tier or contract-plan changes from snapshot tables, and thinning runs of equal sensor values to save storage. Often only the moment of change matters for decisions; the three steps LAG → compare → filter can be reused as a template.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 5

Table Reconciliation and Difference Detection — Validate a Migration with EXCEPT × UNION ALL

EXCEPTSet operationsData reconciliationUNION ALL

Background ▼

Following Basic Q5's UNION (set union), this question uses the set difference EXCEPT.A EXCEPT B returns rows present in A but not B and is central to validating table migrations and synchronization.

SELECT ... FROM members_old
EXCEPT                       -- in old but not new = missing from migration
SELECT ... FROM members_new;

EXCEPT is also part of duplicate management: Like UNION, EXCEPT also determines row identity by the combination of all columns and removes duplicates from its result (use EXCEPT ALL when you need to retain them). Unlike NOT IN, it handles NULL correctly as an equal value, making it the safest form for reconciliation. Use INTERSECT when you need only the common rows.

Problem ▼

The member table was migrated from members_old to members_new. Detect missing rows (in old but not new: missing) and extra rows (in new but not old: extra) in one result labeled with diff_type.

Table used

▸ members_old (source)

member_id	email
1	tanaka@ex.com
2	sato@ex.com
3	suzuki@ex.com
4	yamada@ex.com

▸ members_new (target)

member_id	email
1	tanaka@ex.com
2	sato@ex.com
4	yamada@ex.com
5	kato@ex.com

Note: member_id=3 (Suzuki) is missing from the migration, while member_id=5 (Kato) is an extra row found only in the target. Use EXCEPT in both directions and combine the results into one report with UNION ALL.

Expected Output ▼

diff_type	member_id	email
extra	5	kato@ex.com
missing	3	suzuki@ex.com

Model Answer ▼

SELECT 'missing' AS diff_type, member_id, email
FROM (
  SELECT member_id, email FROM members_old
  EXCEPT                                -- old − new = missing
  SELECT member_id, email FROM members_new
) AS d1
UNION ALL                               -- stack the two directional differences (they cannot overlap, so use ALL)
SELECT 'extra' AS diff_type, member_id, email
FROM (
  SELECT member_id, email FROM members_new
  EXCEPT                                -- new − old = extra
  SELECT member_id, email FROM members_old
) AS d2
ORDER BY diff_type, member_id;

/*
  Execution order:
  1. d1: EXCEPT                     → in old but not new (missing)
  2. d2: EXCEPT                     → in new but not old (extra)
  3. add the label column                        → assign missing / extra
  4. UNION ALL                      → concatenate the differences vertically
  5. ORDER BY diff_type, member_id  → sort and output
  */

Explanation (table transitions & key points) ▼

SELECT 'missing' AS diff_type, member_id, email
FROM (
  SELECT member_id, email FROM members_old
  EXCEPT
  SELECT member_id, email FROM members_new
) AS d1
UNION ALL
SELECT 'extra' AS diff_type, member_id, email
FROM (
  SELECT member_id, email FROM members_new
  EXCEPT
  SELECT member_id, email FROM members_old
) AS d2
ORDER BY diff_type, member_id;

LEGEND ▼

Rows read / loaded

① source — members_old ▼

SELECT member_id, email FROM members_oldThis is the source table with 4 rows. All 4 should exist in the target. We use it as the reconciliation baseline.

1 / 5

member_id	email
1	tanaka@ex.com
2	sato@ex.com
3	suzuki@ex.com
4	yamada@ex.com

members_old: 4 rows (expected state)

LEARNING POINTS

SET DIFFERENCE

Bidirectional reconciliation with EXCEPT — detect missing and extra rows in one query

A standard migration and synchronization check: “0 rows means a match” is the pass condition
(old EXCEPT new) UNION ALL (new EXCEPT old)

Always reconcile in both directions:old EXCEPT new alone misses extra rows, and comparing row counts misses cases like this one where missing and extra rows cancel out. Only when both directions of EXCEPT return 0 rows can you say that the two tables are identical. This symmetric checking pattern is worth memorizing.

Choosing between NOT IN / NOT EXISTS and EXCEPT: You can write the same reconciliation with NOT EXISTS, but EXCEPT completes multi-column matching by simply listing all columns to compare in SELECT. It also treats NULL as an equal value without the common NOT IN trap. Use NOT EXISTS when you also need non-key columns such as update timestamps in the result.

Applying the UNION / UNION ALL choice:Missing and extra are disjoint by definition (the same row cannot appear in both), so choosing UNION ALL instead of UNION is the practical application of the criterion from Basic Q5. Make a habit of asking “could these overlap?” before deciding whether to use ALL; it balances correctness and speed.

ANTI-PATTERNS

A column-order mismatch makes every row a difference:EXCEPT matches columns by position. If one side uses SELECT email, member_id in a different order, even rows that should match appear as differences (and compatible types may produce no error). For set operations, make the SELECT column lists on both sides identical, character for character.

Including columns that can change in the comparison:Including a post-migration column such as last_login in SELECT falsely flags many logically identical members as differences. In reconciliation, compare only the columns that define identity, then inspect the details of differing rows separately as a second step; this two-stage process is a practical tip.

Field Notes

EXCEPT reconciliation is a final safeguard for data quality. Examples include full-row validation after a database migration or replacement, a daily batch detecting synchronization drift between production and a DWH, and verifying that query results did not change before and after refactoring (old SQL result EXCEPT new SQL result). The last case—self-checking a query rewrite—is immediately useful:0 rows in both EXCEPT directions means the rewrite is safe. This gives you a mechanical pass condition.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.