What happens if the tables we perform the
UNION operator on have duplicate rows?
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
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
If, however, you wanted to include duplicates, certain versions of SQL provides the
UNION ALL operator.