Program With Dave

Sometimes it can be confusing what the difference between ‘union’ in a SQL statement and ‘union all’ means. The basic definition is that ‘union’ will combine a given set (query results) with the values of a second set that don’t already exist. ‘Union All’ will simply append the second set to the end of the first set, which includes all the records of the first set and all the records of the second set.

Let’s see an example.

1
2
3
4
5

SELECT 1 MyValue
 
UNION
 
SELECT 1 MyValue

Here we are combining two sets of data which each contain a single record, 1. If you execute this command, you will get back a single row which contains the value 1 in the MyValue column. This is because the new value we union in already existed in the first data set, so it did not include it.

Let’s modify our query to use Union All.

1
2
3
4
5

SELECT 1 MyValue
 
UNION ALL
 
SELECT 1 MyValue

We now get back a set with two records, the 1 from the first set AND the 1 from the second set.

This entry was posted on Thursday, July 29th, 2010 at 12:00 am and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.