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.
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:
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.
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.
- How to Protect Stored Procedure Code?
- Create a Logon Trigger to Restrict sysadmin logins to Connect to SQL Server during a given time Interval
- How to get Total Row Count of all tables of a database
- SQL Server Update Statistics Best Practices - April 15, 2020
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018