Union vs Union All in sql server

In previous article, I mentioned Understanding SQL server COALESCE() with example (ISNULL comparison included) but in this article, I have mentioned Union vs Union All in sql server with an example.

UNION and UNION ALL are SQL command just like JOIN, which is used to concatenate 2 or more result sets, but when using UNION command, all the selected columns need to be of the same data type.

The basic difference between UNION and UNION ALL is

• UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
• While UNION ALL does not remove duplicates, and therefore query execution time is faster than UNION.

Let's take a look at an example in which we will perform Union and Union All Query on sample database tables as below

So, if use UNION command using above sample tables

Select Country from [OrderDetails].[dbo].[OrderCountry]
Union
Select Country from [OrderDetails].[dbo].[Orders]

I will get country output as below

Australia
Brazil
Canada
China
India
Nepal
Spain

While if I use Union ALL command in SQL Query as below:

Select Country from [OrderDetails].[dbo].[OrderCountry]
Union All
Select Country from [OrderDetails].[dbo].[Orders]

Output is

India
Canada
China
Brazil
Spain
India
Australia
Brazil
China
Nepal

So as you can see from above results.

Union Returns 'Country' name from 2 tables, as sorted and without any duplicates (Distinct)

While Union All Returns all country names from 2 tables, as it just concatenate records, not eliminate duplicates, so it is faster than UNION.

In order to remove duplicates the result set must be sorted, and this may have an impact on the performance of the UNION, depending on the volume of data being sorted.

You may also like to read:

Get month and year in sql server from date

Import CSV file to MySQL (Query or using Workbench)

Understanding SQL server switch case (With Example)

Get Date From Datetime in SQL Server

TRIM(), LTRIM(), RTRIM() Functions in SQL Server

Best Open Source Database Software (DBMS)