Multiset Difference: Remove Only Matching Duplicate Occurrences

Multiset difference removes matching occurrences while keeping any extra copies from the left input. I assign occurrence numbers before pairing rows. A value’s presence on the right should not automatically remove every left copy.

This requirement differs from a distinct set comparison. Three copies on the left and two on the right leave one copy. The result must preserve that remaining multiplicity instead of reducing the problem to unique values.

Rank occurrences within each value

The left input has three A values, one B and two NULL values. The right input has two A values, one C and one NULL. Each source row has a unique identifier for stable ordering.

ROW_NUMBER assigns an occurrence within each value partition. The first left A pairs with the first right A, and the second pairs with the second. The third left A has no corresponding occurrence.

EXCEPT compares the value and occurrence together. Each complete pair is unique within its input, so its distinct-row behavior does not collapse remaining copies. The final projection retains the occurrence for inspection.

WITH LeftInput AS
(
    SELECT RowId, ValueText
    FROM (VALUES (1, CAST('A' AS varchar(10))), (2, 'A'), (3, 'A'),
                 (4, 'B'), (5, NULL), (6, NULL)) AS v(RowId, ValueText)
), RightInput AS
(
    SELECT RowId, ValueText
    FROM (VALUES (11, CAST('A' AS varchar(10))), (12, 'A'),
                 (13, 'C'), (14, NULL)) AS v(RowId, ValueText)
), RankedLeft AS
(
    SELECT RowId, ValueText, ROW_NUMBER() OVER (PARTITION BY ValueText ORDER BY RowId) AS Occurrence
    FROM LeftInput
), RankedRight AS
(
    SELECT RowId, ValueText, ROW_NUMBER() OVER (PARTITION BY ValueText ORDER BY RowId) AS Occurrence
    FROM RightInput
)
SELECT ValueText, Occurrence
FROM
(
    SELECT ValueText, Occurrence FROM RankedLeft
    EXCEPT
    SELECT ValueText, Occurrence FROM RankedRight
) AS Difference
ORDER BY ValueText, Occurrence;

Read the three remaining rows

The expected result includes A with occurrence three. Only two A occurrences exist on the right. Exactly one A copy therefore remains after the comparison.

The B row remains because the right input has no B occurrence. The right-only C value does not create a result row. This operation subtracts from the left input rather than combining both populations.

One NULL copy remains as left row six with occurrence two. The first left NULL paired with the right NULL. The explicit missing-value rule prevents both left NULL copies from surviving accidentally.

The occurrence column makes the surviving multiplicity visible. The equality contract concerns ValueText alone after pairing numbered copies. Source identifiers provide a stable numbering order, rather than becoming part of the compared value.

Define the complete equality contract

For a multi-column value, partition and match on every component that defines equality. A single text column is intentionally narrow here. Ignoring another meaningful component could pair rows that are not equivalent.

Text equality uses the relevant collation. Case, accents and trailing spaces can therefore matter to the contract. This bounded sample avoids those differences, but a real reconciliation should test them explicitly.

The occurrence numbers need an ordering that identifies source rows consistently. This example uses unique row identifiers. If duplicates have additional meaningful attributes, include those in the value contract rather than pretending they are interchangeable.

EXCEPT treats two NULL values as equal when comparing these complete rows. That supplies the missing-value rule used here. Projecting the value afterward without DISTINCT keeps the remaining duplicate count.

Use subtraction without inventing a performance claim

The output multiplicity follows the difference between left and right counts, bounded below by zero. Excess right copies do not create negative rows. A distinct set operator answers a different question and cannot supply this duplicate-preserving result directly.

This query changes no data and creates no objects. It demonstrates a reconciliation rule using complete VALUES inputs. The example does not claim that occurrence pairing is the fastest approach for every workload.

Large reconciliations need appropriate keys, types and an execution-plan review. The logical requirement should remain explicit regardless of that implementation choice. Compare duplicate counts and missing values before considering an optimization.

I keep one unmatched value, one partially matched repeated value and a repeated NULL group in the example. Those cases expose different failure modes. A source containing only unique populated values would not establish this multiset contract.

Count the copies first, and the subtraction takes care of itself.

Multiset difference is not a distinct comparison, it is subtraction of matching copies.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.