CODEWITHSUNDEEP
mysql
Bolto Cavolta
Bolto Cavolta

Reputation: 443

Using IS NULL vs NOT IN

I have three tables, user, role, and user_role.

I have a query to find users without a role assigned to them. I would like to know which one of the following queries is more efficient, if any:

select * from user u left join user_role ur on u.id = ur.user_id where ur.user_id is null

vs

select * from user u where u.id not in (select user_id from user_role);

Upvotes: 0

Views: 39

Answers (2)

shawtydubs
shawtydubs

Reputation: 64

Your first query will perform the join once and then simply filter on whichever ones are null. Your second query performs the subquery, and then checks every user from user against every user in user_role. Sometimes you need to perform a subquery based on the structure of your tables, but for something simple like this I would go with your first one.

Upvotes: 1

juergen d
juergen d

Reputation: 204904

Run them using explain select ... to see which runs faster. It actually depends on the amount of data in your tables. IN clauses perform slower with much data.

Upvotes: 3

Related Questions