Sql Server – Union and Union All

Posted on November 17, 2009 by vivekjohari
Union and Union All, both are used to select data from one or more than one tables but still they have some differences. For Example, Union is used to select distinct data from the tables but Union All allows duplicate rows to be selected from the tables.

Suppose we have a table called tbl_Manager and the structure of this table is given below:-

CREATE TABLE tbl_Manager(Managerid int identity(1,1) not null,Managername nvarchar(255),Departmentname nvarchar(255))

Now Suppose we insert the data into the table with the help of the query given below:-

Insert into tbl_Manager(Managername,Departmentname)
select ‘Vivek Johari’, ‘Technology’ union all
select ‘Atul’,’Testing’ union all
select ‘Vivek Johari’, ‘Mobile development’ union all
select ‘Atul’,’HR’ union all
select ‘Virender Singh’, ‘Web development’ union all
select ‘Virender Singh’, ‘R&D development’ union all
select ‘Jagdish’,’Quality Assurance’ union all
select ‘Jagdish’,’Mobile development’ union all
select ‘Atul’,’Technology’

Now Suppose, we want to select the “Managername” from the table who work in the department “Technology” and “Mobile development”.

First, we try to get the result with the help of Union.In this case the query will be given below:-

select Managername from tbl_Manager where departmentname=’Technology’
union
select Managername from tbl_Manager where departmentname=’Mobile development’

Result :-

Now if we use the Union All instead of union to selet the managername from the table who work in the department “Technology” and “Mobile development”, we get the following result:-

select Managername from tbl_Manager where departmentname=’Technology’
union all
select Managername from tbl_Manager where departmentname=’Mobile development’

Result:-

By analyzing the two results, one can easily find out that the Union all allowed dublication of the data and Union do not allows the duplicate data to appear.

The second difference between Union and Union All can be given on the basic of their uses. Both Union and Union All  can be used to insert multiple rows in the table in a single query. You can see the use of the Union All in the insert statement, which we have used to insert the data into the table tbl_Manager.But in case where we required to insert multiple rows containing duplicate values , we can’t use Union for this purpose. Also the use of Union will increase the cost of execution plan for the insert query as compared to Union all..

Also since Union does not allowed the duplication of the data, therefore it has to first select the whole data and then use the Distinct function to eliminate the duplicate data.It will increase the cost of the execution plan for the query since it have to use the two functions whereas Union All allow the duplication of the data so it only needs the select statement to show the data. So it will cost little as compare to the Union.

Summary
Both Union and Union All are used to select the data from the tables but we should use Union All unless it is required to fetch only the distinct data since the use of Union will increase the cost of the execution of the query.

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Server, SQL Union Function and tagged . Bookmark the permalink.

31 Responses to Sql Server – Union and Union All

  1. Anonymous says:
    June 11, 2010 at 11:47 am

    Hi Vivek,

    I read this article and try second difference i.e. 'Union All can be used to insert multiple rows in the table in a single query. You can see the use of the Union All in the insert statement, which we have used to insert the data into the table tbl_Manager.We can’t use Union for this purpose.'

    I'm able to insert using both UNION and UNION ALL.

    What I try is:
    INSERT INTO MyTable (FirstCol, SecondCol)
    SELECT 'First' ,1
    UNION ALL
    SELECT 'Second' ,2

    and

    INSERT INTO MyTable (FirstCol, SecondCol)
    SELECT 'First' ,3
    UNION
    SELECT 'Second' ,4

    with both as Primary key constraints and no constraint on SecondCol.

    Please rectify me where I'm wrong.

    Thnx in advance
    Tulika

    Reply
  2. Vivek Johari says:
    June 12, 2010 at 2:57 pm

    Hi Tulika,
    Thanks for your valuable comments. We can use Union as well as Union All in the insert statement but we can not use Union in case, when we required to insert duplicate rows in the table. For Example if we write the following Insert statement

    INSERT INTO MyTable (FirstCol, SecondCol)
    SELECT 'First' ,3
    UNION
    SELECT 'Second' ,4
    UNION
    SELECT 'Second' ,4

    It will only insert 2 rows instead of 3 rows. But if we use Union all in place of Union, it will insert all the three rows.

    Also the use of Union will increase the cost of execution plan since it have to perform two functions Select and then Distinct function. Therefore use of Union all is preferred over Union in select statement.

    I will also rectified this point in my above article.

    Reply
  3. Choudhary nafees ahmed says:
    August 2, 2013 at 7:47 pm

    Thanks vivek for explaining in such a descriptive manner….
    Choudhary Nafees

    Reply

  4. Pingback: pasang togel dengan benar

  5. Pingback: Bess Pets

  6. Pingback: รักษาหลุมสิว

  7. Pingback: best real estate agent websites

  8. Pingback: เลเซอร์ขน

  9. Pingback: แทงบอลออนไลน์

  10. Pingback: Hot Wife Captions

  11. Pingback: nsfwgif

  12. Pingback: online Bhojpuri movie

  13. Pingback: dark circle remover

  14. Pingback: w888

  15. Pingback: 안전토토사이트

  16. Pingback: www.w88th

  17. Pingback: fun 88

  18. Pingback: Bassett Caterers

  19. Pingback: croc shoes store locator

  20. Pingback: สมัครUFABET

  21. Pingback: led display

  22. Pingback: corn hole game

  23. Pingback: Empire Market

  24. Pingback: กังนัม คลินิก

  25. Pingback: Apollon Market

  26. Pingback: target marketing analysis

  27. Pingback: Global (AMER, EMEA, BRICS and APAC) Wallpaper Remove Device and Removers Market Size, Status and Forecast 2019-2025

  28. Pingback: Slager hengelo

  29. Pingback: seniqq

  30. Pingback: sex

  31. Pingback: jaguar qq

Leave a Reply