Symmetric Except

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Comments posted to this topic are about the item Symmetric Except

    Igor Micev,
    My blog: www.igormicev.com

  • gvoshol 73146

    Hall of Fame

    Points: 3187

    I'm not quite sure how the correct answer will produce the line with dashes   πŸ˜‰

  • Budd

    Hall of Fame

    Points: 3769

    Very interesting and I think this would be the same as 
    (select a from tbl1 WHERE a NOT IN (select a from tbl2)) union (select a from tbl2 WHERE a NOT IN (select a from tbl1))
    BUT, how often do you run into selecting 1 column of data from 2 or more tables with no Primary key to link ?
    Can anyone provide a bigger picture of how this could be used in corporate data structure ?
    ---Always learning, and never as smart as I used to believe I was ... πŸ™‚

  • George Vobr

    SSCrazy Eights

    Points: 9215

    Thanks for an interesting question Igor. I had the biggest problem with an estimate of the result the answer No. 4, where there were no brackets but I did it... πŸ™‚

  • Kaye Cahs

    SSCarpal Tunnel

    Points: 4135

    gvoshol 73146 - Thursday, March 29, 2018 6:42 AM

    I'm not quite sure how the correct answer will produce the line with dashes   πŸ˜‰

    Run the query in SSMS with the "Results to Text (Ctrl + T)" mode turned on.

  • david.gugg

    SSCertifiable

    Points: 5692

    Fun question, thanks!


    [font="Tahoma"]Personal blog relating fishing to database administration:[/font]

    [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

  • shellerman

    SSC Veteran

    Points: 298

    Another way:
    select coalesce ( tbl1.a, tbl2.a ) as a
    from tbl1 full outer join tbl2 on tbl1.a = tbl2.a
    where tbl1.a is null or tbl2.a is null

  • Aleksl-294755

    Hall of Fame

    Points: 3514

    I like this question. Thanks.

  • t.ovod-everett

    Ten Centuries

    Points: 1389

    The advantages of this syntax are that it lets you compare two output streams to identify rows that differ, and that it treats NULLs as being equivalent.  EXCEPT and INTERSECT are really useful when you need NULL to equal NULL, and as Paul White has discussed, they use a special SQL Plan Operator to perform better than alternative approaches.

    For comparing query output, before and after snapshots of tables, etc., I typically use the following snippet:
    WITH
    DSO AS (
     SELECT *
     FROM #Old
    ),
    DSN AS (
     SELECT *
     FROM #New
    )
    SELECT 'Old' AS Tbl, * FROM (SELECT * FROM DSO EXCEPT SELECT * FROM DSN) AS U1
    UNION ALL
    SELECT 'New' AS Tbl, * FROM (SELECT * FROM DSN EXCEPT SELECT * FROM DSO) AS U2
    ORDER BY Tbl DESC;

    The reason I use the DSO and DSN CTEs is to isolate the "diff" portion of the query from the portion that identifies the data sets being diffed.  This way I only need to replace #Old and #New in one place each.  It also lets me replace the * in the DSO and DSN CTEs with an explicit column list if I want to exclude specific columns from the comparison.  I can further customize it by modifying the ORDER BY clause at the end - I will frequently stick a candidate key at the beginning.  This can be useful for regression testing - dump the output from two different versions of a query into temp tables, then compare the output.

  • webrunner

    SSC-Dedicated

    Points: 30300

    Thanks for the question! I'd be curious to know Gail Shaw's or Jeff Moden's take on this kind of query.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • MMartin1

    One Orange Chip

    Points: 27502

    'a' is the column name here and the dashes is the separator between the column name and values returned for this confused me at first glance.

    ----------------------------------------------------
    How to post forum questions to get the best help [/url]

  • diego.perdices

    SSCommitted

    Points: 1701

    Nice question.
    Just a tip, remember to use union all instead of union when there is no need to remove duplicates.

  • t.ovod-everett

    Ten Centuries

    Points: 1389

    diego.perdices - Monday, April 2, 2018 12:37 AM

    Nice question.
    Just a tip, remember to use union all instead of union when there is no need to remove duplicates.

    Good catch!  I added an ALL to my generic table diff query.

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71852

    Nice one, thanks Igor

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    β€œlibera tute vulgaris ex”

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

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