Post reply

Union Queries

  • May 23, 2006 at 9:15 am

    #117730

    I did a few searches and I am not seeing anything saying Union queries are bad.  Are they? 

    I understand that they have to make two selects instead of one but if it is for the same record count total is it ok or even optimized?

    I can not figure out a way to do a query without using either a case statement or a union.  I am leaning toward the union query. 

    This is in SQL 2000

  • May 23, 2006 at 9:48 am

    #639540

    There is nothing wrong with UNION queries. If that's the only way to retrieve the data set required, then use it.

    The primary optimization concern is whether or not the data in each unioned query is mutually exclusive, thereby allowing the use of UNION ALL. Using UNION ALL means the query engine does not ned to perform a DISTINCT on the resultset, and it is this final step of getting distinct rows that often proves to be expensive in UNION queries.

    Try running a query plan on these:

    Select 1 As TestValue

    UNION ALL

    Select 2

    UNION ALL

    Select 3

    versus:

    Select 1 As TestValue

    UNION

    Select 2

    UNION

    Select 3

    ... the merge join/union execution steps in the 2nd version of the query become expensive if the queries return large numbers of rows, versus the simple concatenation step in the 1st version.

  • May 23, 2006 at 10:13 am

    #639544

    Wow that was pretty cool. I never new that. I of course shared this to co-workers and got kudos. Thanks a ton. that helps me in the future in chioces I make

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply