Select unmatched data from two columns

  • chgn01

    Hall of Fame

    Points: 3535

    Comments posted to this topic are about the item Select unmatched data from two columns

    --------------------------------------
    ;-)โ€œEverything has beauty, but not everyone sees it.โ€ โ€• Confucius

  • Ron McCullough

    SSC Guru

    Points: 63877

    Thanks for a good question to start the week.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • bochambers

    Say Hey Kid

    Points: 700

    I've had to deal with this issue a lot, due to incomplete relationships in databases due to poor rules governing primary/foreign key relationships not being enforced. Another alternative is to use NOT EXISTS in a where clauses which from experience can be more efficient, particularly for relationships that aren't over a unique join condition. I hadn't looked at EXCEPT before and found it a useful comment and will see how it performs in the future.

    Thanks.

  • Paul White

    SSC Guru

    Points: 150442

    I like this way of expressing the solution:

    SELECT t1.ID FROM dbo.T1 AS t1

    UNION

    SELECT t2.ID FROM dbo.T2 AS t2

    EXCEPT

    SELECT t1.ID FROM dbo.T1 AS t1

    INTERSECT

    SELECT t2.ID FROM dbo.T2 AS t2;

  • Lokesh Vij

    SSChampion

    Points: 10836

    Thanks for a good question to start the week!

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • demonfox

    SSCertifiable

    Points: 6289

    Too many queries , too many aliases ; I started to loose patience ...

    but it said 2 correct , and found that initially ...

    saved the day ..

    an easy one to start the day..

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • This was removed by the editor as SPAM

  • Koen Verbeeck

    SSC Guru

    Points: 258941

    So much to read on a Monday morning ๐Ÿ™‚

    I wonder why the explanation talks about EXCEPT when none of the answers use it.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Toreador

    SSChampion

    Points: 11231

    Koen Verbeeck (11/26/2012)


    I wonder why the explanation talks about EXCEPT when none of the answers use it.

    +1 ๐Ÿ˜‰

  • TomThomson

    SSC Guru

    Points: 104772

    Nice basics question.

    Odd sort of explanation though - maybe the author thought the references explained it well enough that he didn't need to say anything himself about why the actual answer is right and other options wrong. For the few people who got this wrong it might have been worth pointing out that any select statement of the general form

    select col from something where col is null

    can't return anything that isn't null, which immediately eliminates options 2,4,5 and 6, leaving only 1 and 3 as possibilities.

    Tom

  • Lokesh Vij

    SSChampion

    Points: 10836

    SQL Kiwi (11/25/2012)


    I like this way of expressing the solution:

    SELECT t1.ID FROM dbo.T1 AS t1

    UNION

    SELECT t2.ID FROM dbo.T2 AS t2

    EXCEPT

    SELECT t1.ID FROM dbo.T1 AS t1

    INTERSECT

    SELECT t2.ID FROM dbo.T2 AS t2;

    One more way without using EXCEPT or INTERSECT

    SELECT Isnull(a.id, b.id)

    FROM t1 a

    FULL OUTER JOIN t2 b

    ON a.id = b.id

    WHERE a.id IS NULL

    OR b.id IS NULL

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • kapil_kk

    SSC-Insane

    Points: 21316

    Very good question to start of the week with joins ๐Ÿ™‚

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kalyani.k478

    Default port

    Points: 1400

    +1:) easy basic question

  • Paul White

    SSC Guru

    Points: 150442

    Lokesh Vij (11/26/2012)


    One more way without using EXCEPT or INTERSECT

    SELECT Isnull(a.id, b.id)

    FROM t1 a

    FULL OUTER JOIN t2 b

    ON a.id = b.id

    WHERE a.id IS NULL

    OR b.id IS NULL

    That's the one I wrote first (though I used COALESCE, as it happens). The optimizer's expansion of the full join to left join concat anti-semi join eventually led me to the one that uses UNION, INTERSECT and EXCEPT. I like that combination of operators; it's the set-theoretic idea of difference being the union minus the intersection.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Koen Verbeeck (11/26/2012)


    I wonder why the explanation talks about EXCEPT when none of the answers use it.

    Probably because the author of the question realized that it would have been so much easier to do this with EXCEPT instead of using these left and right anti-semi-join patterns.

    In code that is supposed to be supported in the future, I'd always use

    SELECT a.Id

    FROM T1 AS a

    WHERE NOT EXISTS

    (SELECT *

    FROM T2 AS b

    WHERE b.Id = a.Id)

    UNION ALL -- All solutions in the question should have used UNION ALL too!!

    SELECT b.Id

    FROM T2 AS b

    WHERE NOT EXISTS

    (SELECT *

    FROM T1 AS a

    WHERE a.Id = b.Id);

    I also worked out a solution with a full outer join and no union, and a solution with intersect and except, but those (or cariations that are close enough) are already posted.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 25 total)

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