Difference between UNION and UNION ALL T-SQL Operators

If you want to combine output from multiple tables or queries into one result set, you can do this using an operator known as UNION. A UNION is used to extract rows based on some conditions specified whereas UNION ALL will extract all rows from both tables. Here, i will discuss difference between UNION and UNION ALL operators ( UNION vs UNION ALL ) in this article along with their use cases.


The UNION operator allows us to combine the results of two or more SELECT statements into a single result set and removes duplicate rows from that result set. Basically, it is performing a DISTINCT operation across all columns in the result set. The UNION operator is different from using joins that combine columns from two tables.

The following are basic rules for combining the result sets of two queries by using UNION:

  • The number and the order of the columns must be the same in all queries.
  • The data types must be compatible.
  • Corresponding result set columns in the individual statements that are being combined with UNION must occur in the same order, because UNION compares the columns one-to-one in the order given in the individual queries.
  • The column names in the result from UNION are taken from the first individual query in the UNION statement.

Below example shows how can you use UNION to combine multiple outputs into one result set.


If tables would not have same structure or same data types, you will get below error while using UNION:

error 205

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.


UNION ALL operator also works like UNION operator that is used to combine multiple result sets into one result set but duplicate rows will not be removed from the result set using UNION ALL unlike the result set of UNION operator that gives distinct rows.


Difference Between UNION and UNION ALL ( UNION vs UNION ALL )

Please find important points if we compare the difference between UNION vs UNION ALL result sets and their uses.

  • The UNION operator removes duplicate rows from the result set and gives us distinct rows whereas UNION ALL operator does not remove duplicate rows and give all outputs in the result set.
  • UNION ALL operator works faster than UNION operator because it does not remove duplicate rows. UNION operator performs distinct sort to remove duplicate rows so its performance is little slower than UNION ALL.
  • Operator UNION ALL works with all data type columns whereas UNION operator does not work with a column that has a TEXT data type and it will throw error during execution.

Guidelines to use UNION  and UNION ALL

I have discussed difference between UNION and UNION ALL in above section. Here i will explain below guidelines you should consider while using UNION and UNION ALL operators with other T-SQL statements. Below rules are also apply to EXCEPT and INTERSECT operator:

  • The first query can contain an INTO clause that creates a table to hold the final result set. Only the first query can use an INTO clause. If the INTO clause appears anywhere else, SQL Server displays an error message.
  • ORDER BY is allowed only at the end of the statement. It cannot be used within the individual queries that make up the statement.
  • GROUP BY and HAVING clauses can be used only within individual queries; they cannot be used to affect the final result set.
  • UNION, EXCEPT and INTERSECT can be used within an INSERT statement.
  • The FOR BROWSE clause cannot be used in statements that involve the UNION, EXCEPT and INTERSECT operators.

Related Articles:

I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

Manvendra Deo Singh
Follow me:
Article Name
Difference between UNION and UNION ALL Operator
If you want to combine output from multiple tables or queries into one result set, you can do this using an operator known as UNION.  Here, i will discuss UNION and UNION ALL operator and the differences between them.
Publisher Name

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *