Select Query

Hello!

My name is Assaf Lasry and I'm a junior programmer and I have a problem building a query. I'm using SQL Server and I have 2 tables (TB1, TB2). in both tables, I have a field called IDNum. I want to build a query that selects all ID's in TB1 that are NOT found in TB2. Example: If in both tables I have the following records:

TB1: IDNum          TB2: IDNum
      1                   1
      2                   4
      3                  
      4
we can see that 2 and 3 are not in TB2. These are the values that I want to retrieve. I was wondering if you could help me and tell me what should my query look like. Please, I would appreciate your help.

Thank you in advance,
Assaf Lasry.
AssafLAsked:
Who is Participating?
 
DrSQLCommented:
Assaf,

  Normally, I would be a little more direct than spcmnspff:

select IDNum from TB1
where not exists (select 1 from TB2 where IDNum = TB1.IDNum);

  You could also use relational operations to accomplish the same thing:

select IDNum from TB1
MINUS
select IDNum from TB2;

Both are pretty clear as to what they're trying to do and are about as fast as outer joining and looking for the unmatched records.

Good luck!
0
 
spcmnspffCommented:
Assafl we need to do an outer join checking for null in TB2:

Select TB1.IDNum
From TB1 Left Outer Join TB2
    ON TB1.IDNum = TB2.IDNum
WHERE TB2.IDNum Is Null

That should do it for ya . . . =)
0
 
spcmnspffCommented:
Does SQL server support relational notation?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DrSQLCommented:
spcmnspff,
  Sorry about that, no it doesn't suppport MINUS (at least thru v7.0).  It only has the UNION operator.  Microsoft recommends "NOT EXISTS" instead.

Good luck!
0
 
thegroupCommented:
I recomend that option

Select TB1.IDNum
From TB1 Left Outer Join TB2
   ON TB1.IDNum = TB2.IDNum
WHERE TB2.IDNum Is Null

because exists (select...) is very slow in comparision because every record in TB1 triggers a Select on TB2.
0
 
nico5038Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: spcmnspff  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.