Save Big on Cyber Monday! Up to 40% Off
ends in   {{days}}
Days
{{timeFormat.hours}}
:
{{timeFormat.minutes}}
:
{{timeFormat.seconds}}

Difference Between UNION and UNION ALL in SQL

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 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?

SQL how to use UNION

Dealing with UNIONs might be not so easy for unexperienced users. dbForge SQL Complete can give a helping hand.

  • No need to memorize column names — pick the columns you need from the list.
  • Context-aware code-completion allows you to significantly speed up your coding process.
  • Built-in code snippets enable you to quickly enter repeating chunks of code, saving time and effort. Besides, you can create custom snippets.
  • Syntax checker has zero typo tolerance to even slightest errors, while an advanced Debugger validates your code.
  • Last but not least, you can quickly and easily bring your code up to the standards with the SQL Complete's instant Code Formatter.

In this SQL UNION operator tutorial, we'll explain UNION and UNION ALL using examples from dbForge SQL Complete and a popular AdventureWorks2022 sample database.

SQL UNION example

Let's explore how to use the UNION operator in SQL. Suppose we want to list all employee job titles along with their corresponding pay types. We can achieve this using a simple UNION query. 

SELECT JobTitle,'Salaried' AS PayType
FROM HumanResources.Employee
WHERE SalariedFlag = 1
UNION
SELECT JobTitle,'Hourly' AS PayType
FROM HumanResources.Employee
WHERE SalariedFlag = 0;
T-SQL UNION

dbForge SQL Complete

Accelerate your SQL coding with advanced add-in for SSMS and VS
Free edition available

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 UNION ALL

The syntax for SQL 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.
MSSQL UNION ALL query syntax

SQL UNION ALL example

We want to retrieve a list of last names from two groups in the database: employees and customers. UNION ALL will combine these two sets without removing duplicates. 

SELECT p.LastName,'Employee' AS Source
FROM Person.Person p
WHERE p.BusinessEntityID IN (SELECT
		BusinessEntityID
	FROM HumanResources.Employee)
UNION ALL
SELECT p.LastName,'Customer' AS Source
FROM Person.Person p
WHERE p.BusinessEntityID IN (SELECT
		PersonID
	FROM Sales.Customer
	WHERE PersonID IS NOT NULL);
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

Let us retrieve a list of job titles from both salaried and hourly employees and then order the results alphabetically.

SELECT JobTitle,'Salaried' AS PayType
FROM HumanResources.Employee
WHERE SalariedFlag = 1
UNION
SELECT JobTitle,'Hourly' AS PayType
FROM HumanResources.Employee
WHERE SalariedFlag = 0
ORDER BY JobTitle;

Note that you can use ORDER BY only on the final result set; otherwise, you'll encounter a syntax error.

Using SQL UNION with ORDER BY

Using the WHERE clause in SQL UNION and UNION ALL

You can combine two or more SELECT statements with their WHERE clauses. Let us retrieve a list of manager job titles, categorized into Salaried and Hourly groups.

SELECT JobTitle,'Salaried' AS PayType
FROM HumanResources.Employee
WHERE SalariedFlag = 1 
AND JobTitle LIKE '%Manager%'
UNION
SELECT JobTitle,'Hourly' AS PayType
FROM HumanResources.Employee
WHERE SalariedFlag = 0 
AND JobTitle LIKE '%Manager%';

Ensure all queries have the same number and order of columns, with compatible data types.

Using the WHERE clause in SQL UNION vs UNION ALL

UNION vs. UNION ALL: Comparison

Feature UNION UNION ALL
Duplicate rows Removed automatically Included in the final result
Performance Slower due to duplicate elimination Faster since it skips duplicate checks
Sorting/Filtering Performs an implicit sort to remove duplicates No sorting unless explicitly requested
Result set size May be smaller than the sum of all queries Always equals the total of all queries
Use case When you need distinct records When all data (including duplicates) matters
System resource usage Higher (CPU/memory) Lower, especially with large datasets
Typical scenario Combining lookup data without repetition Merging logs, transactions, or audit data

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. JOIN uses data from one table to select the rows in another table, creating new columns.

Also, JOINs and UNIONs have different purposes. JOINs form a new result table by combining columns from two or more tables. UNIONs combine the results of two distinct queries that deliver records from the same columns, appending the result rows one by one.

SQL JOIN vs UNION

Frequently Asked Questions

What is the primary difference between SQL UNION and UNION ALL?

The primary difference between these operators is that UNION removes duplicate rows from the combined result set, while UNION ALL includes all rows from the query output, including duplicates.

Is there a performance difference between UNION and UNION ALL?

UNION ALL is generally faster than UNION because it does not sort the result set and does not remove duplicate rows from it.

Are there specific use cases where UNION ALL is preferred over UNION?

UNION ALL is the preferred option when duplicates are meaningful or acceptable. For instance, data aggregation, audit trails, or performance-sensitive operations may benefit from using the UNION ALL operator.

When should I use SQL UNION instead of UNION ALL?

UNION is the preferred option when your work scenario requires eliminating duplicate rows.

How do different SQL dialects affect the use of UNION and UNION ALL?

Most SQL dialects support both UNION and UNION ALL. Still, syntax rules (such as parentheses, column aliasing, and ordering) may vary slightly across platforms, including MySQL, PostgreSQL, Oracle, and SQL Server, which can affect how queries are written. Ensure that your query is correctly adjusted to the specific SQL dialect.

How does UNION handle duplicate rows in combined results?

UNION automatically removes duplicate rows from the combined result by comparing all selected columns and returning only distinct records.

What are common mistakes when using UNION or UNION ALL?

The most common mistakes occur when there is a mismatch between column counts or data types in queries, or when the column aliases are incorrect, or when UNION is used instead of UNION ALL (and vice versa).

Is UNION ALL suitable for combining large datasets?

UNION ALL is more efficient for handling large datasets because it skips the extra step of checking for and removing duplicates. As a result, it offers faster performance, produces a lighter query, and is well-suited for merging large volumes of data, provided that duplicate records in the output are acceptable.

Can I use WHERE clauses with UNION queries?

Yes, each SELECT statement in a UNION or UNION ALL query can have its WHERE clause. It allows you to implement specific filtering before applying UNION.

Which is better for reporting: UNION or UNION ALL?

It depends on the reporting needs. If you need distinct records only, use the UNION operator. However, if performance is a concern, UNION ALL is preferable as it does not expend resources on removing duplicate entries. Of course, using UNION ALL is suitable only if your use case allows duplicate records in the report.

dbForge SQL Complete

Accelerate your SQL coding with advanced add-in for SSMS and VS
Free edition available