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 |