none
Union vs Union All RRS feed

Answers

  • The choice is simple if you know the answer to the Question "Do i want to eliminate possible duplicate rows when I combine 2 result sets with this operation ?".

    If yes, then use UNION . But be aware that a UNION will take the extra step of scanning the result set and presenting you with ONLY distinct rows. So it is going to perform slower because it has to sort the result set.

    If its guaranteed that your two result sets which you want to combine will not have any overlapping rows, or you don't care if there are duplicate/over lapping rows, then you should use UNION ALL. since UNION ALL does not have to take the extra step of scanning the result sets and extracting only the distinct values, so UNION ALL is usually better performing than UNION, because it does not have to sort the result set.

    And as Uri's reply says , please do real this article on Pinal Dave's blog - http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/ . It had detailed explanation, good examples as well as shows the performance difference by looking at the execution plan for both .

    Hope this helps !


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com



    • Edited by Sanil Mhatre Wednesday, June 13, 2012 4:52 PM
    • Marked as answer by newbisql Thursday, June 14, 2012 1:56 PM
    Wednesday, June 13, 2012 4:50 PM
  • http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/

    Generally UNION ALL is better in terms of performance


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

    • Marked as answer by newbisql Thursday, June 14, 2012 1:56 PM
    Wednesday, June 13, 2012 1:48 PM
    Answerer
  • 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)


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by newbisql Thursday, June 14, 2012 1:56 PM
    Wednesday, June 13, 2012 1:53 PM
  • Check this blog post to know the main difference b/w the two and when to use when: http://sqlwithmanoj.wordpress.com/2010/12/30/why-union-all-is-faster-than-union/

    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011

    • Marked as answer by newbisql Thursday, June 14, 2012 1:56 PM
    Wednesday, June 13, 2012 2:16 PM
  • Union gives only distinct rows and union all gives all rows. Union all has better performance.

    SQL Champ
    Database Consultants NY

    • Marked as answer by newbisql Thursday, June 14, 2012 1:56 PM
    Wednesday, June 13, 2012 2:33 PM
  • UNION statement eliminates duplicate rows, returns common rows in select statements UNION ALL statement includes duplicate rows, returns all rows from select statements,

    UNION All has best performance then UNION.

    • Marked as answer by newbisql Thursday, June 14, 2012 1:56 PM
    Wednesday, June 13, 2012 4:42 PM

All replies

  • http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/

    Generally UNION ALL is better in terms of performance


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

    • Marked as answer by newbisql Thursday, June 14, 2012 1:56 PM
    Wednesday, June 13, 2012 1:48 PM
    Answerer
  • 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)


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by newbisql Thursday, June 14, 2012 1:56 PM
    Wednesday, June 13, 2012 1:53 PM
  • Check this blog post to know the main difference b/w the two and when to use when: http://sqlwithmanoj.wordpress.com/2010/12/30/why-union-all-is-faster-than-union/

    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011

    • Marked as answer by newbisql Thursday, June 14, 2012 1:56 PM
    Wednesday, June 13, 2012 2:16 PM
  • Union gives only distinct rows and union all gives all rows. Union all has better performance.

    SQL Champ
    Database Consultants NY

    • Marked as answer by newbisql Thursday, June 14, 2012 1:56 PM
    Wednesday, June 13, 2012 2:33 PM
  • UNION statement eliminates duplicate rows, returns common rows in select statements UNION ALL statement includes duplicate rows, returns all rows from select statements,

    UNION All has best performance then UNION.

    • Marked as answer by newbisql Thursday, June 14, 2012 1:56 PM
    Wednesday, June 13, 2012 4:42 PM
  • The choice is simple if you know the answer to the Question "Do i want to eliminate possible duplicate rows when I combine 2 result sets with this operation ?".

    If yes, then use UNION . But be aware that a UNION will take the extra step of scanning the result set and presenting you with ONLY distinct rows. So it is going to perform slower because it has to sort the result set.

    If its guaranteed that your two result sets which you want to combine will not have any overlapping rows, or you don't care if there are duplicate/over lapping rows, then you should use UNION ALL. since UNION ALL does not have to take the extra step of scanning the result sets and extracting only the distinct values, so UNION ALL is usually better performing than UNION, because it does not have to sort the result set.

    And as Uri's reply says , please do real this article on Pinal Dave's blog - http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/ . It had detailed explanation, good examples as well as shows the performance difference by looking at the execution plan for both .

    Hope this helps !


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com



    • Edited by Sanil Mhatre Wednesday, June 13, 2012 4:52 PM
    • Marked as answer by newbisql Thursday, June 14, 2012 1:56 PM
    Wednesday, June 13, 2012 4:50 PM