What happens if tables we perform UNION on have duplicate rows?


#1

Question

What happens if the tables we perform the UNION operator on have duplicate rows?

Answer

When you combine tables with UNION, duplicate rows will be excluded.

To explain why this is the case, recall a Venn Diagram, which shows the relations between sets. If we perform UNION on two sets of data (tables), say A and B, then the data returned in the result will essentially be
A + B - (A intersect B)

In the first part,
A + B
will add together all the rows of both tables, including duplicates.

The second part,
- (A intersect B)
will remove every duplicate, which is where A and B intersected.

If, however, you wanted to include duplicates, certain versions of SQL provides the UNION ALL operator.