CODEWITHSUNDEEP
mysqlsql
DrDan
DrDan

Reputation: 63

NOT IN missing results

I've got a pretty simple query, "I want all the user_ids which don't have a role_type of 'GROUP'. The following sqlfiddle gives the correct result I want http://sqlfiddle.com/#!2/0a9640/1 but when I run the query on my actual MySQL DB it doesn't return the 2 results with user_id=13565 and I've no idea why.

The subquery gives the single answer 8301 and indeed if I manually enter this value into the brackets i.e.

select * from role 
where role.user_id not in (
  select role.user_id 
  from role
  where role.role_type = 'GROUP' 
)
and (group_id=1465 or group_id=6314)
order by user_id

Then the 2 missing results somehow reappear. Any ideas why these results are stripped out when I use the subquery with NOT IN?

Upvotes: 2

Views: 102

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270783

A known confusion with not in is that the all rows get filtered out if the subquery returns NULL for any of the elements. This can be fixed by checking for this explicitly:

select r.*
from role r
where r.user_id not in (select r2.user_id 
                        from role r2
                        where r2.role_type = 'GROUP' and r2.user_id is not null
                       ) and
      r.group_id in (1465, 6314)
order by r.user_id;

I prefer using not exists, because it doesn't have this issue with NULLs:

select r.*
from role r
where not exists (select r2.user_id 
                  from role r2
                  where r2.role_type = 'GROUP' and r2.user_id = r.user_id
                ) and
      r.group_id in (1465, 6314)
order by r.user_id;

EDIT:

I suspect the problem is that user 13565 has a role_type = 'GROUP' but for a different group_id. Perhaps this is the query you really want:

select r.*
from role r
where not exists (select 1
                  from role r2
                  where r2.role_type = 'GROUP' and r2.user_id = r.user_id and
                        r2.group_id in (1465, 6314)
                ) and
      r.group_id in (1465, 6314)
order by r.user_id;

Upvotes: 1

Joël Salamin
Joël Salamin

Reputation: 3576

Using a subquery is not the best way when we think about performance.

Here is the query you're looking for, without use of subquery:

SELECT R.*
FROM role R
LEFT OUTER JOIN role R2 ON R2.user_id = R.user_id
                         AND R2.role_type = 'GROUP'
WHERE R.group_id IN (1465, 6314)
    AND R2.user_id IS NULL
ORDER BY R.user_id

Hope this will help you

Upvotes: 0

Related Questions