Module 3: UNION and UNION ALL

Previous Tutorial - Next Tutorial

Set operators operate on two result sets of queries that compare the complete rows between the results. SQL join expands the result set horizontally, that is columns are added to the results from multiple tables which is essentially widening the result. While set operators expand the results vertically as the records are piled up on top of the other.

SQL Server supports four types of set operators which are UNION, UNION ALL, INTERSECT, and EXCEPT. First, let's get started with the UNION and UNION ALL operators. A union or a union all query expands the result vertically as records are piled up on top of one another. It combines multiple results with the same number of columns and returns a single result set and in the result, all the columns must have compatible data types.

There are some rules which we need to take care of while using the set operators in the SQL server. We will go through each one within a few minutes. This is the general syntax of the union and union all operators. Here in the syntax, I have mentioned two queries but you can define more than two queries to fulfill your requirement. Almost everything is the same in these two operators.

Now the question is what is the difference between a union and union all operators? The key difference between these two is UNION ALL incorporates all the rows into the results including duplicates while UNION returns unique or distinct rows only in the result set. So as UNION ALL does not perform distinct operations in the results set, It is much faster in comparison with the UNION operator. But It does not remove duplicate results from the result. Now open the SQL server management studio to see this operator in action.

For demo purposes, I am using two tables which are the country region currency table and currency table. This country region currency table contains 109 rows and the currency table contains 105 rows. For demo purposes, I am returning only a single column from both of the tables.

If I use the union all operator then that operator will return all the records from this country region currency table and this currency table. First copy this query (SELECT currencycode FROM Sales.Currency) and I want this result in an order by currency code. Now write the order by clause (ORDER BY CurrencyCode) and execute the query.

Union ALL SQL

This query is returning me 214 rows. If you remember that this country region currency table contains 109 rows and this currency table contains 105 rows. Union all operators return all the rows from both the tables which are 214 rows. What happens if I use only the union operator? For that copy of the query, I'm going to remove this all and execute the query. As you see with the union operator you will get 105 rows which are distinct rows from both of the tables.

As I said earlier that when you use a union operator then it will perform the distinct operation and return only a unique record from both of these queries. Union all does not perform a distinct operation in the results set, so it is much faster in comparison with the union operator. To check that, let enable the actual execution plan and execute these two queries. You can clearly see that union all operator consume 35% and union can consume 65% of the overall costs of the query.

Before we dig deep into other operators we should first understand some of the basic rules for combining the results set of two or more queries. Let us understand what are the basic rules which we need to consider. The first basic rule for the set operators which we need to consider is the number of columns in the order of the column must be the same in all queries.

Let's understand these rules practically in the management studio. I have defined one query and execute this query then it will return me two columns first name and last name. If I use union all and write a second query, here I'm not going to define the second column and without the second column let execute this query. If I execute this query then it will give me an error that clearly says that and this operator must have an equal number of expressions in the target list.

Union ALL

Now let's go to the second rule which says the data types of the corresponding columns must be compatible. To understand this demo let's define another column and copy this query and let me extract it over here. Now in the second query INSERT of two, I have written in the single code (123). Here in the first query customer ID column is of type Integer. In the second query, the same column is of the type where care.

Now when I execute this query then it will give me an error which says the conversion failed when converting the where care value to data type Integer. In short, the data type of the corresponding columns must be compatible.

Now let's move on to the third rule. The third rule says individual queries are not allowed to have an order by clause. To understand that rule let me add an order by clause to both of these single queries. Now, these two individual queries have their order by clause. When I execute this query it will give me an in-corrects syntax error, which means when you use set operators individual queries are not allowed to have an order by clause.

SQL Union ALL

Now let's move to the fourth rule. It says the column names of the result columns are determined by the first query. Let me remove this redundant order by clause. In the first query, I have given the first column name as a customer ID. In the second query what if I give this column name as employee ID. So let's change this.

Now when I execute this query, in the result you will always get the customer ID as the first column which means when you use any set operators the column name of the result column is always determined by the first query. In the next slide, we will learn the next two operators which intersect and except.

Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials: