Finding Common Rows with INTERSECT

Sometimes, we only care about the rows both rowsets have in common. We use the INTERSECT operator to accomplish this. INTERSECT ALL preserves duplicates while INTERSECT removes duplicates.

INTERSECT ALL

@intersect_all =
    SELECT * FROM @a
    INTERSECT ALL
    SELECT * FROM @b;

@intersect_all

INTERSECT DISTINCT

@intersect_distinct =
    SELECT * FROM @a
    INTERSECT DISTINCT
    SELECT * FROM @b;

@intersect_distinct