Reason for NOT IN behaviour sql&gt; SELECT * FROM runners;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | John Doe     |
|  2 | Jane Doe     |
|  3 | Alice Jones  |
|  4 | Bobby Louis  |
|  5 | Lisa Romero  |
+----+--------------+

sql&gt; SELECT * FROM races;
+----+----------------+-----------+
| id | event          | winner_id |
+----+----------------+-----------+
|  1 | 100 meter dash |  2        |
|  2 | 500 meter dash |  3        |
|  3 | cross-country  |  2        |
|  4 | triathalon     |  NULL     |
+----+----------------+-----------+


What will be the result of the query below?

SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races);


The above query will return nothing because the races table contain null winner_id. But what is the reason behind this?
SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races);


Is something like this (actual values from races):

SELECT * FROM runners WHERE
id &lt;&gt; 2 and
id &lt;&gt; 3 and
id &lt;&gt; null;


id &lt;&gt; null is unknown by definition, so the whole where clause evaluates to either false (for ids: 2, 3) or unknown (for ids: 1, 4, 5) because of the and operators, thus no result returned.

If you want to run the query disregarding the nulls in races, you can use:

SELECT * FROM runners 
WHERE id NOT IN 
      (SELECT winner_id FROM races WHERE winner_id IS NOT NULL);