UNION ALL operator in SQL

The UNION ALL operator concatenates the results of two queries into a single result set. We control whether the result set includes duplicate rows:

UNION ALL : The UNION ALL operator includes duplicates.
UNION : The UNION operator removes duplicates.

SQL Syntax

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

Example: Let's create two tables.

SQL

CREATE TABLE EmployeesA (
   EmployeeID INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    Age int
);

Load the data into the EmployeesA table.

SQL

insert into EmployeesA values
(1, "Ashish", "Goel", 25), 
(2, "Kirshan", "Gupta", 30),
(3, "Anjali", "Sharma", 25),
(4, "Manju", "Saini", 15),
(5, "Katrina", "Kaif", 18);

By using the following query, we see the data from the EmployeesA table.

SQL

SELECT * FROM EmployeesA;

Table: EmployeesA

EmployeeID	FirstName	LastName	Age
1	Ashish	Goel	25
2	Kirshan	Gupta	30
3	Anjali	Sharma	25
4	Manju	Saini	15
5	Katrina	Kaif	18

Let’s create a EmployeesB table.

SQL

CREATE TABLE EmployeesB (
   EmployeeID INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    Age int
);

Load the data into the EmployeesB table.

SQL

insert into EmployeesB values
(3, "Anjali", "Sharma", 25),
(4, "Manju", "Saini", 15),
(5, "Katrina", "Kaif", 18),
(6, "Esha", "Dixit", 20),
(7, "Ankita", "Verma", 19), 
(8, "Meenakshi", "Chikkara", 35),
(9, "Alia", "Bhatt", 16);

By using the following query, we see the data from the EmployeesB table.

SQL

SELECT * FROM EmployeesB;

Table: EmployeesB

EmployeeID	FirstName	LastName	Age
3	Anjali	Sharma	25
4	Manju	Saini	15
5	Katrina	Kaif	18
6	Esha	Dixit	20
7	Ankita	Verma	19
8	Meenakshi	Chikkara	35
9	Alia	Bhatt	16

We can use the UNION ALL operator in SQL to concatenates the query into one set.

SQL

SELECT EmployeeID, FirstName, LastName, Age
FROM EmployeesA
UNION ALL
SELECT EmployeeID, FirstName, LastName, Age
FROM EmployeesB;

The result of the above query is shown below:

EmployeeID	FirstName	LastName	Age
1	Ashish	Goel	25
2	Kirshan	Gupta	30
3	Anjali	Sharma	25
4	Manju	Saini	15
5	Katrina	Kaif	18
3	Anjali	Sharma	25
4	Manju	Saini	15
5	Katrina	Kaif	18
6	Esha	Dixit	20
7	Ankita	Verma	19
8	Meenakshi	Chikkara	35
9	Alia	Bhatt	16

Previous Next