SQL Server - UNION, UNION ALL, INTERSECT

Remember Set Theory?

Part of the foundation of database theory is founded on set theory. If you are in your fifties or older you will remember set theory as the "new math." There is quite an irony in the moniker "new math" as set theory was written formally written about back in the 1870's.

It is also ironic that one needs to seldom write a union in SQL. In my twenty years of working in SQL, apart from teaching I have had to write a union once. For whatever reason giving problems in job interviews that require you to write a union seems to be quite popular. Yet I digress...

A Simple Example

Assume the following tables:

UNIONTABLE1

uniontable1

UNIONTABLE2

uniontable2

These are seperate SQL tables that just so happen to have the same schema. More on the requirements for a union in SQL Server later. For now lets look at the two tables in terms of set theory diagram.

set

You can see the overlapping data (for the language column). And, you can see where this column has unique data for each table.

UNION

If we want to combine the two tables and produce a recordset of all the unique rows found in each table we can create a UNION query.

SELECT language
 ,type
 FROM Play.dbo.UNIONTABLE1
 UNION
 SELECT language
 ,type
 FROM Play.dbo.UNIONTABLE2 


The following are the results of the above union.

union

SQL Server Requirements for a UNION

UNION ALL

We can return all the rows from both side in our union by adding the "ALL" keyword. In the query below I have added an "Order By" so you can more easily see the duplicates.

SELECT language
 ,type
 FROM Play.dbo.UNIONTABLE1
 UNION ALL
 SELECT language
 ,type
 FROM Play.dbo.UNIONTABLE2
 ORDER BY language

The following are the results of the above union.

unionall

INTERSECT

If all we want to see are the duplicates between the two tables we can perform an INTERSECT instead of a UNION.

SELECT language
 ,type
 FROM Play.dbo.UNIONTABLE1
 INTERSECT
 SELECT language
 ,type
 FROM Play.dbo.UNIONTABLE2

The following are the results of the above intersect.

intersect

 

 

 

 

 

 

 

 

 

 
 Collapse All    Expand All
Custom Search

SQL Server Information and Resources