Skip to content

Categories:

mysql “NOT IN” not working as expected

Last night, I was trying to extract all users that were not listed in one of several other tables. Thinking that I could simply use

SELECT * FROM users WHERE users.id NOT IN (SELECT othertable.id FROM othertable)

and receiving a result, I was on my way.

However, after analyzing the data a little bit I was surprised to see records in the set that should not have been there. “NOT IN” was not working as I expected it to.

A quick search also yielded similar experiences from other users on stackoverflow. I changed the query to use LEFT JOIN as follows:

SELECT * FROM users LEFT JOIN othertable on users.id = othertable.id WHERE othertable.id IS NULL

This query yields the expected data and according to the stackoverflow discussion even performs a little better. It is easy extendable to more than two tables as well.

Time to go back and check similar code I’ve used in the past!

 

Be Sociable, Share!

Posted in code.


0 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.



Some HTML is OK

or, reply to this post via trackback.