SQL Union Operator
In this lesson, you will learn how to combine the results of two or more select statements in SQL.
The UNION Operator In SQL
The UNION operator can be used between SELECT statements to combine their results as follows.
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
By default, UNION removes duplicate rows.
However, if you want to retrieve all rows use UNION ALL as follows.
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
The selected columns from each table should have the same data type.
Preparing A Database
Since you will learn how to combine selected rows from multiple database tables, you must have at least 2 tables with some records to practice combining their rows. Execute the following query to create the database.
Example
-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;
-- Create a new database called "demo"
CREATE DATABASE demo;
-- Select the "demo" database
USE demo;
-- Create a new table called "employees" inside the selected database
CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(7,2)
);
-- Create a new table called "customers" inside the selected database
CREATE TABLE customers (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
);
-- Insert 5 records in the "employees" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO employees VALUES (null, "Emily", "Johnson", 800);
INSERT INTO employees VALUES (null, "Benjamin", "Anderson", 750);
INSERT INTO employees VALUES (null, "Olivia", "Smith", 900);
INSERT INTO employees VALUES (null, "William", "Davis", 845);
INSERT INTO employees VALUES (null, "Sophia", "Brown", 1000);
-- Insert 5 records in the "customers" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO customers VALUES (null, "Emily", "Johnson", "emily@example.com");
INSERT INTO customers VALUES (null, "Benjamin", "Anderson", "benjamin@example.com");
INSERT INTO customers VALUES (null, "James", "Wilson", "james@example.com");
INSERT INTO customers VALUES (null, "Mia", "Garcia", "mia@example.com");
INSERT INTO customers VALUES (null, "Ethan", "Jones", "ethan@example.com");
These are the tables that we created in the demo database.
Table: Employees
| id | first_name | last_name | salary |
|---|---|---|---|
| 1 | Emily | Johnson | 800.00 |
| 2 | Benjamin | Anderson | 750.00 |
| 3 | Olivia | Smith | 900.00 |
| 4 | William | Davis | 845.00 |
| 5 | Sophia | Brown | 1000.00 |
Table: Customers
| id | first_name | last_name | |
|---|---|---|---|
| 1 | Emily | Johnson | ethan@example.com |
| 2 | Benjamin | Anderson | ava@example.com |
| 3 | James | Wilson | james@example.com |
| 4 | Mia | Garcia | ethan@example.com |
| 5 | Ethan | Jones | ethan@example.com |
We make the first 2 rows in both tables have the same id, first name and last name on purpose to make you later understand the difference between the UNION and UNION ALL operators.
Union Example
The following query retrieves distinct records from tables employees and customers combined.
Example
SELECT id, first_name, last_name from employees
UNION
SELECT id, first_name, last_name from customers;
Output
| id | first_name | last_name |
|---|---|---|
| 1 | Emily | Johnson |
| 2 | Benjamin | Anderson |
| 3 | Olivia | Smith |
| 4 | William | Davis |
| 5 | Sophia | Brown |
| 3 | James | Wilson |
| 4 | Mia | Garcia |
| 5 | Ethan | Jones |
8 records of 10 are returned only because the result is distinct.
Union All Example
The following query retrieves all records from tables employees and customers combined.
Example
SELECT id, first_name, last_name from employees
UNION ALL
SELECT id, first_name, last_name from customers;
Output
| id | first_name | last_name |
|---|---|---|
| 1 | Emily | Johnson |
| 2 | Benjamin | Anderson |
| 3 | Olivia | Smith |
| 4 | William | Davis |
| 5 | Sophia | Brown |
| 1 | Emily | Johnson |
| 2 | Benjamin | Anderson |
| 3 | James | Wilson |
| 4 | Mia | Garcia |
| 5 | Ethan | Jones |
All rows inside employees and customers tables are returned.
Combine Results Exercise
1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.
2- Write a query that creates a table called "customers" and check whether it exists or not. the table should contain the following fields:
id INT NOT NULL PRIMARY KEY AUTO_INCREMENTfirst_name VARCHAR(50)last_name VARCHAR(50)phone VARCHAR(20)
3- Write a query that creates an identical table to the "customers" table called "suppliers".
4- Write a query that inserts 5 rows in the "customers" table.
5- Write a query that inserts 3 rows in the "suppliers" table.
6- Write a query that returns all records inside the "customers" and "suppliers" tables ordered by their ids.
Note: The result should not contain repeated rows.
Solution
DROP DATABASE IF EXISTS training;
CREATE DATABASE training;
USE training;
CREATE TABLE IF NOT EXISTS customers (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
phone VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS suppliers (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
phone VARCHAR(20)
);
INSERT INTO customers (first_name, last_name, phone) VALUES
('Jack', 'Reed', '10203040'),
('Emma', 'Clark', '55512388'),
('Luke', 'Lee', '32452012'),
('Jim', 'Wilson', '96112377'),
('Lily', 'Brooks', '36018090');
INSERT INTO suppliers (first_name, last_name, phone) VALUES
('Patrik', 'Jane', '94824673'),
('Michael', 'Johnson', '99345665'),
('Sophie', 'Wagner', '88024041');
SELECT * FROM customers
UNION
SELECT * FROM suppliers
ORDER BY id;
