mysql - NOT IN missing results -

i've got pretty simple query, "i want user_ids don't have role_type of 'group'. following sqlfiddle gives correct result want http://sqlfiddle.com/#!2/0a9640/1 when run query on actual mysql db doesn't return 2 results user_id=13565 , i've no idea why.

the subquery gives single answer 8301 , indeed if manually enter value brackets i.e.

select * role  role.user_id not in (   select role.user_id    role   role.role_type = 'group'  ) , (group_id=1465 or group_id=6314) order user_id 

then 2 missing results somehow reappear. ideas why these results stripped out when use subquery not in?

a known confusion not in rows filtered out if subquery returns null of elements. can fixed checking explicitly:

select r.* role r r.user_id not in (select r2.user_id                          role r2                         r2.role_type = 'group' , r2.user_id not null                        ) ,       r.group_id in (1465, 6314) order r.user_id; 

i prefer using not exists, because doesn't have issue nulls:

select r.* role r not exists (select r2.user_id                    role r2                   r2.role_type = 'group' , r2.user_id = r.user_id                 ) ,       r.group_id in (1465, 6314) order r.user_id; 

edit:

i suspect problem user 13565 has role_type = 'group' different group_id. perhaps query want:

select r.* role r not exists (select 1                   role r2                   r2.role_type = 'group' , r2.user_id = r.user_id ,                         r2.group_id in (1465, 6314)                 ) ,       r.group_id in (1465, 6314) order r.user_id;