Database basic part 11: UNION

1. What is UNION

In SQL, the UNION operator is used to combine the results of two or more SELECT queries into a single result set. The UNION operator returns only distinct values by default, meaning it removes duplicate rows from the result set.

Basic Syntax

SELECT column1,
       column2, ...
FROM table1
UNION
SELECT column1,
       column2, ...
FROM table2;

There are some other operator similar UNION like UNION ALL, INTERSECT, EXCEPT, I will mention them in the tutorial video.

2. Example

SET SQL_SAFE_UPDATES = 0;

USE JadeDb;


DROP TABLE IF EXISTS Employees;


CREATE TABLE IF NOT EXISTS Employees (id INT PRIMARY KEY AUTO_INCREMENT,
                                                         full_name VARCHAR(30),
                                                                   department_id INT);


DROP TABLE IF EXISTS Departments;


CREATE TABLE IF NOT EXISTS Departments (id INT PRIMARY KEY AUTO_INCREMENT,
                                                           department_name VARCHAR(20));


INSERT INTO Departments (department_name)
VALUES ('Marketing'),
       ('Sale'),
       ('HR'),
       ('Dev'),
       ('X');


INSERT INTO Employees (full_name, department_id)
VALUES ('Jade', 4),
       ('John', 1),
       ('Keyli', 2),
       ('Hana', NULL),
       ('Hine', 3),
       ('Xeya', NULL);
SELECT full_name,
       department_name
FROM Employees
JOIN Departments ON Employees.department_id = Departments.id
WHERE full_name = 'John'
UNION
SELECT full_name,
       department_name
FROM Employees
JOIN Departments ON Employees.department_id = Departments.id
WHERE full_name = 'Jade'
SELECT full_name,
       department_name
FROM Employees
JOIN Departments ON Employees.department_id = Departments.id
UNION ALL
SELECT full_name,
       department_name
FROM Employees
JOIN Departments ON Employees.department_id = Departments.id

3. Video tutorial