UNION ALL

The SQL UNION ALL operator combines the result sets of two or more SELECT statements into a single result set. It is similar to the UNION operator, but it does not remove duplicate rows from the result set.

Syntax

The syntax for using UNION ALL is similar to UNION, but with the addition of the ALL keyword:

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

Example

Suppose we have two tables, students and teachers, with the following data:

Table students:

id name age
1 Alice 20
2 Bob 22
3 Carol 21
4 David 20

Table teachers:

id name subject
1 Mr. Lee Math
2 Ms. Smith Science
3 Mr. Kim English
create table students(id int, name varchar(250), age int);
insert into students(id, name, age) values
(1,'Alice',20), (2,'Bob',22), (3,'Carol',21), (4,'David',20);

create table teachers(id int, name varchar(250), subject varchar(250));
insert into teachers(id, name, subject) values
(1,'Mr. Lee','Math'), (2,'Ms. Smith','Science'), (3,'Mr. Kim','English');

We can use the UNION ALL operator to combine the results of two SELECT statements that select data from these two tables:

SELECT id, name, age, NULL AS subject
FROM students
UNION ALL
SELECT id, name, NULL AS age, subject
FROM teachers;

This query selects all the columns from the students table and the id, name, and subject columns from the teachers table. Since the age column does not exist in the teachers table, we use the NULL keyword to fill in the missing values. The result of this query would be:

id name age subject
1 Alice 20 NULL
2 Bob 22 NULL
3 Carol 21 NULL
4 David 20 NULL
1 Mr. Lee NULL Math
2 Ms. Smith NULL Science
3 Mr. Kim NULL English

Note that the result set includes all the rows from both tables, and there are no duplicate rows since we used the UNION ALL operator.