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. We challenge you to do this by reviewing the SQL Create queries and modifying them to create two brand new employee tables.

USE mydatabase;

SELECT * FROM employees
UNION
SELECT * FROM employees2;

SQL Table:

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.

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:

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.

SELECT * FROM employees
UNION ALL
SELECT * FROM employees2;

SQL Table:

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:

• Go Back
• Continue

Found Something Wrong in this Lesson?

Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!