39

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

Improve this question

4 Answers 4

Reset to default
55

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 )
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Is it the OP's case? My understanding was that the "if no match is found and one of the expressions in the list is NULL" isn't the case since 732 should be found, thereby invalidating that clause, right?
@Eric: 732 is not found in emails.
52

... 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)
Improve this answer

2 Comments

This worked for me, wanted to create a dropdown of "meter types" which had not already been selected, ie had no records in second table
I prefer this answer. Didn't know the "where id is not null null" was necessary to make the "not in" query work.
9

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.

Improve this answer

1 Comment

My understanding is that using a left outer join is better for performance as well.
3

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.

Improve this answer

1 Comment

Even though it's not intuitive, that's actually the most efficient solution.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.