SQL - Union

SQL UNION combines two separate SQL queries into one result set. A JOIN statement adds additional table columns to a result set (horizontally), UNION combines row results from one table with rows of another table (vertically).


In order to perform a UNION the columns of table 1 must match those of table 2. This rule ensures that the result set is consistent as rows are fetched by SQL.
For these next exercises we suggest creating two different tables that are identical in structure but contain unique rows of data.

SQL Select Union Code:

USE mydatabase;

SELECT * FROM employees
UNION
SELECT * FROM employees2;

SQL Table:

ID	Lastname	Firstname	Title
1	Johnson	David	crew
2	Hively	Jessica	crew
9	Hicks	Freddy	crew
10	Harris	Joel	crew
11	Davis	Julie	manager
101	Yazzow	Jim	crew
102	Anderson	Craig	crew
103	Carlson	Kevin	crew
104	Maines	Brad	crew

The result is a complete listing of every employee from the two tables, perhaps representing a list of employees from two different departments.
The next example shows a more practical means of using a union clause. Here we will select all of our employees from both tables and join them with our invoices table to generate a complete list of sales from both stores on a given day.

SQL Code:

SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees
INNER JOIN invoices
ON employees.id = invoices.EmployeeID
UNION
SELECT employees2.Lastname, employees2.Firstname, invoices.Sale, invoices.Price
FROM employees2
INNER JOIN invoices
ON employees2.id = invoices.EmployeeID;

SQL Table:

Lastname	Firstname	Sale	Price
Johnson	David	HOT DOG	1.99
Hively	Jessica	LG SFT DRK	1.49
Davis	Julie	CK SLD	3.99
Yazzow	Jim	HOT DOG	1.99
Carlson	Kevin	LG SFT DRK	1.49

Here we combined a join query with the union clause to create one table.

SQL - Union All

UNION ALL selects all rows from each table and combines them into a single table. The difference between UNION and UNION ALL is that UNION ALL will not eliminate duplicate rows. Instead, it just pulls all rows from all tables fitting your query specifics and combines them into a table.

SQL Code:

SELECT * FROM employees
UNION ALL
SELECT * FROM employees2;

SQL Table:

ID	Lastname	Firstname	Title
1	Johnson	David	crew
2	Hively	Jessica	crew
9	Hicks	Freddy	crew
10	Harris	Joel	crew
11	Davis	Julie	manager
101	Yazzow	Jim	crew
102	Anderson	Craig	crew
103	Carlson	Kevin	crew
11	Davis	Julie	manager
104	Maines	Brad	crew

SQL Code:

SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees
INNER JOIN invoices
ON employees.id = invoices.EmployeeID
UNION ALL
SELECT employees2.Lastname, employees2.Firstname, invoices.Sale, invoices.Price
FROM employees2
INNER JOIN invoices
ON employees2.id = invoices.EmployeeID;

SQL Table:

Lastname	Firstname	Sale	Price
Johnson	David	HOT DOG	1.99
Hively	Jessica	LG SFT DRK	1.49
Davis	Julie	CK SLD	3.99
11	Davis	Julie	manager
Yazzow	Jim	HOT DOG	1.99
Carlson	Kevin	LG SFT DRK	1.49
11	Davis	Julie	manager
11	Davis	Julie	manager

Posted in: SQL