Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
With UNION and UNION ALL Opertors, we can merge multiple sql statement’s result in a single result set.
When we merge two SQL statements with the UNION operator, duplicate records are listed once in the result set.
When we merge two SQL statements with the UNION ALL operator, all results are listed.
If there are no duplicate records in queries that we will merge with union or union all, UNION and UNION ALL will return the same result set. We will explain the difference in performance between them at the end of the article.
UNION or UNION ALL operators are used when we need to merge multiple result sets in SQL Server. For example, let’s assume that the internet sales and the store sales are in different tables in a database. To list the total sales, a combination of these two tables is required. When using UNION and UNION ALL operators, the following points should be considered.
- The number of columns in all result sets must be the same.
- The data types of the columns corresponding to each other must match each other.
Examples about UNION and UNION ALL:
First, let’s create 2 sample tables.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE TABLE Test ([ID] INT IDENTITY, [Name] NVARCHAR(20), [Surname] NVARCHAR(50), [Date] DATE) INSERT Test VALUES ('Hakan','GURBASLAR','2017-01-01'),('Nurullah','CAKIR','2017-01-01'),('Faruk','ERDEM','2017-01-01') CREATE TABLE Test2 ([ID] INT IDENTITY, [Name] NVARCHAR(20), [Surname] NVARCHAR(50), [Date] DATE) INSERT Test2 VALUES ('Hakan','GURBASLAR','2015-01-01'),('Nurullah','CAKIR','2016-01-01'),('Faruk','ERDEM','2017-01-01') |
First, let’s see the difference between UNION and UNION ALL:
UNION ALL:
1 2 3 4 5 | SELECT [Name],[Surname] FROM Test UNION ALL SELECT [Name],[Surname] FROM Test2 |
UNION:
1 2 3 4 5 | SELECT [Name],[Surname] FROM Test UNION SELECT [Name],[Surname] FROM Test2 |
As seen in this example, when we use UNION, the recurring records are listed 1 times in the result set.
When we try to merge two result sets with a different number of columns, we get an error as follows.
1 2 3 4 5 | SELECT [Name],[Surname],[ID] FROM Test UNION ALL SELECT [Name],[Surname] FROM Test2 |
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Let’s change the column order in Query. Because the data types are the same, we will not encounter any errors in UNION operation.
1 2 3 4 5 | SELECT [Surname],[Name] FROM Test UNION ALL SELECT [Name],[Surname] FROM Test2 |
If we change the query as follows, we get an error when we run the query. Because the data types of the ID and Date columns do not match.
1 2 3 4 5 | SELECT [Name],[Surname],[ID] FROM Test UNION ALL SELECT [Name],[Surname],[Date] FROM Test2 |
Msg 206, Level 16, State 2, Line 1
Operand type clash: int is incompatible with date
Let’s run the UNION query by taking only the year parts of the date columns.
1 2 3 4 5 | SELECT [Name],[Surname],YEAR([Date]) FROM Test UNION ALL SELECT [Name],[Surname],YEAR([Date]) FROM Test2 |
We said that if there are no duplicate records in the two queries, UNION and UNION ALL will bring the same result.
So how are the performance values of UNION and UNION ALL?
The following two query will return the same result set. Because there is no duplicate record. When we examine the execution plans, the cost of the query is almost tripled as the Distinct Sort operation is done in the UNION process. Therefore, if you are sure that there are no duplicate records, you must use UNION ALL.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SELECT [Name],[Surname],YEAR([Date]) FROM Test Where [Name]='Hakan' UNION SELECT [Name],[Surname],YEAR([Date]) FROM Test2 Where [Name]='Nurullah' GO SELECT [Name],[Surname],YEAR([Date]) FROM Test Where [Name]='Hakan' UNION ALL SELECT [Name],[Surname],YEAR([Date]) FROM Test2 Where [Name]='Nurullah' |
