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