none
Union clause RRS feed

  • Question

  • For  the query

    SELECT 2   UNION SELECT 1

    The result is displayed as

    2

    1

    But for the query

    SELECT 3 UNION SELECT 2   UNION SELECT 1

    The result is displayed as(in sorted order)

    1

    2

    3

    Why is it so? I have tried to search but not able to find any thing.

    Wednesday, June 14, 2017 4:03 AM

All replies

  • I don't know the exact science of it, but if you want it to return in order of query entry (ie 3 then 2, then 1) try using a UNION ALL.  A UNION will sort the full result set as part of it's process of removing duplicate records. A UNION ALL does not remove duplicates, but essentially appends results to the prior result set.

    Wednesday, June 14, 2017 4:12 AM
  • The below link also underlines the same point as  DeviantLogic metnioned:

    "In the same way, use UNION ALL rather than UNION unless you need to eliminate duplicates. UNION returns rows from each query included in the statement, puts them in a work table, and then sorts them to remove duplicates. UNION ALL skips the last step."

    http://www.informit.com/articles/article.aspx?p=27015&seqNum=8

    • Edited by SQLNeophyte Wednesday, June 14, 2017 5:08 AM
    Wednesday, June 14, 2017 5:08 AM
  • Another proof id you want get back the sorted data use ORDER BY clause

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, June 14, 2017 5:15 AM
    Answerer
  • Hi puneet,

    Maybe you can do some little tricks when using union. Please refer:

    CREATE TABLE #t1 (ID INT, Col1 VARCHAR(100));
    CREATE TABLE #t2 (ID INT, Col1 VARCHAR(100));
    GO
    -- Sample Data Build
    INSERT INTO #t1 (ID, Col1)
    SELECT 1, 'Col1-t1'
    UNION ALL
    SELECT 2, 'Col2-t1'
    UNION ALL
    SELECT 3, 'Col3-t1';
    INSERT INTO #t2 (ID, Col1)
    SELECT 3, 'Col1-t2'
    UNION ALL
    SELECT 2, 'Col2-t2'
    UNION ALL
    SELECT 1, 'Col3-t2';
    
    SELECT ID, Col1
    FROM #t1
    UNION
    SELECT ID, Col1
    FROM #t2
    
    --Add a dummy ordinal column
    
    SELECT 1,ID, Col1
    FROM #t1
    UNION
    SELECT 2,ID, Col1
    FROM #t2

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 14, 2017 5:37 AM
  • If you check the execution plan, you will see when you have more than on union operation, you will see Merge Join (Union) operation , which will sort your data.
    Wednesday, June 14, 2017 1:45 PM
    Moderator