UNION and UNION ALL are SQL operators used to combine the results of two or more SELECT statements.

• UNION: Removes duplicate records from the result set, ensuring each row is unique.
• UNION ALL: Includes all duplicates, showing all results from both queries.

The basic syntax of UNION:

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

Combines the result sets of two SELECT statements and removes duplicate records.

The basic syntax of UNION ALL:

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

Combines the result sets of two SELECT statements and includes all duplicates.

Example:

There are two tables:

UNION:

UNION ALL:

We observe that in UNION ALL, “Bob” is not duplicated in output even though it is present in both tables. However, using UNION, all the records are seen in the table.

Special thanks to Kuhuk Agarwal and Gauri Tomar for contributing to this article on takeUforward. If you also wish to share your knowledge with the takeUforward fam, please check out this article.