SQL UNION and UNION ALL

The SQL Union command can be used to combine the output from 2 or more SELECT statements into 1 resultset. This article explains the syntax and rules of the UNION command and gives real world examples of it in use.

Syntax

SELECT [ColumnName, ...]
UNION
SELECT [ColumnName, ...]

UNION rules for success

The syntax for the UNION command is very simple but there are some rules that must be followed for the command to work correctly.

• The SELECT statements must select the same number of columns
• The columns selected in the different SELECT statements must be in the same order
• The corresponding columns from each SELECT statement must either be of the same data type or be able to be converted (either implicitly or explicitly using a SQL function such as CAST).

Real world example

In the following example for an e-commerce website that has a shopping cart and message forum on it we want to generate a resultset of users who we can send the sites monthly email newsletter to. To do this we want to combine the user information from the Customer table with user information from the ForumUser table:

SELECT FirstName, LastName, Email, DateLastOrder
FROM Customer
WHERE ReceiveNewsletter = 1
UNION
SELECT FirstName, FamilyName, EmailAddress, DateLastPost
FROM ForumUser 
WHERE SendNewsletter = 1

The output of this query would look similar to the following:

FirstNameLastNameEmailDateLastOrder

Jimi	Hendrix	jimi.hendrix@guitar.com	10/10/2007
Bob	Marley	bob.marley@reggae.com	05/01/2007
Fred	Bloggs	fred@bloggs.com	09/10/2007

We can see that the single resultset that is made from the UNION command always takes its column headings from the first SELECT statement in the UNION.

UNION ALL

It is also important to identify that the UNION removes any duplicate (non distinct) rows from the resultset. This means that in the example above if a user was in both the Customer table and the ForumUser table and the corresponding Email addresses were the same and the LastOrderDate and LastPostDate were the same then only one or these 2 rows would be included in the resultset. By chance it happens that for the above example this is a beneficial aspect of the UNION command as we would not want to send the newsletter to the same email addresss twice. There may be times however when we want to include all the duplicate rows and to do this we simply modify the UNION command to include the ALL keyword.

SELECT Column1, Column2 FROM Table1
UNION ALL
SELECT Column1, Column2 FROM Table2
UNION ALL 
SELECT Column1, Column2 FROM Table3