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

SQL UNION operator definition

The difference between UNION and UNION ALL lies in how duplicates are handled. UNION combines the results of two or more SELECT statements and removes duplicate rows, while UNION ALL returns every row, including duplicates.

In other words, SQL UNION syntax requires the same number of columns, in the same order, with compatible data types, and produces a distinct result set.

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.

Data type compatibility in SQL UNION and UNION ALL

SQL Server enforces strict rules on column data types when you use UNION or UNION ALL. Each SELECT in the query must return the same number of columns, and the columns are matched by position, not by name. The data types in those positions must be compatible so that SQL Server can implicitly convert them to a common type.

Compatible types example

SELECT CAST(100 AS INT) 
UNION 
SELECT CAST(250.75 AS DECIMAL(10,2));

Here, INT is automatically converted to DECIMAL, so the query runs without issue.

Incompatible types example

SELECT 'SampleText' 
UNION 
SELECT GETDATE();

This query fails because SQL Server tries to implicitly convert the string to DATETIME, but SampleText is not a valid date/time value, so the conversion fails and an error is raised.

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Key takeaway

When combining queries with UNION or UNION ALL, always make sure that:

• Each query returns the same number of columns.
• The column order matches.
• The data types in each position are compatible or explicitly converted.

This prevents conversion errors and keeps your queries predictable.

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 dbForge SQL Complete?

Dealing with UNIONs might not be so easy for inexperienced users, you need to clearly understand how the operator works and remember the names of the columns you want to combine. That's where dbForge 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 the least, you can quickly and easily bring your code up to the company or team standards with the help of dbForge 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

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 to whom 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%'

How UNION and UNION ALL handle NULLs

A key difference between UNION and UNION ALL is how they treat NULL values. In SQL Server, NULL is considered equal to another NULL when removing duplicates.

• With UNION, duplicate NULL values are eliminated, and only one NULL appears in the final result set.
• With UNION ALL, all NULL values are preserved.

Example with NULL values

Suppose we have the following two queries on an Employee table where some managers are unknown (NULL).

SELECT ManagerID 
FROM Employee 
WHERE LastName LIKE 'S%' 

UNION
 
SELECT ManagerID 
FROM Employee
WHERE LastName LIKE 'T%';

Result (UNION)

Now compare with UNION ALL.

SELECT ManagerID 
FROM Employee 
WHERE LastName LIKE 'S%' 

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

Result (UNION ALL)

In the UNION query, only one NULL is kept. In the UNION ALL query, both NULL values are returned.

Takeaway: If duplicates, including NULLs, are meaningful for your analysis, use UNION ALL.

Using UNION with subqueries

You can also use UNION and UNION ALL to combine the results of subqueries. This is useful when you need to apply filters, perform aggregations, or work with derived data before merging it into a single result set.

Basic syntax

SELECT subquery_alias.column 
FROM (subquery_1) AS subquery_alias 

UNION [ALL] 

SELECT subquery_alias.column 
FROM (subquery_2) AS subquery_alias;

Common use cases:

• Aggregating results from different tables and combining them.
• Filtering subsets of data before merging into a final report.
• Comparing results across regions, departments, or categories.

Example: Sales by region

Suppose we have a Sales table with columns Region, OrderDate, and Amount. We want to compare total sales for two different regions. By wrapping each query in a subquery, we can aggregate results separately and then combine them into a single dataset.

SELECT Region, TotalSales 
FROM ( 
   SELECT 'North' AS Region, SUM(Amount) AS TotalSales 
   FROM Sales 
   WHERE Region = 'North' 
) AS NorthSales 
UNION 
SELECT Region, TotalSales 
FROM ( 
    SELECT 'South' AS Region, SUM(Amount) AS TotalSales 
    FROM Sales 
    WHERE Region = 'South' 
) AS SouthSales;

Result

This approach allows you to work with aggregated subquery results and present them as a single, combined dataset.

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.

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;

SQL UNION ALL example

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

Remember that Employees 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 from the result in the output, UNION ALL, as we mentioned earlier, doesn't remove duplicates from the result set.

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.

Execution plan comparison

To see how this works in practice, here's what the execution plans show:

• UNION: Includes a Sort operator to check and remove duplicates. This adds overhead in CPU and memory, especially with large datasets.
• UNION ALL: Skips sorting and directly combines rows, resulting in lower cost and faster execution.

Quick comparison table

The following table summarizes the main differences at a glance.

To improve performance, consider using SQL UNION ALL when duplicates are acceptable. Beyond execution time, UNION and UNION ALL also differ in how they use memory on large datasets.

Memory and performance considerations with UNION ALL

On large tables, the performance gap between UNION and UNION ALL becomes significant. The reason lies in how SQL Server processes duplicates.

• UNION adds a Sort step to order rows and remove duplicates. This operation consumes extra memory and CPU, and in extensive datasets it can even spill to tempdb, slowing the query further.
• UNION ALL skips this step entirely, appending rows as they are. With no sorting or deduplication, memory usage is lower and execution time is faster.

Example with execution plans

-- Using UNION 
SELECT CustomerID FROM Orders_2024 
UNION 
SELECT CustomerID FROM Orders_2025;

-- Using UNION ALL
SELECT CustomerID FROM Orders_2024 
UNION ALL 
SELECT CustomerID FROM Orders_2025;

In the execution plan:

• The UNION query shows a Sort operator used to eliminate duplicates, which raises the estimated query cost.
• The UNION ALL query has no Sort step, so rows are combined directly with less overhead.

Takeaway: When duplicates are acceptable, use UNION ALL. It scales better on large datasets by avoiding unnecessary sorting, reducing memory grants, and lowering the chance of spills to tempdb.

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;

Using the WHERE clause in SQL UNION and UNION ALL

UNION and UNION ALL allow combining 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;

How to use UNION with different column names

When combining queries with UNION or UNION ALL, SQL Server aligns columns by position, not by name. This means the first column in the first query will be matched with the first column in the second query, regardless of their names.

If the column names differ, you can use aliases to make the output more readable and avoid confusion. The final result set will use the column names from the first query.

Example with mismatched column names

Suppose we have two tables:

• Customers with a column CustomerID
• Orders with a column BuyerID

SELECT CustomerID AS ClientID 
FROM Customers 

UNION
 
SELECT BuyerID AS ClientID 
FROM Orders;

Result

Here, CustomerID and BuyerID are aligned by position. By applying the alias ClientID, the final output has a consistent column name.

UNION vs. UNION ALL: Comparison

Difference between UNION and JOIN in SQL Server

Both JOIN and UNION combine data, but they do so in very different ways.

• UNION places the results of queries on top of each other, stacking rows into a single result set. The number of columns and their data types must match.
• JOIN merges data side by side, combining columns from multiple tables based on a related key.

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, appending the result rows together row by row.

Visual comparison

To make the distinction clearer, think of the operations like this:

• UNION - combines result sets vertically: same columns, rows from each query stacked one after another.
• JOIN - combines tables horizontally: rows matched by key, additional columns added from the other table.

Frequently asked questions