Black Friday is here! Get up to 30% off on Devart products!
ends in   {{days}}
Days
{{timeFormat.hours}}
:
{{timeFormat.minutes}}
:
{{timeFormat.seconds}}

UNION vs. UNION ALL in SQL Server:
What's the Difference?

SQL UNION operator definition

In SQL, the UNION clause combines the results of two or more SELECT statements into a distinct single result set without returning any duplicate rows. In other words, SQL UNION operator concatenates the results of two queries and removes duplicate values.

SQL UNION - get distinct values

Fundamental rules for using UNION

  • The number of columns in all queries combined by UNION must be the same.
  • The order of the columns in all queries combined by UNION must be the same.
  • The data types in the columns combined by using UNION must be compatible.
  • The number of expressions in all queries combined by UNION must be the same.

Basic syntax of the SQL UNION

Let's look at how UNION functions in SQL. The syntax for it is as follows: 

SELECT column_1, column_2, ... column_n
FROM table_1
UNION
SELECT column_1, column_2, ... column_n
FROM table_2
...
UNION
SELECT column_1, column_2, ... column_n
FROM table_N;

Why SQL Complete?


SQL how to use UNION

Dealing with UNIONs might be not so easy for unexperienced users — you need to clearly understand how the operator works and remember the names of the columns you want to combine. That's where SQL Complete can give a helping hand.

  • No need to memorize column names — just pick the columns you need from the suggestion list.
  • Powerful context-aware code-completion will allow you to significantly speed up your coding process.
  • With the built-in code snippets you can quickly enter repeating chunks of code and thus save your time and effort. And in case you work a lot with UNIONs, you can create custom snippets and then use them when required.
  • The impeccable syntax checker has zero typo tolerance and will let you know even of a slightest error, while an advanced Debugger will validate your code.
  • And last but not least, you can quickly and easily bring your code up to the company or team standards with the help of SQL Complete's instant Code Formatter.

As part of our SQL UNION operator tutorial, we will explain UNIONs with the examples presented in the dbForge SQL Complete tool and try to cover the difference between UNION and UNION ALL in SQL.

SQL UNION example

Let's look at how to use UNION in SQL.

Suppose, we have the customerdemo database with the Employee table in it. The table contains ten records. We want to find out the ids of managers who the employees whose last names begin with 'S' and 'T' report to. In the output you can see two ids. 

SELECT
ManagerID
FROM Employee
WHERE LastName LIKE 'S%'
UNION
SELECT
ManagerID
FROM Employee
WHERE LastName LIKE 'T%'
T-SQL UNION

dbForge SQL Complete

Enjoy even the most complex queries with SQL Complete

Download

SQL UNION ALL operator explained

The SQL Server UNION ALL operator allows combining the results of two or more SELECT statements. It returns all rows from the query and does not eliminate duplicate rows.

What is the difference between UNION and UNION ALL?

Generally the UNION ALL command is quite similar to the UNION command — the only difference is that UNION ALL selects all values. In other words, UNION ALL will not remove duplicate rows. Instead, it will fetch all rows matching the query and combine them in a result table.
SQL JOIN same table twice on same column

Basic syntax of SQL UNION ALL

The T-SQL syntax for UNION ALL is as follows:

SELECT column_1, column_2, ... column_n
FROM table_1
UNION ALL
SELECT column_1, column_2, ... column_n
FROM table_2
...
UNION ALL
SELECT column_1, column_2, ... column_n
FROM table_N;

Note
You can add conditions to the SELECT statements if required.

MSSQL UNION ALL query syntax

SQL UNION ALL example

Let's now look at how to use SQL UNION ALL.

Remember that Employee table we have discussed earlier? Let's run UNION ALL against it. 

SELECT
ManagerID
FROM Employee
WHERE LastName LIKE 'S%'
UNION ALL
SELECT
ManagerID
FROM Employee
WHERE LastName LIKE 'T%'

As you can see, there are four results in the output. That's because UNION ALL, as we mentioned earlier, doesn't remove duplicates from the result set.

MSSQL UNION

Performance of the UNION and UNION ALL functions

As we have already mentioned, UNION returns only distinct records, while UNION ALL returns all the records retrieved by queries. If we speak about SQL Server UNION vs UNION ALL performance, UNION ALL gives better performance in query execution as it does not waste resources on removing duplicate rows.

Let's look at the difference using SQL Server execution plans.

You can see that the estimated cost of the UNION ALL query is significantly lower, while UNION has an additional Sort operation, and the large amount of resources is spent on this operation.

SQL Server UNION vs UNION ALL performance

Using the ORDER BY clause in SQL UNION and UNION ALL

You cannot use the ORDER BY clause with each SELECT statement in the query. SQL Server can perform sorting only on the final result set.

If you try running a query with the ORDER BY clauses on multiple SELECT statements, you will get the 'Incorrect syntax' error.

The SQL syntax for UNION with ORDER BY is as follows:

SELECT column_name
FROM table_1
UNION\UNION ALL
SELECT column_name
FROM table_2
ORDER BY order_by_expression;
SQL SELF JOIN subquery using WHERE

Using the WHERE clause in SQL UNION and UNION ALL

UNION and UNION ALL allow combing two or more SELECT statements with their WHERE clauses. However, you need to make sure that the number and order of columns in all queries are the same, and that their datatypes are compatible. 

SELECT column_1, column_2
FROM table_1
WHERE condition
UNION\UNION ALL
SELECT column_1, column_2
FROM table_2
WHERE condition;
Where in SQL Union vs Union All

Difference between UNION and JOIN in SQL Server

Both JOIN and UNION combine data; the difference lies in how they perform that operation. UNION places the query results after each other and forms a single result set. While JOIN, in fact, specifies how SQL Server should use data from one table to select the rows in another table, and the data combined using the JOIN statement results into new columns.

It should be mentioned that JOINs and UNIONs have two absolutely different purposes. JOINs are used to form a new result table by combining columns from two or more tables. UNIONs, however, are used to combine the results of two distinct queries with the same columns, they append the result rows together row by row.

SQL JOIN vs UNION

dbForge SQL Complete

Advanced solution for SQL database development, management,
and administration

Download
Try dbForge SQL Complete and double the speed of your SQL coding! Try now

We use cookies to provide you with a better experience on the Devart website. You can read more about our use of cookies in our Cookies Policy.
Click OK to continue browsing the Devart site. Be aware you can disable cookies at any time.