INTERSECT with duplicate rows in one table

Question

Accepted Answer

INTERSECT returns only rows that appear in both result sets and then removes duplicates from the output. X appears in both tables, so it qualifies. Even though group_a has two rows with X, the deduplication means the result contains exactly one row: X. To preserve all matching copies, use INTERSECT ALL - that would return two rows of X because group_a contributes two copies and group_b contributes one (the minimum of the two counts).