SQL SERVER – Introduction and Example of UNION and UNION ALL

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.

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.

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 (http://blog.SQLAuthority.com)

About these ads

18 thoughts on “SQL SERVER – Introduction and Example of UNION and UNION ALL

  1. Hello,

    I wish I had read this article one day before.

    Yesterday when I was working on one production issue, I saw a strange case, in which we create two temporary table on the fly, and then we do a select statement with a union. Every time we use to get an error… You wont believe we spent 2-3 hours on it. At last we saw the temporary tables that were created with select * into command was creating different datatype, one with int and another with char, and union was not able to perform its action and our process was failing. I learnt a good lesson, it was worth it :)

    Thanks,
    Imran.

  2. Its a hobby to read your blog’s article on daily basis to refresh my knowledge.

    Some tricky part which mostly ignore, Catch it from your articles.

    Keep up the good work.

    God Bless you

    Joggee.

  3. First time i am going through this site.It’s simply the best.
    U have all the details without redundant details. Thanks tons for this.

  4. is ther a way to use a union all statement and then issue a where at the end to filter the entire query?

    example:

    table 1
    tcol1 tcol2 tcol3
    1 1 1
    2 2 1

    table 2
    tcol1 tcol2 tcol3
    3 3 1
    4 4 2

    SELECT tcol1,tcol2 FROM (
    SELECT tcol1,tcol2
    UNION ALL
    SELECT tcol1,tcol2
    ) AS temptable
    WHERE tcol3=’1′ <—

    thanks

  5. Is it possible to use ‘union all’ in hibernate, Since I am using Java and Hibernate for my application, I need to convert the query using ‘union all’ to hibernate.Please Help me to solve this issue.

  6. I was looking at performance gaining and a friend of mine doing a peer review of my code suggested that we use union all instead of union, I was not sure how I will gain performance and while researching came across your article.

    I do need a distinct list of records and therefore used union
    but she has an opinion that I do a union all and then group by the columns individually before applying union all.

    Which is best?
    get the distinct list of table 1 (by using group by clause) and distinct list of table2 and then do union all

    or select list from table 1 and select list from table 2 and do union there by eliminating duplicates in individual tables

  7. @Aparna,

    Why don’t you look at Execution plan. Look at cost of each step and each query. You will get an answer.

    Also, please post your observation.

    ~ IM.

  8. I was short of time with lots of work in hand so did not really look at the execution plan. I will post my findings in this forum. Thanks Imran for your suggestion. wondering if anyone had similar issues.

    FYI Table 1 is not distint by itself and Table 2 is not distinct by itself but there are dateid’s in Table1 and Table2 that makes their union distinct

    TABLE1
    DATEID INVESTOR
    20090530 1
    20090530 2
    20090530 1

    TABLE 2

    DATEID INVESTOR
    20090630 1
    20090630 2
    20090630 1

    Any one has thoughts until I look at the execution plan

  9. Hi
    I have a question .

    I need to use select statement and use two from clauses.

    that is, everything is same in both the select statements and from clause except there is an extra join in one from clause. what i am trying to do is use condition and take the reqired from clause but i could not do it.

    Any help plz

  10. Pingback: SQL SERVER – UNION ALL and UNION are Different Operation | SQL Server Journey with SQL Authority

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #051 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s