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.