SQL SERVER – Introduction and Example of UNION and UNION ALL

October 15, 2008

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

It is very much interesting when I get request from blog reader to re-write my previous articles. I have received few request to rewrite my article SQL SERVER – Union vs. Union All – Which is better for performance? wi.th examples. I request you to read my previous article first to understand what is the concept and read this article to understand the same concept with example.

xe=”color:green;”>/* Create First Table */
DECLARE @Table1 TABLE (Col INT)
INSERT INTO @Table1
SELECT 1
INSERT INTO @Table1
SELECT 2
INSERT INTO @Table1
SELECT 3
INSERT INTO @Table1
SELECT 4
INSERT INTO @Table1
SELECT 5

/* Create Second Table */
DECLARE @Table2 TABLE (Col INT)
INSERT INTO @Table2
SELECT 1
INSERT INTO @Table2
SELECT 2
INSERT INTO @Table2
SELECT 6
INSERT INTO @Table2
SELECT 7
INSERT INTO @Table2
SELECT 8

/* Result of Union operation */
SELECT Col ‘Union’
FROM @Table1
UNION
SELECT Col
FROM @Table2

/* Result of Union All operation */
SELECT Col ‘UnionAll’
FROM @Table1
UNION ALL
SELECT Col
FROM @Table2
GO

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

SQL SERVER - Introduction and Example of UNION and UNION ALL unionunionall

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

If you look at the resultset it is clear that UNION ALL gives result unsorted but in UNION result are sorted. Let us see the query plan to see what really happens when this operation are done.

SQL SERVER - Introduction and Example of UNION and UNION ALL unionplan

From the plan it is very clear that in UNION clause there is an additional operation of DISTINCT SORT takes place where as in case of UNION ALL there is no such operation but simple concatenation happens. From our understanding of UNION and UNION ALL this makes sense.

There are three rules of UNION one should remember.

UNION RULES

A UNION must be composed of two or more SELECT statements, each separated by the keyword UNION.
Each query in a UNION must contain the same columns, expressions, or aggregate functions, and they must be listed in the same order.
Column datatypes must be compatible: They need not be the same exact same type, but they must be of a type that SQL Server can implicitly convert.

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Joins, SQL Scripts, SQL Union clause

SQL SERVER – Get Numeric Value From Alpha Numeric String – UDF for Get Numeric Numbers Only

SQL SERVER – Downgrade Database to Previous Version