Empty set returned when asking for items not in empty set

Question

I have a query that is behaving in ways I would otherwise not expect.

I have two tables, stagin_users and users. In both tables I have a column called name. In the users table, EVERY value for name is NULL. In staging_users I have 13 rows that do not have a NULL value. I am trying to run a query where I get all users in the staging table whose name is not in the users table.

My query as written is:

SELECT name FROM staging_users WHERE name NOT IN (SELECT name FROM users);

As the query is written, I get NO results back. What is the reason for this behavior?

As the users table only has NULL values I know I could say WHERE name IS NOT NULL and I would get the same results, but I want this query to work against the values in the table, which all happen to be NULL.

possible duplicate of [MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) - Unexpected result](http://stackoverflow.com/questions/1001144/mysql-select-x-from-a-where-not-in-select-x-from-b-unexpected-result) — , Aug 28 '14 at 16:57
NULL is looked upon as `unknown` which makes the query sort of invalid. — RST, Aug 28 '14 at 17:18

Arth · Accepted Answer · 2014-08-28T17:01:51.673

From the docs:

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.

And

expr NOT IN (value,...) is the same as NOT (expr IN (value,...)).

Thus as you are SELECTing NULL values.. NOT IN returns NULL.. so no rows match.

You could rectify as so:

SELECT name 
  FROM staging_users 
 WHERE name IS NOT NULL
   AND name NOT IN (
   SELECT name 
     FROM users 
    WHERE name IS NOT NULL
       );

Or, same logic:

   SELECT su.name 
     FROM staging_users su
LEFT JOIN users u
       ON u.name = su.name
      AND su.name IS NOT NULL
      AND u.name IS NOT NULL;

As an extra note, I would seriously question a data structure that allows users to have NULL names.. your original query will work if this is changed.

score 1 · Answer 2 · edited Apr 13 '17 at 12:42

1

NOT EXISTS is usually a better option for this type of query. NOT IN tends to be inherently unsafe when working with potentially NULL values. See the following link for more details.

https://dba.stackexchange.com/questions/17407/why-does-not-in-with-a-set-containing-null-always-return-false-null

edited Apr 13 '17 at 12:42

Community

1
1

answered Aug 28 '14 at 16:57

amherrington

21
2

Empty set returned when asking for items not in empty set

2 Answers2