SQL SERVER – Union vs. Union All – Which is better for performance?

This article is completely re-written with better example SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison. I suggest all of my readers to go here for update article.

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

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.

Example:
Table 1 : First,Second,Third,Fourth,Fifth
Table 2 : First,Second,Fifth,Sixth

Result Set:
UNION: First,Second,Third,Fourth,Fifth,Sixth (This will remove duplicate values)
UNION ALL: First,First,Second,Second,Third,Fourth,Fifth,Fifth,Sixth,Sixth (This will repeat values)

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

188 thoughts on “SQL SERVER – Union vs. Union All – Which is better for performance?

  1. Union command does 2 things
    1) It works like distinct clause ie it removes duplicate rows also it shows the rows which are unique in a particular table.
    eg if Table A has rows with values 1,2,3 and Table B has rows with values 4,2,3
    the Union table A and table B would give result
    1,2,3,4

    • Hi,

      I need some more clarification on this, Table A has 1,2,3,4 and table B has 4,2,3 and it will display 1,2,3,4. ok up to this fine.

      My question is:

      2,3 are available in both tables , can you please tell me which table’s data will be the 2 and 3 whether it is from table A or from table B.

      please explain, I gave all the answers above , again the interviewer twisted with this, kindly give a example

      • this is nice twist. but i guess, it always retain the first table values in the result set as per your question, its table ‘A’ s.

  2. this is really a good article to understand but can u give this example with a example. This will be the good for all the people to understand the concept very clearly.

    Thanks………..

    • Hi Pinal,

      If you have two tables.
      First table : 1,2,3,4,5
      Second table : 1,2,5,6

      for Union All:
      1,1,2,2,3,4,5,5,6

      but you said 1,2,2,3,4,5,5,6

      ???

    • thanks for your help
      but i have a large amount of data and i use union all , i need to improve its performance .
      thanks

      • DECLARE @tab1 AS TABLE(id INT)
        DECLARE @tab2 AS TABLE(id INT)

        INSERT INTO @tab1
        VALUES(1),(2),(3),(4),(5)

        INSERT INTO @tab2
        VALUES(1),(2),(5)

        SELECT * FROM @tab1
        EXCEPT
        SELECT * FROM @tab2

      • Use an except clause, (Minus if you’re using an Oracle Database)

        Select *
        from table a
        except (minus in Oracle)
        Select *
        from table B

        (This will show the results in table A that aren’t in table B)

        If you want the result 1,2,5 use intersect

        Select *
        from table a
        intersect
        Select *
        from table B

        This will show only the results in table a that appear in table B

  3. Another example, which this article helped me troubleshoot today:

    First Table: 1,2,2,3
    Second Table: 4,5

    Union All:
    1,2,2,3,4,5

    Union:
    1,2,3,4,5

    I suppose I understood that union would remove duplicates between the two tables, but I didn’t realize that it would also remove the EXISTING duplicates (which I wanted to keep!).

    • declare @t1 table(i int)
      insert into @t1 select 1 union all select 2 union all select 2 union all select 3
      declare @t2 table(i int)
      insert into @t2 select 3 union select 1 union select 4 union select 5
      select * from @t1 as t

      where exists(
      select i from @t1
      where i=t.i
      group by i having count(*)>1
      )

      union all
      (
      select * from @t1 as t
      where exists(
      select i from @t1
      where i=t.i
      group by i having count(*)=1
      )
      union
      select i from @t2
      )

    • Hello sir,
      If i have a 1000 records in a pdf or word or notepad format. All my 1000 records having just 3 fields like eno,ename,esalary.
      I also created table with same field in SQL 2005. By using only one single query i should insert all my 1000 records into the SQL.
      Please send me the query for that.

  4. Good explanation….

    I have got one problem… I would appreciate if any one help me out solve the problem…

    Here is my requirement. I have to write a store procedure which returns a table like below.

    create procedure temp
    AS
    BEGIN

    if(condition1)
    BEGIN
    sql statement1
    END

    if(condition2)
    BEGIN
    sql statement2
    END

    END

    Now i have to combined these two tables and have to return. How can i use UNION or UNION all here in this case???

    I hope the requirement is clear…

  5. Hi Suresh,

    For your example of having to effectively union two statements that are nested in different IF statements, you may want to use a temp table / table variable.

    if(condition1)
    BEGIN
    INSERT INTO #MyTable
    SELECT * FROM Table1
    END

    if(condition2)
    INSERT INTO #MyTable
    SELECT * FROM Table2
    END

    SELECT * FROM #MyTable

    In the above example, you would need to explicitly decide if you wanted to do a simple SELECT (UNION ALL) or SELECT DISTINCT (UNION).

    Also, you should carefully choose to use a temp table (#tmp) or table variable (@tmp). There are a number of differences, but basically, variable are better for small resultsets and temp tables better for large. When using a variable table, you lose all parallelism, so inserting into the table can take significantly longer.

  6. I would like to work with u….

    If u can recognize me i am vaibhav pathak…….

    I like ur article for difference beteen Union and Union All

    You have selected me but the boss of ur company had no faith on me thats why i did not come to ur company on 15th jan 2005………

  7. I would like to work with u…. If u allow !!!!!!!!

    If u can recognize me i am vaibhav pathak……. I have acd interview taken by u.

    I like ur article for difference beteen Union and Union All

    You have selected me but the boss of ur company had no faith on me thats why i did not come to ur company on 15th jan 2008………

  8. Pingback: SQL SERVER - Simple Puzzle Using Union and Union All Journey to SQL Authority with Pinal Dave

  9. Hello,

    I have a challange here Im hoping someone could help… I got two tables with identical schema that need to be “glued” together, but the duplicate records must be truncated under certain condiction:

    For example, if the PK field is identical in both tables, I would want the actDate to be evaluated and the one with higher value to be selected during the UNION process… how can I accomplish this?

    Thanks a whole bunch in advance!!

  10. hi Pinal
    Good article
    i have a doubt
    what if if the table contains a Null value
    can u give example in this regard

    With thanks
    sree

  11. If I have use a UNION ALL with two selects why does the order change instead of returning the results in natural order? For example if I have SELECT 0 As SortOrder FROM XXX UNION ALL SELECT 1 As SortOrder FROM AAA the 1 will be the first record instead of the second (some times)? If I add a Sort Order to the entire SQL Statement then my query slows down to much since it is buried in a bunch of other queries.

  12. Hi Pinal,

    The article of Union v/s Union ALL was fantastic, it clears the doubts of difference between UNION AND UNION ALL.

    My only qus is : Does Union ALL also shows repeated columns (duplicates) where the datatype is common, which is the case in UNION clause.

  13. Hi Pinal,

    I am a beginer in SQL and have a query and finding difficult solving this. Can you please help me.

    [Northwind] List the CompanyName, ContactName, City, Region, PostalCode, Country, and Phone of all Customers who have ordered product ‘Tofu’ and of the Supplier of that product. Use UNION. Show all output rows.

    Thanks
    Zainab

  14. pinaldave,

    Is the any difference in UNION and UNION ALL sorting order?
    As in case of UNION SQL Server build temporay indexes on all the columns.

    Thanks
    Muhammad Wasim

  15. hi ,
    its reakky a good article . well i wana know how can i import data from excel sheet into 2005 database.pls do reply

    thanks
    amit sharma

  16. Hi amit,

    you can import the Excel Sheet data using the Sql Server Import and Export Command. Below is the steps for the same:
    1. Right click on the Database Select Tasks-> Import Data…
    2. Select the datasource as ‘Microfost Excel’
    3. Browse the location of the Excel File.
    4. if you want to Import into the already existing table then you can Map the columns otherwise you can import into the new tables too.

    Its is very simple wizard.

    Please do let me know if you still face any issue.

    Rupesh

  17. hi Rupesh,

    thanks for urs reply, well rupesh i am still getting error while importing the excel file.

    error:
    Error 0xc00470fe: Data Flow Task: The product level is insufficient for component “Source – Assessment$” (1).
    (SQL Server Import and Export Wizard)

    may i know wht is this error and what i have to do for resolving this error

    thanks
    amit sharma

  18. hello Sir,
    i have an problem with the union operator

    i write a qry1

    Select tm.DBID,max(tm.Token) as token,max(tm.Symbol) as symbol,max(tm.Series) as series,max(tm.ExpiryDt)
    as expirydt, max(tm.StrikePrice) as StrikePrice,
    (tm.timeslot) as PutCall,sum(tc.FillQuantity) as BuyQty,
    sum(tc.FillQuantity * tc.FillPrice)/100 as BuyValue, 0 as SellQty,0 as SellValue from Trade_Confirm tc
    left join Orders o on tc.ResponseOrderNumber=o.OrderNumber left join tokenmast tm on o.DBID=tm.DBID
    Where tc.BuySellIndicator = 1 and o.Userid=1 and o.StrategyCode=10 group by tm.DBID,tm.timeslot

    and write a 2nd qry

    Select tm.DBID,max(tm.Token) as token,max(tm.Symbol) as symbol,max(tm.Series) as series,max(tm.ExpiryDt) as expirydt,
    0 as BuyQty,0 as BuyValue, max(tm.StrikePrice) as StrikePrice,tm.timeslot as PutCall,sum(tc.FillQuantity) as SellQty,
    sum(tc.FillQuantity * tc.FillPrice)/100 as SellValue from Trade_Confirm tc
    left join Orders o on tc.ResponseOrderNumber=o.OrderNumber left join tokenmast tm on o.DBID=tm.DBID
    Where tc.BuySellIndicator = 2 and o.Userid=1 and o.StrategyCode=10 group by tm.DBID,tm.timeslot

    i got result by individual run but when i join union then get a error this:

    Error:Warning: Null value is eliminated by an aggregate or other SET operation.
    Server: Msg 8114, Level 16, State 5, Line 1
    Error converting data type varchar to numeric.

    if you have any solution then please send me in given mail id manishpandey2009@gmail.com

  19. Hi,

    Can this be done

    select col1,col2,col3…
    from
    {
    Qry 1
    union
    qry 2
    union
    qry 3
    }
    order by col2,col5….

    Thanks in advance.

  20. Very much helped.Thanks.

    The way it helped me is like this :

    Since I want to get the stock of a given item , I had to use
    UNION between tables like Purchase , Sales , Sales Return..

    In case , which happens only rarely , if any 2 rows from different tables have duplicate values , UNION will remove 1 row , that would affect the stock.

    In that case UNION ALL is the solution.

  21. Input SQL query: select ‘1’ union select null union select 5

    Output:
    NULL
    1
    5

    Any idea about the data type of ‘1’, because if I ‘ll give ‘a’, it throws error.

    Also, why NULL comes first? What is the sort order? Even I am not able to put a column alias to imply the sort order.

  22. Dear Arup,

    I did not understand your question properly, you did not give complete information as how you want your output to be, good if you would have given an example.

    Based on what I understood, this is what I would try,

    Query1:

    select 1 as Firstcolumn , null Secondcolumn , 5 ThirdColumn

    Output:

    FirstColumn SecondColumn ThirdColumn
    1 NULL 5

    Query2:
    select 1 as OneColumn union all select null union all select 5

    Output:

    OneColumn
    1
    NULL
    5

    Hope this helps.
    Imran.

  23. Hi Imran,

    Many thanks for the reply.

    Actually in my sql query i.e. select ‘1′ union select null union select 5, the out put is :

    NULL
    1
    5

    So the first select returns 1, then the second select returns ‘NULL’ and third select returns 5. Since I have not used any order by clause, hence the output may be:

    1
    NULL
    5

    I have seen that u have solved the issue by using a columnname in your Query 2, then how is it happening?

    My another question is, in this output column, the data types are different, like ‘1’ is char and 5 is int. How is it possible?

  24. Pingback: SQL SERVER - Simple Puzzle Using Union and Union All - Answer Journey to SQL Authority with Pinal Dave

  25. i have to table exalple a1 and a2

    a1 table return no rows
    a2 table return 10 rows

    when i union two table it returns 9 row why?
    but i union all two table it returns 10 row. i would not understand what functioning going on.

    please if u have any idea share with me

  26. Pingback: SQL SERVER - Introduction and Example of UNION and UNION ALL Journey to SQL Authority with Pinal Dave

  27. Hello Pinal ,

    Can you provide me more difference between them lik 3 or 4 more difference bcoz in one interview i was asked to provide 5 differences between them.

    Thanks
    Dharm

  28. I came across your site the other day and i found it really useful. I am new to the world of sql and was bit confused about different join concepts. I hope you would post something regarding joins real soon or recommend some useful website.

    Thanks!
    KS.

  29. Hi,

    I beg you pardon, but I’ve got a similar problems on reporting services.
    I usually write code using small queries collected into a union structure, such as

    Select Something
    Union
    Select Something Else

    Because of the necessity to display data in a proper order.

    Now I’m in trouble because I have to use a condition statement like

    IF First condition Select Statement 1.0
    Else
    Select Statement 2.0
    Union
    IF First condition Select Statement 1.1
    Else
    Select Statement 2.1

    and so on

    Each “Nucleo” If Else Select has the same structure.

    Do you have some tips ti workaround or go throw this topics.

    Finally, “Sono molto sorpreso dalla tua provessionalità”…I’m Very surprise of your professional exprerience and skills.

    Ciao
    massimiliNO

  30. Pingback: SQL SERVER - Three Rules to Use UNION Journey to SQL Authority with Pinal Dave

  31. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  32. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 8 Journey to SQL Authority with Pinal Dave

  33. What is the difference in processing speed of these two queries? Is one actually faster than the other???

    ————————————————

    SELECT COL1, COL2, COL3 FROM TBL1
    UNION
    SELECT COL1, COL2, COL3 FROM TBL2

    – — versus ———-

    (notice using DISTINCT with UNION ALL)
    SELECT DISTINCT COL1, COL2, COL3 FROM TBL1
    UNION ALL
    SELECT DISTINCT COL1, COL2, COL3 FROM TBL2

    ——————————————————

    Note: TBL1 and TBL2 are very large and both have quite a few duplicates within each table… but each table TBL1 and TBL2 will not have anything in common. Creating a 3rd table is out of the question.

    Thanks for your timely response.
    Jim

  34. Pingback: SQL SERVER - Difference Between Union vs. Union All - Optimal Performance Comparison Journey to SQL Authority with Pinal Dave

  35. I tried using the UNION ALL to combine the result of two separate queries where the columns / fields are of the same values.

    The question is : Can I use th UNION all for more than two select statements wherethe columns / fields are of the same values.

    Thank you

  36. Hi – thanks for this – very useful and concise – I haven’t come across a better comparisson that’s been this easy to follow

  37. Hello!!
    I have used union all for two tables and worked very well. Now how I can do it for 5 or more. I am new. Please help me.

  38. @Adres

    What is the problem?

    SELECT … FROM T1
    UNION ALL
    SELECT … FROM T2
    UNION ALL
    SELECT … FROM T3
    UNION ALL
    SELECT … FROM T4
    UNION ALL
    SELECT … FROM T5

  39. Hi Pinal,

    I have 2 columns Project date and trench. I want to display the date as range if the trench value is same. For eg:
    Project date have three values 10-12-2009,11-12-2009,12-12-2009 and all the three have same trench value 1.

    Now my requirement is i need to show them as
    10-12 Dec 2009.

    I am trying with Union so far not successful.

    Can someone tell me how to achieve this.

  40. @Ranjani

    To get the range, use a GROUP BY with MIN() and MAX()

    The date would be interesting, because it may be multiple months or even years. Given the example provided (the three records) this should work:

    WITH
    Data([Project date], trench)
    AS
    (
    SELECT ’10-12-2009′, 1 UNION ALL
    SELECT ’11-12-2009′, 1 UNION ALL
    SELECT ’12-12-2009′, 1
    )
    SELECT
    CAST(DAY(MIN([Project date])) AS VARCHAR(2))
    + ‘-‘
    + CAST(DAY(MAX([Project date])) AS VARCHAR(2))
    + ‘ ‘
    + LEFT(DATENAME(m, MAX([Project date])), 3)
    + ‘ ‘
    + CAST(YEAR(MAX([Project date])) AS VARCHAR(4))
    FROM
    Data
    GROUP BY
    trench;

    Note the date format will depend on what it is set to. For the US, all three dates are the 12th, but the month changes. Which is not what you intended.

  41. Hi all,

    Is it possible to use if …else condition with union all… its throwing an exception Msg 156, Level 15, State 1, Line 41
    Incorrect syntax near the keyword ‘if’.

    Sample query
    if(@1stHearingdate is not NULL)
    BEGIN
    Select 1 as ‘Week No’, convert(varchar(100),Hrgdatetime,101) as ‘HearingDate’, count(Hrgid) ‘No of hearing’,’Remarks’
    from
    Hearing
    where convert(varchar(100),Hrgdatetime,101) = convert(varchar(100),@1stHearingdate,101)
    group by convert(varchar(100),Hrgdatetime,101)
    END
    else
    Select 1 as ‘Week No’,convert(varchar(100),@1stHearingdate,101),0,’Remarks’

    union all

    if(@2ndHearingdate is not NULL)
    BEGIN
    Select 1 as ‘Week No’, convert(varchar(100),Hrgdatetime,101) as ‘HearingDate’, count(Hrgid) ‘No of hearing’,’Remarks’
    from
    Hearing
    where convert(varchar(100),Hrgdatetime,101) = convert(varchar(100),@2ndHearingdate,101)
    group by convert(varchar(100),Hrgdatetime,101)
    END
    else
    Select 1 as ‘Week No’,convert(varchar(100),@2ndHearingdate,101),0,’Remarks’

  42. Hi,

    Best answer thanx a lot man.

    Regards,

    Mr. Rajesh Mhaisne
    Software Engineer | Pune

    The word “IMPOSSIBLE” Says ‘I M’ “POSSIBLE”.

  43. Hi!
    i am using UNION in my program where i want that union shoud not auto arrange the order since its default order is imp for me.

    ex table1
    area 1.5
    table 2
    area 1.2
    table 3
    area 2.3

    after union of these result comes like

    area 1.2
    area 1.5
    area 2.3

    i want i should come like

    area 1.5
    area 1.2
    area 2.3

    what should exact querry.
    pls help me.

  44. Can we use Where condtion after Union or Union all statements, Like as below. Coz i need to query some thin like below. PLease help me in this.

    create table table1
    (
    id1 int,
    name1 varchar(50)
    )
    go
    insert into table1 values(1,’venu’)
    insert into table1 values(2,’gopal’)
    insert into table1 values(3,’hari’)
    insert into table1 values(4,’prasad’)
    insert into table1 values(5,’balu’)
    go
    create table table2
    (
    id2 int,
    name2 varchar(50)
    )
    go
    insert into table1 values(1,’Gopal’)
    insert into table1 values(20,’fadfl’)
    insert into table1 values(3,’Prasad’)
    insert into table1 values(40,’asdfasd’)
    insert into table1 values(50,’dfsd’)

    create view myview
    as
    select name1 from table1
    union all
    select name2 from table2
    where table1.id1 = table2.id2

  45. hi!

    pls can you show me how to query in two table that are not match with each other?

    sampl data:

    table 1—–1,2,4,6,7

    table 2—–2,3,4,5,8

    will return—-1,3,5,6,7,8

    pls..pls..pls….

  46. Hi,

    Please find solution for your query:

    DECLARE @T1 TABLE(i INT)
    INSERT INTO @T1 VALUES(1)
    INSERT INTO @T1 VALUES(2)
    INSERT INTO @T1 VALUES(4)
    INSERT INTO @T1 VALUES(6)
    INSERT INTO @T1 VALUES(7)

    DECLARE @T2 TABLE(j INT)
    INSERT INTO @T2 VALUES(2)
    INSERT INTO @T2 VALUES(3)
    INSERT INTO @T2 VALUES(4)
    INSERT INTO @T2 VALUES(5)
    INSERT INTO @T2 VALUES(8)

    ;with CTE AS(
    SELECT i
    FROM @T1
    UNION
    SELECT j AS i
    FROM @T2
    )
    SELECT *
    FROM Cte
    WHERE NOT EXISTS(
    SELECT *
    FROM @T1 t1
    INNER JOIN @T2 t2 ON i=j
    WHERE t1.i= CTE.i
    )
    ORDER BY i

    Let me know if you any questions.

    Thanks,

    Tejas

    • we can also do this with

      select distinct * from @T1 where i not in (select distinct * from @T2 ) union
      select distinct * from @T2 where j not in (select distinct * from @T1 ) order by 1 desc

    • select * from @T1 where i not in (select distinct * from @T2 ) union
      select * from @T2 where j not in (select distinct * from @T1 )

  47. ID Username ModifiedDate

    1 A.BEENA 2009-12-07 04:48:17.980
    2 A.BEENA 2009-11-17 06:02:27.443
    3 Abhilash 2009-12-07 04:48:17.980
    4 abhilash.r 2009-12-07 04:48:17.980
    5 AJI 2009-11-17 06:02:27.443
    6 haris 2009-12-07 04:48:17.980
    7 haris 2009-11-17 06:02:27.443

    I want to select details of all distinct users order by ModifiedDate.

    I need output like this

    A.BEENA 2009-12-07 04:48:17.980
    Abhilash 2009-12-07 04:48:17.980
    abhilash.r 2009-12-07 04:48:17.980
    AJI 2009-11-17 06:02:27.443
    haris 2009-12-07 04:48:17.980

    Please help me

    Thanks,

    Aneesh

    • @Aneesh Daniel

      How’s this?

      WITH
      Data(ID, Username, ModifiedDate)
      AS
      (
      SELECT 1, ‘A.BEENA’, ‘2009-12-07 04:48:17.980′ UNION ALL
      SELECT 2, ‘A.BEENA’, ‘2009-11-17 06:02:27.443′ UNION ALL
      SELECT 3, ‘Abhilash’, ‘2009-12-07 04:48:17.980′ UNION ALL
      SELECT 4, ‘abhilash.r’, ‘2009-12-07 04:48:17.980′ UNION ALL
      SELECT 5, ‘AJI’, ‘2009-11-17 06:02:27.443′ UNION ALL
      SELECT 6, ‘haris’, ‘2009-12-07 04:48:17.980′ UNION ALL
      SELECT 7, ‘haris’, ‘2009-11-17 06:02:27.443′
      )
      SELECT
      Username,
      MAX(ModifiedDate)
      FROM
      Data
      GROUP BY
      Username;

  48. I got two tables with different number of fields.. is there any way I can ‘append’ the second table to the first table

    for example:

    table1 has the following records:
    abc def ghi
    jkl mn op

    table2 has
    1 2
    3 4

    I want my new table to look like this:

    abc def ghi
    jkl mn op
    1 2
    3 4

    please help!
    thanks

  49. Hi Mike,

    I have tricky solution. You can do this by this way:

    DECLARE @Table1 TABLE(A VARCHAR(10), B VARCHAR(10), C VARCHAR(10))
    INSERT INTO @Table1(A, B, C)
    SELECT ‘abc’, ‘def’, ‘ghi’
    UNION
    SELECT ‘jkl’, ‘mn’, ‘op’

    DECLARE @Table2 TABLE(A INT, B INT)
    INSERT INTO @Table2(A, B)
    SELECT 1, 2
    UNION
    SELECT 3, 4

    SELECT A,
    B,
    C
    FROM @Table1
    UNION ALL
    SELECT CAST(A AS VARCHAR),
    CAST(B AS VARCHAR),
    ” AS C
    FROM @Table2

    What I did is, Added blank column on Table2, so I can use UNION ALL.

    Let us know if it helps you.

    Thanks,

    Tejas
    SQLYoga.com

  50. Hello,

    Thanks for the reply. Is there any way I can do it dynamic. Meaning, the number of columns in both tables can differ.

    So there are 3 possbilities:
    1. TableA has the same number of columns as tableB
    2. TableA has more columns than tableb
    3. TableA has less columns as tableB

    Thanks in advance

  51. Hi everybody,

    My question is : Is there any alternate for UNION ALL..

    i.e.. I want to fetch a record from one table and one more record from other table without using the UNION ALL.

    I hope you got my query..

    Plzz help me out..

    • @praveen goud

      UNION ALL is probably best. However, if both TABLEs will only return one record, and if you want that record on the same line, you can just join them:

      WITH
      A(A) AS (SELECT 1 UNION ALL SELECT 2),
      B(B) AS (SELECT 1 UNION ALL SELECT 2)
      SELECT A.A, B.B FROM A, B WHERE A.A = 1 AND B.B = 2;

      Note, this only works when both will return only one record.

  52. Hi..
    Thanx for the reply..

    I need to fetch only one record from the first table and more than a record from the second one..

    Is there any other way to acheive this without using UNION ALL ..

    kindly suggest ..

  53. Hi Pinal Dave

    I am trying use UNION ALL in Stored Procedure but i am not getting result what is the problem please explain. My code is

    create procedure Usp_UserCompanyPersonalAddresses
    (
    @UserAddressId nvarchar(255),
    @UserCompanyAddressId nvarchar(255)
    )
    as
    begin
    select u_address_name as AddressName,
    (u_first_name+u_last_name) as [Name],
    u_address_line1 as Address1,
    u_address_line2 as Address2,
    u_city as City,
    u_region_code as [State],
    u_country_name as Country,
    u_postal_code as PostalCode,
    u_tel_number as TelephoneNumber,
    u_tel_extension as TeleExtention
    from addresses where u_address_id in(@UserAddressId)

    union all

    select u_Companyaddress_name as AddressName,
    u_Companyaddress_name as [Name],
    u_address_line1 as Address1,
    u_address_line2 as Address2,
    u_city as City,
    u_region_name as [State],
    u_country_name as Country,
    u_postal_code as PostalCode,
    u_tel_number as TelephoneNumber,
    u_tel_extension as TeleExtention
    from companyaddresses
    where u_Companyaddress_id in(@UserCompanyAddressId)
    end

  54. This is also one problem i am passing the Parameter from Front end(.Net). i am not getting records. is There any restriction on WHERE IN While using it in Stored Procedure?
    if I write the Inline Query it is working fine

    CREATE procedure Usp_UserPersonalAddresses
    (
    @UserAddressId nvarchar(max)
    )
    as
    begin
    select
    u_address_id as AddressId,
    u_address_name as AddressName,
    u_first_name as [Name],
    u_address_line1 as Address1,
    u_address_line2 as Address2,
    u_city as City,
    u_region_code as [State],
    u_country_name as Country,
    u_postal_code as PostalCode,
    u_tel_number as TelephoneNumber,
    u_tel_extension as TeleExtention
    from addresses where u_address_id in(@UserAddressId)
    end

      • Hi Madhivanan

        Ya i am passing with comma separated value
        My Variable is :
        string strValue=”‘{xxx}’,'{yyy}’,'{zzz}’”;
        sqlCmd.paramerers.Add(“@ UserCompanyAddressId”,SqlDataType.NvarChar,255).value=strValue

        Like This i am passing

  55. while using union and unoin all
    wht are the major requirement on both the table
    if column have different data types then , is it possible to use union and union all or in case of join will it work

    plz let me clear it

    thanx

  56. Hi Pinal

    it is very useful websit on MS SQL server,
    i need a clarification from you, while I am going through the properties of relational table you mentioned a word ATOMIC,what is this world specified can you eloberate please

  57. Hi All,

    I need a help that can i join two table which dont have reference between both table.
    Suppose: table 1 having column:
    ID
    Name
    Table 2 having column:
    Phone
    Address

    How can we join these table so that data can show in below form:

    Table:
    ID NAme Phone Address

    Regrds,
    Sanjay

  58. you can do by
    select a.*, b.* from table1 a, table2 b

    but its full join and it will return you the result as

    total number of records in to table1*total numebr of records in table2

    as there is nothing to match

  59. Can anyone explain why when I did a UNION ALL the duplicates were deleted, but when I did UNION the duplicates were still there. I have seen it explained both ways, but more often that UNION ALL retains the duplicates.

  60. I have two table with a lot of record. Now I want get a view
    So
    Case 1:
    Select …
    From table1
    where….
    UNION
    Select…
    From table2
    where…

    Case 2:
    Select …
    From table1
    where….
    UNION ALL
    Select…
    From table2
    where…

    Case3:
    declare @tmpTable table(…)
    Go
    Insert into @tmpTable sSelect … From table1 where….
    Go
    Insert into @tmpTable sSelect … From table2 where….
    Go
    select * from @tmpTable
    drop @tmpTable

    Please help me! Which case is the best performance?

  61. 100 palkalai 30000
    101 raja 5000
    102 kumar 4900
    103 murali 21000
    104 sugumar 13000
    105 gugan 8940
    106 gobi 56000
    107 dhilip 34000

    I have records like this in my notepad or word. how can i insert all those records in SQL. send me the query..

  62. I CAN’T BELIEVE THAT USING “UNION ALL” BOOSTED MY QUERY AT A 500 % FASTER THAN “UNION”
    THANK YOU VERY MUCH BROTHER.

  63. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 9 of 31 Journey to SQLAuthority

  64. Once again you’ve saved the day for me. Mr. Pinal Dave, you’re columns have taught me more about SQL than ten books on the subject. Thank you!

  65. Krishna

    Today i got different scenario where union is working better than union ALL.
    One of the point astonished me while using UNION it is returning results in 20 sec where as it is taking more than 10 min

  66. PBillno PurDate Partyname Tax SBillno SalDate Partyname Tax
    ————————————————————————————————-
    1 2011-09-30 Raji 14 1 2011-09-30 Test 0
    2 2011-09-30 Testing 55.57 4 2011-10-10 Rajesh 1.60
    4 2011-10-10 Kumar 0.00 5 2011-10-10 Guna 29.40
    5 2011-10-10 Suji 0.00 6 2011-09-10 Ragu 0.80
    —————————————————————————————————

    This is My Table …

    i Have PurDate Same to SalDate.. If Not Enough Then that SalDate Record Null Row..

    How do.. Please Help Me..

  67. hello everybody..
    please assist me how to get only duplicate records from a table1 into a new table2..

    thanks in advance sir..please guide me
    Shivendra

  68. hi

    dbo.T_Products

    ID Product name
    1 CAD
    2 PTC
    3 windchill
    4 desk

    dbo.T_Users

    FK_Products Region
    1 Asia
    1 Asia
    1 Europe
    1 America
    2 Asia
    2 Asia
    3 Europe
    3 Europe

    Output:

    Product Name Asia count Europe count America count
    CAD 2 1 1
    PTC 2 0 0
    Windchill 0 2 0

    ID and FK_Products are as primary and foreign key….

    plz give me a query

  69. consider 2 table

    1st table 2nd table
    ———— ——————
    sno rno sno rno
    1 – – 1
    – 2 2 –
    3 – – 3
    – 4 4 –

    output;
    sno rno

    1 1
    2 2
    3 3
    4 4

    how is this possible and what query they are using in sql server 2005?
    any body plz give me the solution

    • select case when sno=’-‘ then rno else sno end as sno,case when rno=’-‘ then sno else rno end as rno from table1
      select case when sno=’-‘ then rno else sno end as sno,case when rno=’-‘ then sno else rno end as rno from table2

  70. Hi All –
    thanks for the explanantion about the UNION and UNION ALL

    can any one help on this

    ———————————————————————
    SELECT ‘My 1 Row123′
    UNION
    SELECT ‘My 2 Row123′
    UNION
    SELECT ‘My 12 Row123′
    UNION
    SELECT ‘My 13 Row123′
    UNION
    SELECT ‘My 122 Row123′
    ———————————————————————
    SELECT ‘My 1 Row123′
    UNION ALL
    SELECT ‘My 2 Row123′
    UNION ALL
    SELECT ‘My 12 Row123′
    UNION ALL
    SELECT ‘My 13 Row123′
    UNION ALL
    SELECT ‘My 122 Row123′
    ——————————————————————–
    when I use UNION, the result set is sorted by the column and not with UNION ALL..
    Could anyone explain the reason for this?

  71. hi sir ,

    i have a problem ,,i have one table field it has asset like(row wise car,computer) and other table field record is (id of these asset like 1,2,3)
    then how to join these tables,, please reply me soon

  72. I am beginer in sql.. i have a problem..i will try combine two queries in sql using UNION..the result is fine…but my problem like
    First table contain 2,5
    second table contain 1,6
    Final Result 1,2,5,6
    but i want 2,5,1,6..
    Anyway to get like that….?
    please help me
    Thanks advance

  73. I am doing a union of 3 complex queries with multiple joins (inner and left outer). Strangely UNION takes 22 sec while UNION ALL takes 24 sec

  74. Hi,
    I would like to union the two tables which are stored at different databases.Suppose the table A is stored in Database A and the table B is stored in Database B.How to write the query to unioj n these tables?
    Can anyone help me?

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

  76. Hi Pinal,
    Can you please explain how
    if that all the records returned are unique from your union, UNION ALL gives faster results?

    Thanks
    Faisal

    • If you use UNION ALL, it does not have to search for duplicates, it just pastes he second table below the first.
      If you are sure there are no duplicates, using UNION is useless and therefor less efficient, since it will be searching for duplicates to delete that are not there. It will find nothing and ‘afterwards’ do exactly what UNION ALL would have done already.

      Kind regards,

      Daan

  77. 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