What is the difference between UNION and UNION ALL?

UNION returns only distinct rows from the combined result set, while UNION ALL returns all rows including duplicates. UNION ALL is generally faster because it skips the deduplication step.

When should I use UNION ALL instead of UNION?

Use UNION ALL when you want to preserve all rows from both queries, including duplicates. It is also preferred for performance when you know duplicates are acceptable or when you need to count total occurrences across multiple sources.

Do the columns in UNION ALL queries need to match?

Yes. Both SELECT statements in a UNION ALL must return the same number of columns, and corresponding columns must be of compatible data types.

Can UNION ALL combine more than two queries?

Yes. You can chain multiple UNION ALL operators to combine three or more SELECT statements into a single result set.

SQL UNION ALL

SQL > Advanced SQL > Union All

The purpose of the SQL UNION ALL command is to combine the results of two queries together without removing any duplicates.

Key Takeaway: UNION ALL merges result sets from two or more SELECT statements and keeps all rows, including duplicates. It is faster than UNION because it skips duplicate elimination. Both queries must select the same number of columns with compatible data types.

Syntax

The syntax for UNION ALL is as follows:

[SQL Statement 1]
UNION ALL
[SQL Statement 2];

The columns selected in [SQL Statement 1] and [SQL Statement 2] need to be of the same data type for UNION ALL to work.

Example

We use the following tables for our example.

Table Store_Information

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
Los Angeles	300	Jan-08-1999
Boston	700	Jan-08-1999

Table Internet_Sales

Txn_Date	Sales
Jan-07-1999	250
Jan-10-1999	535
Jan-11-1999	320
Jan-12-1999	750

To find out all the dates where there is a sales transaction at a store as well as all the dates where there is a sale over the internet, we use the following SQL statement:

SELECT Txn_Date FROM Store_Information
UNION ALL
SELECT Txn_Date FROM Internet_Sales;

Result:

Txn_Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-07-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

UNION vs UNION ALL

UNION and UNION ALL both combine the results of two SQL queries. The difference is that, while UNION only returns distinct values, UNION ALL selects all values. If we use UNION in the above example,

SELECT Txn_Date FROM Store_Information
UNION
SELECT Txn_Date FROM Internet_Sales;

the result becomes,

Txn_Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

Notice that while the UNION ALL query returns "Jan-07-1999" and "Jan-08-1999" twice, the UNION query returns each value only once.

Frequently Asked Questions

What is the difference between UNION and UNION ALL?: UNION returns only distinct rows, eliminating duplicates from the combined result. UNION ALL returns every row from all queries, including duplicates. UNION ALL is generally faster since it skips the deduplication step.
When should I use UNION ALL instead of UNION?: Use UNION ALL when duplicates are acceptable or intentional, or when you need better performance. UNION is preferable when you need a deduplicated list of values from multiple sources.
Do the columns in UNION ALL need to match?: Yes. Both SELECT statements must return the same number of columns, and corresponding columns must have compatible data types. Column names from the first SELECT are used as the output column names.
Can UNION ALL combine more than two queries?: Yes. You can chain multiple UNION ALL operators: Query1 UNION ALL Query2 UNION ALL Query3 to combine three or more result sets into one.

Next: SQL Inline View

This page was last updated on March 19, 2026.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.

SQL UNION ALL

Syntax

Example

UNION vs UNION ALL

Frequently Asked Questions

Related Topics

AdBlock Detected!