UNION vs UNION ALL SQL

UNION vs UNION ALL

UNION and UNION ALL are two SQL commands that are used to combine the results of two or more queries. In this article, I will explain in detail the difference between the two in a more straightforward way.

The main difference when comparing the two is that UNION ALL returns all the rows from the two queries while UNION returns only distinct (without duplicates) rows from the two queries.

UNION :

The UNION operator is used to combine the result set of two or more SELECT statements. The number of columns in the result set of each SELECT statement must be equal. The columns must also have similar data types. The columns in each SELECT statement must also be in the same order.

UNION Syntax :

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

UNION Example :

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

UNION ALL :

The UNION ALL operator is used to combine the result set of two or more SELECT statements. The UNION ALL operator selects all rows from each SELECT statement, without removing duplicates, and then combines the result sets.

UNION ALL Syntax :

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

UNION ALL Example :

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

Summary :

Both of these statements are used to combine the result of two or more queries. UNION combines queries while removing duplicates on the other hand UNION ALL combines queries along with duplicates.