Difference between UNION and UNION ALL in SQL Server

The UNION command in SQL Server selects only distinct values from two tables eliminating any duplicate rows.
The UNION ALL command selects all values from two tables.

When using the UNION commands, all selected columns need to be of the same data type.

UNION:
We will join CurrencyCode values from Sales.CountryRegionCurrency table and Sales.Currency table in AdventureWorks database using UNION command:

USE AdventureWorks
SELECT CurrencyCode
FROM [Sales].[CountryRegionCurrency]
UNION
SELECT CurrencyCode
FROM [Sales].[Currency]
ORDER BY CurrencyCode

sql_server_union

As a result, we get 105 rows with only distinct values.

UNION ALL:
We will join CurrencyCode values from Sales.CountryRegionCurrency table and Sales.Currency table in AdventureWorks database using UNION ALL command:

USE AdventureWorks
SELECT CurrencyCode
FROM [Sales].[CountryRegionCurrency]
UNION ALL
SELECT CurrencyCode
FROM [Sales].[Currency]
ORDER BY CurrencyCode

sql_server_union_all

As a result, we get 214 rows including duplicate values.

UNION ALL does not perform DISTINCT operation in the result set, so it is faster, but it does not remove duplicate results from the result set.

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">