UNION vs UNION ALL
Description	UNION	UNION ALL
Duplication Handling	The UNION removes duplicate rows from the result set, presenting only unique records.	The UNION ALL remains all rows, including duplicates, without any elimination.
Performance Impact	The UNION involves the additional step of identifying and eliminating duplicates, potentially impacting performance.	The UNION ALL generally performs faster, as it does not have the overhead of duplicate removal.
Result Set Structure	The UNION creates a distinct result set with unique records.	The UNION ALL produces a result set that includes all rows from the combined queries.
Syntax	The syntax for both UNION and UNION ALL is similar, with the key difference lying in the choice of the operator.	The syntax for both UNION and UNION ALL is similar, with the key difference lying in the choice of the operator.
Use Case	The UNION is suitable when the emphasis is on unique records and the removal of duplicates is essential.	The UNION ALL is preferred when retaining all rows, including duplicates, is acceptable or even desired.
Data Volume	For large datasets, UNION may not be efficient due to its complex processing with duplicate checks.	For large datasets, UNION ALL may be more efficient due to its simplified processing without duplicate checks.
Query Optimization	The UNION may involve additional processing to identify and eliminate duplicates, potentially leading to a longer execution time.	The UNION ALL, being less restrictive, may result in quicker query execution.
Resource Utilization	The UNION consumes additional resources to identify and remove duplicate rows, impacting memory usage.	The UNION ALL is generally more resource-efficient as it bypasses the duplicate elimination step.
Compatibility	Both UNION and UNION ALL are widely supported across various relational database management systems (RDBMS).	Both UNION and UNION ALL are widely supported across various relational database management systems (RDBMS).
Consideration for Result Accuracy	If eliminating duplicates is crucial for the accuracy of the result set, then UNION is the appropriate choice.	When duplicate records are acceptable or necessary, and performance is a priority, UNION ALL is the preferred option.

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).