UNION vs UNION ALL in SQL

SQL UNION and UNION ALL operators are used to concatenate results of multiple SELECT statements. However, they are different from each other. One key difference between UNION and UNION ALL in SQL is that the UNION command removes duplicates from the final results set, whereas the UNION ALL command allows duplicates in the results set.

Here we will explore the difference between UNION and UNION ALL in SQL.

Difference Between UNION and UNION ALL

UNION and UNION ALL, both commands are used to combine the result of two or more SELECT Statements, but there are many difference in UNION and UNION ALL.

SQL UNION Operator

SQL UNION Operator is used to combine the set of one or more SELECT statements as the resulting. The UNION operator removes duplicates from the combined result from the set of SELECT statements.

The conditions for a UNION statement are that the columns in the SELECT statement need to be in the same order, and the data types should be compatible. It is important to match the number of columns and their data types across different SELECT statements.

Syntax

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

Explanation: In the Above query, It combines the results of two SELECT statements using the UNION operator. It retrieves data from columns "column1," "column2," and "column3" from both "table1" and "table2," eliminating duplicate rows from the result set.

UNION ALL Operator

SQL UNION ALL operator is also used to combine the set of one or more select statements as the result. The difference between UNION and UNION ALL is that in the UNION ALL operator there are duplicates in the result sets of SELECT statements whereas in the UNION operator, there are no duplicate values. The UNION ALL is faster than the UNION statement because in UNION ALL there is no additional step of eliminating duplicates.

Syntax

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

Explanation: In the Above query, It combines the results of two SELECT statements using the UNION ALL operator. It retrieves columns "column1," "column2," and "column3" from "table1" and appends the same columns from "table2" to the result set.

UNION and UNION ALL Operator Examples

Let's look at some examples of the UNION and UNION ALL commands in SQL, and understand how they differ from each other.

We will be using the customer table, shown below:

SQL UNION Example

In this example, we will retrieve and combine rows with CustomerID between 1 and 5 from the "customer" table using the UNION operator.

Query:

SELECT ∗ FROM customer
WHERE CustomerID BETWEEN 1 AND 5
UNION
SELECT ∗ FROM customer
WHERE CustomerID BETWEEN 1 AND 5;

Output:

Explanation:

• The first SELECT statement retrieves all columns (*) from the customer table where the CustomerID is between 1 and 5.
• As the first statement is identical to the second one, so it will retrieve all columns from the customer table where the CustomerID is between 1 and 5.
• The UNION operator is used to combine the result sets of the two SELECT statements. UNION includes all rows, excluding duplicates, from both result sets.
• The output will be a combined result set of rows from the customer table where CustomerID is between 1 and 5, but it will not contain the duplicate rows from the two SELECT statement.
  

SQL UNION ALL Example

In this example, we will retrieves rows with CustomerID between 1 and 5 from the "customer" table and combines them with duplicate rows using the UNION ALL operator

Query:

SELECT ∗ FROM customer
WHERE CustomerID between 1 AND 5
UNION ALL
SELECT ∗ FROM customer
WHERE CustomerID BETWEEN 1 AND 5;

Output:

Explanation:

• The first SELECT statement retrieves all columns (*) from the customer table where the CustomerID is between 1 and 5.
• As the first statement is identical to the second one, so it will retrieve all columns from the customer table where the CustomerID is between 1 and 5.
• The UNION ALL operator is used to combine the result sets of the two SELECT statements. UNION ALL includes all rows, including duplicates, from both result sets.
• The output will be a combined result set of rows from the customer table where CustomerID is between 1 and 5, with potential duplicates if any rows satisfy the conditions in both SELECT statements.

Conclusion

This article covered the differences between UNION and UNION ALL Operator along with their examples. The UNION removes duplicate rows from the result set while the UNION ALL remains all rows, including duplicates, without any elimination. Also Both UNION and UNION ALL are widely supported across various relational database management systems (RDBMS).