2

First a quick explanation: I am actually dealing with four tables and mining data from different places but my problem comes down to this seemingly simple concept and yes I am very new to this...

I have two tables (one and two) that both have ID columns in them. I want to query only the ID columns that are in table two only, not in both. As in..

Select ID
From dbo.one, dbo.two
Where dbo.two != dbo.one

I actually thought this would work but I'm getting odd results. Can anyone help?

2boolORNOT2bool
  • 557
  • 3
  • 9
  • 23

2 Answers2

2
SELECT t2.ID
    FROM dbo.two t2
    WHERE NOT EXISTS(SELECT NULL
                         FROM dbo.one t1
                         WHERE t2.ID = t1.ID)

This could also be done with a LEFT JOIN:

SELECT t2.ID
    FROM dbo.two t2
        LEFT JOIN dbo.one t1
            ON t2.ID = t1.ID
    WHERE t1.ID IS NULL
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Works great thanks a bunch! Now I'm off to study this NOT EXISTS clause. Thanks again! – 2boolORNOT2bool Aug 08 '11 at 16:48
  • @2boolORNOT2bool: see my answer on dba.se please http://dba.stackexchange.com/questions/4009/the-use-of-not-logic-in-relation-to-indexes/4010#4010 – gbn Aug 09 '11 at 06:28
  • I'm learning that you guys know a great deal more than I expected, I know a great deal less than I expected, and Stack is hugh. Theres a Christianity site! Wow! – 2boolORNOT2bool Aug 09 '11 at 14:46
2

Completing the other 2 options after Joe's answer...

SELECT id
FROM dbo.two
EXCEPT
SELECT id
FROM dbo.one

SELECT t2.ID
FROM dbo.two t2
WHERE t2.ID NOT IN (SELECT t1.ID FROM dbo.one t1)

Note: LEFT JOIN will be slower than the other three, which should all give the same plan.

That's because LEFT JOIN is a join followed by a filter, the other 3 are semi-join

gbn
  • 422,506
  • 82
  • 585
  • 676
  • This looks solid as well. I'll try this out too so I can play with that EXCEPT clause. Thanks for the help! – 2boolORNOT2bool Aug 08 '11 at 21:39
  • Umm.. I ran your query and got different results. I thought I screwed something up so I phisicaly checked the tables and Joes code is just giving me all the IDs in table two. Yours seams to be sorting through. I may have done a pre-imptive strike on the answer check. Or I could very well be doing something wrong. – 2boolORNOT2bool Aug 08 '11 at 21:53
  • @2boolORNOT2bool: any sort is accidental without an ORDER BY – gbn Aug 09 '11 at 06:28