Rajendra Gupta
SQL Union vs Union All - SQL Union operator

SQL Union vs Union All in SQL Server

April 18, 2019 by

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
  • Table B having 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

SQL Union vs Union All - SQL Union operator

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.

SQL Union vs Union All - Example of SQL Union operator

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 operator

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.

SQL Union All operator

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.

Example of SQL Union All operator

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.

Example of SQL Union All operator

SQL Union Vs Union All Operator

Union

Union All

It combines the result set from multiple tables with eliminating the duplicate records

It combines the result set from multiple tables without eliminating the duplicate records

It performs a distinct on the result set.

It does not perform distinct on the result set

We need to specify Union operator

We need to specify Union All Operator

SQL Union All gives better performance in query execution in comparison to SQL Union

It gives better performance in comparison with SQL Union 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 free SQL query execution plan viewer to generate an execution plan of Select statements.

The execution plan for the SQL Union Operator

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 the SQL Union Operator

The execution plan for SQL Union All operator

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.

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.

Order by clause and Union

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.

Combination of SQL Union and SQL Union All in a Select statement

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
4,107 Views