SQL - Union

The union clause places two serarate queries together forming one table. A union works best when using two tables with similar columns because each cloumn must have the same data type.

Say we had another table called employees2 with the names and information of employees from our second store. With 2 queries, we can combine the tables into a list of all employees.

SELECT * FROM employees
UNION
SELECT * FROM employees2;

SQL Table:

The result is a complete listing every employee from store 1 and 2.

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