UNION vs UNION ALL in SQL – UNION and UNION ALL are used to combine results from multiple retrieve statements. UNION returns only unique rows keeping duplicates, whereas UNION ALL includes all rows, keeping duplicates.

UNION vs UNION ALL in SQL:

The key major difference is that UNION removes duplicate records, whereas UNION ALL includes all duplicates. This distinction not only changes the number of rows in the query result, but it also impacts performance.

UNION vs UNION ALL in SQL Example:

Let’s consider two sample tables, cutomers_new and cutomers_old , representing cutomers records for two types.

SELECT *
FROM cutomers_new;

id	name	city
1	Jack	City1
2	Rose	City2
3	Jim	City3

SELECT *
FROM cutomers_old;

id	name	city
1	Jack	City1
2	Rose	City2
4	Roe	City4

Now, let’s join our two tables using the UNION function.

SELECT id, name, city FROM cutomers_new

UNION

SELECT id, name, city FROM cutomers_old;

Result:

id	name	city
1	Jack	City1
2	Rose	City2
3	Jim	City3
4	Roe	City4

In this result, duplicate records are removed. Now let’s compare this with the result we obtain after using UNION ALL.

SELECT id, name, city FROM cutomers_new

UNION ALL

SELECT id, name, city FROM cutomers_old;

Result:

name

city

Jack

Rose

Jim

Jack

Rose

Roe

City1

City2

City3

City1

City2

City4

Top 10 SQL Query Optimization Techniques

What is UNION in SQL?UNION in SQL combines data from two SELECT queries’ results into a single distinct result set. The result set to produce from UNION doesn’t contain any duplicate value.

UNION Syntax in SQL:

SELECT column_names
FROM table_1
WHERE conditions

UNION

SELECT column_names
FROM table_2
WHERE conditions;

What Are the Use Cases for UNION in SQL?

Here are some common use cases for the UNION function.

Combining Results From Different Tables: When you want to combine data from multiple tables and ensure no duplicate records, UNION is the go-to function.
Handling Different Data Sources: UNION is useful when combining tables from different data sources.
Removing Duplicates Across Queries: When you want to ensure the uniqueness of the combined result set, use UNION.

What is UNION ALL?

UNION ALL is an extension of UNION in SQL. As the result set of UNION of two tables doesn’t contain all the records from both tables, UNION ALL comes into the picture and returns all the records from both tables (including duplicate rows also).

SQL Order of Execution – Strong Understanding

UNION ALL Syntax in SQL:

SELECT column_names
FROM table_1
WHERE conditions

UNION ALL

SELECT column_names
FROM table_2
WHERE conditions;

What Are the Use Cases for UNION ALL in SQL?

Here are some common use cases for the UNION ALL function.

Combining Results With Duplicates: Use UNION ALL when you need to combine results from multiple queries and preserve all duplicate rows.
Performance Considerations: UNION ALL is more time-efficient than the UNION function, because it doesn’t require the additional step of removing duplicates.
Aggregating Data From Different Periods: When aggregating data from different periods or sources, and you need to preserve the duplicate entries, UNION ALL is preferred.
Reporting and Analysis: For reporting purposes where every record, including duplicates, is necessary, UNION ALL is suitable.

Difference Between UNION and UNION ALL (UNION vs UNION ALL):

Here is a detailed comparison of UNION and UNION ALL based on various features:

UNION	UNION ALL
Removes duplicate rows, returning only unique records.	Includes all rows, even duplicates, from both result sets.
Slower due to the need to eliminate duplicates.	Faster, as it doesn’t check for duplicates and simply combines the rows.
The result set is smaller as duplicates are removed.	The result set is larger because duplicates are included.
UNION removes duplicates internally (via sorting or hashing), but the final result order is not guaranteed unless ORDER BY is specified.	No sorting is done unless explicitly mentioned using ORDER BY
Use when you need distinct results in the final output.	Use when retaining all rows, including duplicates, is important.
Requires more processing power because of the duplicate elimination process.	More efficient as no additional computation is needed to remove duplicates.
NULLs are treated as duplicates if they appear in both result sets.	NULLs are treated as regular values and included in the result, even if repeated.
Works well for queries where distinct data is crucial, like in reporting.	Suitable for queries where duplicates are not problematic, such as transaction logs or detailed data analysis.
Can be used in cases where you want to enforce uniqueness across different datasets.	Useful for combining large datasets where duplicate entries represent meaningful records, like in data aggregation.
SQL engine performs a distinct operation internally to remove duplicates.	No distinct operation is performed; all rows, including exact duplicates, are included.
Suitable when result accuracy is more important than performance.	Preferred when performance is the priority and duplicates don’t interfere with analysis or data quality.

Conclusion

UNION and UNION ALL in SQL are used to retrieve data from two or more tables. UNION returns distinct records from both tables, while UNION ALL returns all the records from both tables.