The Power of UNION

The UNION clause is used to join two SELECT statements to create a single output of data. Now why would you want to do that? If you are pulling data from a single table, you can always just adjust the WHERE clause to include both conditions. What about when you are pulling data from more than one table? The UNION clause can help.

My first use of the UNION clause came as I was creating a cross-tab report. I was pulling data from multiple database tables and was able to massage the data so that I could use it with the UNION to create one set of output that could then be displayed in a dynamic spreadsheet-like report. When I was done, I had a 650-line SQL query with over 100 UNION clauses. Here is a simple example of how it works:

Suppose you have two tables: favorite_sports and favorite_hobbies. Never mind that nobody would ever create tables this way, but it helps make the example clear. The favorite_sports table has the following contents:

 id | name |     sport    
----+------+---------------
  1 | Matt | Skiing
  2 | Nate | Rock Climbing
  3 | Dave | Skiing

And the favorite_hobbies table has the following contents:

 id | name |   hobby  
----+------+-----------
  1 | Matt | Ham Radio

The table definitions don't have to match exactly to use the UNION operator, they just need to be somewhat similar. In this example, I used a varchar(20) for the sport column and varchar(25) for the hobby column. That works fine, just don't try to match up an integer with a string.

A simple example of the UNION clause would be:

SELECT * FROM favorite_sports
UNION
SELECT * FROM favorite_hobbies;

And would give the following results:

 id | name |     sport    
----+------+---------------
  1 | Matt | Ham Radio
  2 | Nate | Rock Climbing
  3 | Dave | Skiing
  1 | Matt | Skiing

Notice how we get all of the rows out of both tables. Unfortunately the column heading for the sport or hobby is "sport". We can fix that by using an alias as in the following query:

SELECT id, name, sport AS "sport/hobby" 
  FROM favorite_sports
UNION
SELECT * 
  FROM favorite_hobbies;

And gives us the following results:

 id | name |  sport/hobby 
----+------+---------------
  1 | Matt | Ham Radio
  2 | Nate | Rock Climbing
  3 | Dave | Skiing
  1 | Matt | Skiing

Notice the use of double quotes for the alias name. We generally use single quotes in SQL but that will generate a syntax error here. Now if we want to add a type column, it is as simple as running the following query:

SELECT id, name, sport AS "sport/hobby", 'sport' AS type
  FROM favorite_sports
UNION
SELECT *, 'hobby'
  FROM favorite_hobbies;

Which gives us the following results:

 id | name |  sport/hobby  | type 
----+------+---------------+-------
  1 | Matt | Ham Radio     | hobby
  1 | Matt | Skiing        | sport
  2 | Nate | Rock Climbing | sport
  3 | Dave | Skiing        | sport

Hopefully this gives you a concrete example of the UNION clause and helps you understand when to use it.