CODEWITHSUNDEEP
mysqlsql
user1022585
user1022585

Reputation: 13651

mySQL 'where name NOT IN' not working?

I have this statement:

SELECT id, name 
FROM players
WHERE this = 1 
  AND name NOT IN (SELECT name 
                   FROM players_online 
                   WHERE this = 'that')
ORDER BY RAND() 
LIMIT 3, 6

All columns are correctly named. No SQL errors occur, it just returns no results.

From what I've read in documentation it should work, but doesn't.

Any idea?

Upvotes: 1

Views: 1840

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

Don't use NOT IN with subqueries. It has the wrong semantics if any value in the subquery is NULL. In that case, the NOT IN never evaluates to TRUE, so no rows are returned at all.

Instead, use NOT EXISTS:

SELECT p.id, p.name
FROM players p
WHERE p.this = 1 AND 
      NOT EXISTS (SELECT 1
                  FROM players_online po
                  WHERE po.name = p.name AND po.this = 'that'
                 )
ORDER BY RAND()
LIMIT 3, 6;

Upvotes: 1

Related Questions