CODEWITHSUNDEEP
sqlleft-join
Marcin
Marcin

Reputation: 2409

is SQL left outer join equivalent to "not in" select query?

I'm trying to evaluate logic and performance of the following 2 queries. I'm thinking they are identical, but access graph shows different path and significantly different performance. Can someone please explain if those are logically equivalent?

select f.folderId FOLDERS_TO_DEL from store.Folders f 
    where f.ownerUserId not in (select u.userId from store.users u);


select f.folderId FOLDERS_TO_DEL from store.Folders f LEFT JOIN 
    store.Users u ON f.ownerUserId=u.userId WHERE u.userId IS NULL;

Edit: I would like to add that all ID fields are not-nullable in my examples.

Upvotes: 0

Views: 436

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181027

No, if there are NULLs in either Folders.ownerUserId or Users.userId, they behave in quite different ways.

A simple SQLfiddle demo with a null in ownerUserId.

Upvotes: 3

Related Questions