CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Fler
Fler

Reputation: 392

Is NOT IN the same as <> in this context?

Within the where clause of an SP I'm working on, I'm trying to filter out rows where there are matching IDs. Are there any differences in the below? Are there any benefits to either method? (I imagine the first option is quicker).

 - Where t1.ID <> t2.ID
 - Where t1.ID NOT IN (Select ID from T2)

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I can only imagine one context where these would be the same. That would be if t2 had at most one different value of id in the table.

That seems highly unlikely, so I think the only reasonable answer is "no".

I suspect you are thinking of:

select . . .
from t1 left join
     t2
     on t1.id = t2.id
where t2.id is null;

This is almost equivalent to not in with a subquery. (The only difference is if id is ever NULL.)

Upvotes: 3

Related Questions