sql - MYSQL: Excluding several rows if one joined row matches a condition -

sql - MYSQL: Excluding several rows if one joined row matches a condition -

i've got 2 tables. 1 users, has fields uid , name. other table users_roles, has fields uid , rid (role id).

i'd retrieve list of users don't have of set of provided roles.

for example:

uid | name ---------- 1 | bob 2 | dave 3 | john

users_roles:

uid | rid --------- 1 | 1 1 | 2 1 | 3 2 | 1 3 | 1

i want able query users don't have set of rids. instance, query excludes rids 2 , 3 should homecoming dave , john, or query excludes rids (1,3) should homecoming nobody.

a query using anti-join pattern efficient:

select u.uid , u.name users u left bring together users_roles r on r.uid = u.uid , r.rid in (2,3) r.uid null

the anti-join pattern left [outer] bring together user_roles table pull matching rows, , rows users don't have matching row. "trick" exclude matching rows predicate in clause eliminates rows users had match.

an equivalent resultset can obtained using not exists correlated subquery:

select u.uid , u.name users u not exists ( select 1 users_roles r r.uid = u.uid , r.rid in (2,3) )

another approach utilize not in, although less efficient. performance depends on whole host of factors. it's possible optimizer generate different execution plans each of these queries.

in case, best performance, you'll need index ... on users_roles (uid) or on users_roles (uid,rid).

followup:

performance testing on mysql 5.1.34 server reveals anti-join query twice fast equivalent not exists , not in queries. (1.091 sec vs. 2.066 sec , 2.020 sec)

-- setup , populate test tables create table t_users ( uid int unsigned not null primary key , `name` varchar(50) ) engine=innodb default charset=latin1 ; create table t_users_roles ( uid int unsigned not null , rid int unsigned not null , primary key (uid,rid) ) engine=innodb default charset=latin1; alter table t_users_roles add together constraint fk_t_users_roles_t_users foreign key (uid) references t_users (uid); create index t_users_ix1 on t_users (uid,`name`); create index t_users_roles_ix1 on t_users_roles (rid,uid); insert t_users (uid,`name`) select d.d*100000+e.d*10000+u.d*1000+h.d*100+t.d*10+o.d+1 uid , concat('name',lpad(d.d*100000+e.d*10000+u.d*1000+h.d*100+t.d*10+o.d+1,8,'-')) `name` (select 0 d union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) o bring together (select 0 d union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t bring together (select 0 d union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) h bring together (select 0 d union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) u bring together (select 0 d union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) e bring together (select 0 d union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) d ; -- 1000000 row(s) affected. insert t_users_roles (uid,rid) select d.d*100000+e.d*10000+u.d*1000+h.d*100+t.d*10+o.d+1 uid , r.rid (select 1 rid union select 2 union select 3) r cross bring together (select 0 d union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) o bring together (select 0 d union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t bring together (select 0 d union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) h bring together (select 0 d union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) u bring together (select 0 d union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) e bring together (select 0 d union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) d (d.d*100000+e.d*10000+u.d*1000+h.d*100+t.d*10+o.d+1) % 100000 <> 0 ; -- 2999970 row(s) affected optimize table t_users; optimize table t_users_roles; show status 'qcache_hits' ; -- variable_name value -- ------------- --------- -- qcache_hits 1117342 show variables 'version' ; -- variable_name value -- ------------- ------------ -- version 5.1.53-log -- table size file scheme $ du -sh data/test/t_users*.ibd 72m data/test/t_users.ibd 133m data/test/t_users_roles.ibd -- anti-join query select sql_no_cache u.uid , u.name t_users u left bring together t_users_roles r on r.uid = u.uid , r.rid in (2,3) r.uid null ; -- exec: 1.095 sec -- exec: 1.090 sec -- exec: 1.091 sec -- exec: 1.087 sec -- exec: 1.090 sec -- avg 5 executions: 1.091 sec -- not exists query select sql_no_cache u.uid , u.name t_users u not exists ( select 1 t_users_roles r r.uid = u.uid , r.rid in (2,3) ) ; -- exec: 2.071 sec -- exec: 2.066 sec -- exec: 2.059 sec -- exec: 2.065 sec -- exec: 2.070 sec -- avg 5 executions: 2.066 sec -- not in query select sql_no_cache u.uid , u.name t_users u u.uid not in ( select r.uid t_users_roles r r.uid not null , r.rid in (2,3) ) ; -- exec: 2.022 sec -- exec: 2.023 sec -- exec: 2.014 sec -- exec: 2.026 sec -- exec: 2.016 sec -- avg 5 executions: 2.020 sec show status 'qcache_hits' ; -- variable_name value -- ------------- --------- -- qcache_hits 1117342 explain output 3 statements: -- anti bring together id select_type table type possible_keys key key_len ref rows filtered -- ------------------ ------ -------------- ------------------------- ----------- ------- ----- ------- -------- ------------------------------------ 1 simple u index t_users_ix1 57 1000423 100.00 using index 1 simple r ref primary,t_users_roles_ix1 primary 4 u.uid 1 100.00 using where; using index; not exists -- not exists id select_type table type possible_keys key key_len ref rows filtered -- ------------------ ------ -------------- ------------------------- ----------- ------- ----- ------- -------- -------------------------- 1 primary u index t_users_ix1 57 1000423 100.00 using where; using index 2 dependent subquery r ref primary,t_users_roles_ix1 primary 4 u.uid 1 100.00 using where; using index -- not in id select_type table type possible_keys key key_len ref rows filtered -- ------------------ ------ -------------- ------------------------- ----------- ------- ------ ------- -------- -------------------------- 1 primary u index t_users_ix1 57 1000423 100.00 using where; using index 2 dependent subquery r index_subquery primary,t_users_roles_ix1 primary 4 func 1 100.00 using index; using

mysql sql drupal