Union

  • Andy Warren

    SSC Guru

    Points: 119676

    Comments posted to this topic are about the item Union

  • Samuel Vella

    SSCrazy Eights

    Points: 8055

    Except and Intersect - Fantastic, something I didn't know had been added for 2005

    I've used (to great effect i might add) the extended UNION (INTERSECT and MINUS) features which have been available in Oracle since 8i and I really missed them when I made the shift to SQL Server 7/2000.

    Good to find out they were added for SQL Server 2005 they are a boon in some cases where a join might not be practicle

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    Nice article!

    I've seen some weirdness mixing unions and union all with unexpected results in the past.

    select ...

    union

    select ....

    union

    select ....

    union all

    select ....

    something to beware of I guess in the future.

  • Samuel Vella

    SSCrazy Eights

    Points: 8055

    If you wanted to use several unions like that then I think it would be wise to wrap sets together.

    if you had 3 tables, each with identicle columns (just different row data)

    Table1, table2 and table3

    and you wanted data from table2 which was not in table1 or table3 then I think the following would be best:

    select *

    from table2

    except

    select *

    from (select *

    from table1

    union

    select *

    from table3) as ExTables

  • Andy Warren

    SSC Guru

    Points: 119676

    Interesting how the one extra sentence at the end added more value than I thought!

    Mixing UNION and UNION ALL - definitely interesting. I think I'd be inclined to separate them with parens to give a good visual signal about what I was trying to achieve and why.

  • bc_

    Hall of Fame

    Points: 3098

    it would be great if you wrote a little article like this one on the topics of EXCEPT and INTERSECT. i was aware of those, but have yet to find a use for them.

    thanks,

    bc

    [font="Arial Narrow"]bc[/font]

  • Andy Warren

    SSC Guru

    Points: 119676

    I will add them to my todo list!

  • Samuel Vella

    SSCrazy Eights

    Points: 8055

    The except and intercept clauses are great if you want to compare 2 data sets but for whatever reason cannot use the primary keys. Or you need to compare 2 tables with many columns to try and find the difference between them when that difference could be in any column.

    You could build a query with joins and then a where clause which compares all the columns. Or you can use the EXCEPT keyword and simply select from both tables.

    For a real world example, imagine you have an ETL procedure. A large part of this is contained within some stored procedures which populate a pre-extraction table which being a flattened version of a normalised database has many many columns.

    You've made a change to the scripts as some of the values weren't being transformed correctly.

    You now need to check that a) the script has made the changes you wanted it to and b) the script isn't inadvertantly making any other changes.

    You can copy the pre-extraction table, make the changes and then run the scripts. You now have two copies of the data, one pre change, the other post change.

    To write the sql query, you can use something like the following to get the column names (saves typing)

    select column_name + ','

    from information_schema.columns

    where table_name = ' '

    order by ordinal_position

    remove any columns you don't want to compare and then build the query:

    select col1, col2, col3...col99

    from postchange_table

    EXCEPT

    select col1, col2, col3...col99

    from prechange_table

    the return should only give the rows you expect to have changed. Any more and you've changed more than you expected, any less or zero and nothing has happened

  • Julie Breutzmann

    Hall of Fame

    Points: 3587

    I was surprised the first time I encountered this feature of UNION. It's good to share this for others who may also be unaware.

    I didn't know about Interest and Except were added in 2005 either. Thanks for a brief and helpful article.

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice article...

  • David McKinney

    SSChampion

    Points: 10358

    I was vaguely aware of this behaviour from UNION, but a perverse idea occurred to me reading this article.

    Why not use UNION instead of DISTINCT or GROUP BY?

    SELECT a, b from tbl

    GROUP BY a,b

    is equivalent to

    SELECT distinct a, b from tbl

    which is equivalent to

    SELECT a, b from tbl

    UNION

    SELECT a, b from tbl

    It gives you distinct combinations of a and b.

    I'm not recommending it to anyone - as it appears to do more work than either of the other two, but it's an interesting idea to do a union and get less results than you started with.

  • Wayne West

    SSC-Insane

    Points: 22586

    Union is such a cool command, I've used it for years and got to introduce our other developers to it early this year. Most recently I used it to produce a file for a mainframe that needed one field formatted differently if it was a positive or negative value, Union made it very easy.

    Definitely look forward to your article on Except and Intercept (nudge, nudge), I love set-based operations!

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Samuel Vella

    SSCrazy Eights

    Points: 8055

    David McKinney (9/11/2008)


    Why not use UNION instead of DISTINCT or GROUP BY?

    I'm not recommending it to anyone - as it appears to do more work than either of the other two, but it's an interesting idea to do a union and get less results than you started with.

    I think you answered your own question 😉

Viewing 13 posts - 1 through 13 (of 13 total)

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