CODEWITHSUNDEEP
mysqlsql
Peter P.
Peter P.

Reputation: 3507

Is there a way to query mysql for all records that are not in a set, even when its empty?

So I know mysql says:

In general, tables containing NULL values and empty tables are “edge cases.” When writing subqueries, always consider whether you have taken those two possibilities into account. http://dev.mysql.com/doc/refman/5.0/en/all-subqueries.html

But this doesn't make sense. If I say:

select * from users where users.id NOT IN (NULL);

shouldn't it return all users? Even by their own logic which says:

NOT IN is an alias for <> ALL. Thus, these two statements are the same:

which to me says, return all records where id does not equal NULL, which is all records.

So the question is, how can I achieve selecting all records that are not in a particular set, even if that set is empty.

Obviously, in my application code(a Rails app), I can check if the set is empty and modify my query, but that seems ridiculous. Is there any way to do this all in sql?

Upvotes: 0

Views: 127

Answers (1)

Dale M
Dale M

Reputation: 2473

You are making the common mistake of assuming that NULL is or has a value - in this case you are assuming that NULL = empty set.

NULL means undefined - so in all RDMS NULL = NULL is NULL and NULL <> NULL is also NULL. For that matter Anything = NULL is NULL as is Anything <> NULL. NULL in a RDMS is the same as division by zero in mathematics.

This is why you have to use the special comparison operator IS NULL rather than =.

The query as you have structured it therefore translates to select * from users where users.id NOT IN (undefined). And users.id NOT IN (undefined) is itself undefined.

I assume the NULL in your query is from a variable in Rails - I'll call it pnl (possibly null list). So this query will give you what you want.

select * from users where users.id NOT IN (pnl) OR pnl IS NULL;

Upvotes: 2

Related Questions