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.  Here, i will discuss difference between UNION and UNION ALL T-SQL operators.

UNION

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.

SELECT * FROM Table1
UNION
SELECT * FROM Table2
UNION
SELECT * FROM Table3

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

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.

SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
UNION ALL
SELECT * FROM Table3

Difference Between UNION and UNION ALL

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

Follow below guidelines while using UNION & UNION ALL operators with other Transact-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.

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:
Summary
Article Name
Difference between UNION and UNION ALL Operator
Description
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.
Author
Publisher Name
www.techyaz.com

You may also like...

Leave a Reply

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