This article explains to the SQL Union and vs Union All operators in SQL Server. We will also explore the difference between these two operators along with various use cases.

SQL Union Operator Overview

In the relational database, we stored data into SQL tables. Sometimes we need to Select data from multiple tables and combine result set of all Select statements. We use the SQL Union operator to combine two or more Select statement result set.

The syntax for the SQL Union operator

We need to take care of following points to write a query with the SQL Union Operator.

• Both the Select statement must have the same number of columns
• Columns in both the Select statement must have compatible data types
• Column Order must also match in both the Select statement
• We can define Group By and Having clause with each Select statement. It is not possible to use them with the result set
• We cannot use Order By clause with individual Select statement. We can use it with result set generated from the Union of both Select statements

In the following screenshot, we can understand the SQL UNION operator using a Venn diagram.

• Table A having values 1,2,3,4
1 2 3 4 5 6

CREATE TABLE TableA(     ID INT ); Go INSERT INTO TableA VALUES(1),(2),(3),(4);

• Table B having values 3,4,5,6
1 2 3 4 5 6

CREATE TABLE TableB(     ID INT ); Go INSERT INTO TableB VALUES(3),(4),(5),(6);

If we use SQL Union operator between these two tables, we get the following output.

Output: 1, 2, 3,4,5,6

In my example, TableA and TableB both contain value 3 and 4. In the output, we do not get duplicate values. We get only one row for each duplicate value. It performs a DISTINCT operation across all columns in the result set.

Let look at this with another example. For this example, I created two tables Employee_F and Employee_M in sample database AdventureWorks2017 database.

Execute following script for Employee_F table

Execute following script for Employee_M table

Both the tables do not contains any duplicate rows in each other tables. Let us execute following UNION statement.

Both the tables do not have duplicate rows. Therefore, we get all records from both tables in the output of SQL Union operator. It contains ten records in the output.

Let us create another table that contains duplicate rows from both the tables.

Now, we will use the SQL UNION operator between three tables. We should still get ten records because [Employee_All] contains records that already exist in Employee_M and Employee_F table.

In the following image, you can see a UNION of these three tables do not contain any duplicate values.

SQL Union All Operator Overview

The SQL Union All operator combines the result of two or more Select statement similar to a SQL Union operator with a difference. The only difference is that it does not remove any duplicate rows from the output of the Select statement.

The syntax for SQL Union All operator

Let us rerun the previous examples with SQL Union All operator.

In the following image, you can see Output of both SQL Union vs Union All operators. SQL Union All return output of both Select statements. It does not remove any overlapping rows.

If the tables do not have any overlapping rows, SQL Union All output is similar to SQL Union operator.

We can see following output of SQL Union All output of Employee_M and Employee_F tables.

Now, rerun the query with three tables Employee_M and Employee_F and Employee_All tables. We got 10 records in output of SQL Union between these three tables. Each table contains 5 records. We should get 15 rows in the output of Union All operator on these tables.

SQL Union Vs Union All Operator

Execution plan difference in SQL Union vs Union All operator

We get better query performance once we combine the result set of Select statement with SQL Union All operator. We can look at the difference using execution plans in SQL Server.

Note: In this article, I am using ApexSQL Plan, a SQL query execution plan viewer to generate an execution plan of Select statements.

The execution plan for the SQL Union Operator

We can click on Sort operator, and it shows Distinct – True.

1. It gets the data individual Select statement
2. SQL Server does a Concatenation for all of the data returned by Select statements
3. It performs a distinct operator to remove duplicate rows

The execution plan for SQL Union All operator

In the execution plan of both SQL Union vs Union All, we can see the following difference.

• SQL Union contains a Sort operator having cost 53.7% in overall batch operators
• Sort operator could be more expensive if we work with large data sets

Order By clause in SQL Union vs Union All clause

We cannot use the Order by clause with each Select statement. SQL Server can perform a sort in the final result set only.

Let’s try to use Order by with each Select statement.

We get the following error message. It gives a “incorrect syntax” error message.

The valid query to sort result using Order by clause in SQL Union operator is as follows.

We get the following output with result set sorted by JobTitle column.

Combination of SQL Union vs Union All in a Select statement

We can use SQL Union vs Union All in a Select statement. Suppose we want to perform the following activities on our sample tables.

• Result Set A= UNION between [Employee_F] and [Employee_All]
• Result Set= Union All between [Employee_M] and Result Set A

In the following query, we use parentheses to perform Union between [Employee_F] and [Employee_All] tables. SQL Server runs the query inside parentheses and then performs Union All between result set and [Employee_M] table.

We can understand it easily with execution plan. In the following screenshot, we can see the Actual Execution plan.

Step 1: Concatenation data (SQL Union) between Employee_F and Employee_All table.

Step 2: Concatenation data (SQL Union All) between Employee_M and Step 1 output.

Conclusion

In this article, we compared SQL Union vs Union All operator and viewed examples with use cases. I hope you found this article helpful. Feel free to provide feedback in the comments below.

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023