CODEWITHSUNDEEP
sqlmysql
Chris Burgess
Chris Burgess

Reputation: 3631

MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) - Unexpected result

I expect the result of the third query below to contain id=732. It doesn't. Why is that?

mysql> SELECT id FROM match ORDER BY id DESC LIMIT 5 ;
+------------+
|         id |
+------------+
|        732 | 
|        730 | 
|        655 | 
|        458 | 
|        456 | 
+------------+
5 rows in set (0.00 sec)

mysql> SELECT id FROM email ORDER BY id DESC LIMIT 5 ;
+------------+
|         id |
+------------+
|        731 | 
|        727 | 
|        725 | 
|        724 | 
|        723 | 
+------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM match WHERE id NOT IN ( SELECT id FROM email ) ;
Empty set (0.00 sec)

There are three NULL entries in table email.id, and no NULL entries in match.id.

The full table / queries can be seen at http://pastebin.ca/1462094

Upvotes: 39

Views: 226870

Answers (4)

DrupalFever
DrupalFever

Reputation: 349

Here is some SQL that actually make sense:

SELECT m.id FROM match m LEFT JOIN email e ON e.id = m.id WHERE e.id IS NULL

Simple is always better.

Upvotes: 3

auris
auris

Reputation: 711

... or if you really want to use NOT IN you can use

SELECT * FROM match WHERE id NOT IN ( SELECT id FROM email WHERE id IS NOT NULL)

Upvotes: 52

Quassnoi
Quassnoi

Reputation: 425321

From documentation:

To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

This is exactly your case.

Both IN and NOT IN return NULL which is not an acceptable condition for WHERE clause.

Rewrite your query as follows:

SELECT  *
FROM    match m
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    email e
        WHERE   e.id = m.id
        )

Upvotes: 55

Eric
Eric

Reputation: 95123

I'm a little out of touch with the details of how MySQL deals with nulls, but here's two things to try:

SELECT * FROM match WHERE id NOT IN 
    ( SELECT id FROM email WHERE id IS NOT NULL) ;

SELECT
    m.*
FROM
    match m
    LEFT OUTER JOIN email e ON
        m.id = e.id
        AND e.id IS NOT NULL
WHERE
    e.id IS NULL

The second query looks counter intuitive, but it does the join condition and then the where condition. This is the case where joins and where clauses are not equivalent.

Upvotes: 9

Related Questions