Overview

There comes a point in your SQL career where you will need to combine the results of several queries into one data set, and present it in one view.  When you get to this point, you’ll need to be familiar with the UNION command.  With Microsoft SQL 2005, there are two new functions, similar to UNION that you should also be familiar with.  EXCEPT and INTERSECT allow you to do two common data operations, without building the logic yourself.  I’ll cover these three commands in this post.

I have a script that can build the tables I reference on this page here.

UNION

Consider the data in the following two queries.

SELECT productName
FROM products

SELECT productName
FROM productSale

productName
-------------
Mountain Dew
paper
pen
pencil

productName
--------------
Mountain Dew
Mountain Dew
Mountain Dew
Mountain Dew
Mountain Dew
Mountain Dew
paper
pencil
pencil

Most of the time you’ll get a request that basically asks you to combine these two outputs into one list.  The easiest way to do that is use UNION ALL.

SELECT
productName
FROM products
UNION ALL
SELECT productName
FROM productSale

Mountain Dew
paper
pen
pencil
Mountain Dew
Mountain Dew
Mountain Dew
Mountain Dew
Mountain Dew
Mountain Dew
paper
pencil
pencil

As soon as you combine these two outputs, you should notice there are duplicates.  Most of the time what you actually wanted to see here is a UNION.  UNION will actually remove the duplicates from the output for you.  This actually will return results much quicker than trying to apply DISTINCT to your results.

SELECT
   productName
FROM products
UNION
SELECT
   productName
FROM productSale

productName
-------------
Mountain Dew
paper
pen
pencil

There one important point to keep in mind while using UNION or UNION ALL. The number, order, and datatypes of the columns must be the same for every query you want to combine.  If you change any one of these, you will get an error from SQL informing you that this is true.

Combining queries’ results can be a useful feat when designing reports.  Definitely keep this trick in mind when you are faced with combining multiple data sets into one.

EXCEPT

The EXCEPT command actually builds on the concept of UNION.  If you have two result sets, and you need to see the results from the first set, where that record doesn’t exist in the second set, EXCEPT can be your friend.  EXCEPT can make it easier to do this than using a LEFT JOIN, and testing for no matches.

The same important point from UNION still applies.  You have to be dealing with two or more compatible record sets.

Let’s see what ProductNames exist in products, that doesn’t exist in productSale.

SELECT productName
FROM products
EXCEPT
SELECT productName
FROM productSale

productName
-------------
pen

INTERSECT

The INTERSECT command is the opposite of an EXCEPT.  It will only return the records that are in both record sets.

SELECT
   productName
FROM products
INTERSECT
SELECT
   productName
FROM productSale

productName
-------------
Mountain Dew
paper
pencil

Conclusion

Whenever you have to deal with multple record sets and need to combine them into one, keep these three commands in mind.  UNION is available in SQL 2000 and later, the EXCEPT and INTERSECT command is available in SQL 2005 and later.  All three can give you results that would take more work if you don’t use them.  I hope these examples have helped you understand them better, if not, let me know!

Previous: JOIN

Next: INSERT