Ho to avoid Not In & In clause in my query

[member0026]

select id,name from stud where (id not in (select id from class)) and depid in (select depid from dep where rid=@rid)

[Bernhard Hiller]

What about:

Select DISTINCT id, name
FROM stud
INNER JOIN dep on stud.depid=dep.depid
LEFT JOIN class on stud.id=class.id
WHERE dep.rid=@rid
AND stud.id is null

But I doubt that it's faster...

[thatraja]

JOIN/EXISTS SQL SERVER – Subquery or Join – Various Options – SQL Server Engine knows the Best[^]

thatraja

Code converters | Education Needed No thanks, I am all stocked up. - Luc Pattyn When you're wrestling a gorilla, you don't stop when you're tired, you stop when the gorilla is - Henry Minute

[GuyThiebaut]

select id,name
from stud s
where not exists
(
select c.id
from class c
where c.id = s.id
)
and depid in
(
select depid
from dep
where rid=@rid
)

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[member0026]

Im not sure, but compare not in the join will be the faster one, i heared like that :(