Symmetric Except

  • Comments posted to this topic are about the item Symmetric Except

    Igor Micev,
    My blog: www.igormicev.com

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

  • 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 ... πŸ™‚

  • 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... πŸ™‚

  • 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.

  • Fun question, thanks!


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

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

  • 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

  • I like this question. Thanks.

  • 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.

  • 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

  • '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.

    ----------------------------------------------------

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

  • 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.

  • 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