Multiset Intersection: Keep the Matching Duplicate Occurrences

Multiset intersection keeps the duplicate count shared by both inputs rather than returning each common value once. I pair numbered occurrences to express that requirement. The result should contain the smaller count for each equivalent value.

If one input has three copies of A and the other has two, two copies belong in the intersection. Ordinary distinct intersection returns A once. Adding an occurrence number changes the full row being compared.

Label each copy before comparing

The left input contains three A values, one B and two NULL values. The right contains two A values, one C and one NULL. Unique source identifiers provide a stable order within each value partition.

ROW_NUMBER assigns occurrence one, two and so on for each value. INTERSECT then compares ValueText together with Occurrence. Each complete pair appears at most once within its input.

The output retains the occurrence number to make the shared copies visible. A later projection can remove that diagnostic column without adding DISTINCT. Removing it must not collapse the repeated values that the requirement deliberately preserves.

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
    INTERSECT
    SELECT ValueText, Occurrence FROM RankedRight
) AS Shared
ORDER BY ValueText, Occurrence;

Read the shared copies

The expected result contains NULL with occurrence one, followed by A with occurrences one and two. There are three rows in total. The third left A has no corresponding right occurrence.

B is absent because there is no right B value. C is absent because there is no left C value. Neither unmatched value contributes a row to the shared population.

Two left NULL values and one right NULL value produce one shared NULL copy. INTERSECT considers two NULL values equal for its distinct-row comparison. The occurrence number then controls how many such copies survive.

This result answers a count-sensitive comparison question. It does not identify one unique business record for every value. The numbered copies are a device for preserving multiplicity across otherwise equivalent inputs.

Keep equality and identity separate

The compared value in this query is ValueText. Source identifiers establish numbering order but are not included in the set comparison. Including different source identifiers would prevent otherwise equivalent copies from matching.

For a multi-column value, every equality component must participate in partitioning and comparison. Do not ignore an attribute that changes the business meaning. Numbering incomplete values can pair records that are not actually equivalent.

Text comparison also follows collation rules. This bounded example uses identical uppercase values without accent or space variations. A real comparison should define those rules and test representative variations explicitly.

A unique source ordering key makes the displayed occurrences stable. If additional source attributes distinguish duplicates, reconsider whether they belong in the equality contract. Calling different records interchangeable is a modeling choice, not a property supplied by ROW_NUMBER.

Preserve the result after the comparison

INTERSECT still returns distinct complete rows in this query. Those rows remain distinct because their occurrence numbers differ. Projecting only ValueText afterward can preserve multiple rows with the same displayed value.

Adding DISTINCT to that final projection would change the contract back to one common value per group. Keep the full result cardinality visible during review. A familiar-looking list of values can hide a lost copy.

The query is read-only and has no object creation or session settings. It demonstrates logical multiplicity rather than an execution-speed improvement. Larger comparisons require appropriate keys, types and a separate plan review.

I include unequal counts and NULL copies because a unique-value sample would make both intersection rules look equivalent. Retain those cases when adapting the pattern. The result should reflect shared occurrence counts, not merely familiar labels.

Count the copies first, and the shared count falls out.

Multiset intersection is not a list of common values, it is the shared count of each.

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