SQL UNION ALL Operator

Posted on Tuesday, 07 March 2023 12:06 PM

UNION ALL operator is used to combine the results of two or more SELECT statements into a single result set. The difference between UNION and UNION ALL is that UNION removes duplicate rows from the result set, while UNION ALL does not.

UNION ALL Syntax

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

For example,

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

Students table:

id	name	email	phone	address
1	John Doe	john.doe@example.com	555-123-4567	123 Main St.
2	John	john@example.com	555-987-6543	456 Elm St.
3	Angel	angel@example.com	555-555-1212	789 Oak St.

Teachers table:

id	name	email	phone	address
1	Abdul	abdul@example.com	555-123-4567	234 Maple St.
2	Mary Brown	mary@example.com	555-987-6543	567 Cedar St.
3	Tom Smith	tom@example.com	555-555-1212	890 Pine St.

We can use the UNION ALL operator to combine the results of two SELECT statements:

SELECT name, email FROM students
UNION ALL
SELECT name, email FROM teachers;

OUTPUT

The result set would be:

name	email
John Doe	john.doe@example.com
John	john@example.com
Angel	angel@example.com
Abdul	abdul@example.com
Mary Brown	mary@example.com
Tom Smith	tom@example.com

As you can see,

The UNION ALL operator simply combines the result sets of the two SELECT statements without removing any duplicates. If we had used UNION instead of UNION ALL, the duplicate email address of "Jane Doe" would have been removed from the result set.

K-Learn