Hacker News Reader

Comment by yahelc | Hacker News Reader

Useful but dangerous:

If you have (1,2,3,4,NULL) as data in column foo, and you query

    select foo from tbl WHERE foo!=1

Returns:

    2
    3
    4

Which, makes sense, but can be very confusing if you're not explicitly looking out for it.

Redshift (and I think Postgres generally) will also, very confusingly if you're not aware of it, return no rows if your subquery returns any NULL values in a NOT IN scenario.

If bar is (1,2,3,4,5)

    SELECT * FROM bar WHERE foo NOT IN (SELECT foo FROM tabl)

Will return no results (instead of a single row with 5).

View on HN

SELECT * FROM bar WHERE foo IS NOT NULL AND foo NOT IN (SELECT foo FROM tabl)

The fact that you seem to think that changes anything clearly demonstrates that this behavior is confusing.

OK, so I don't currently have a server to test that on. And I'm a little rusty.

But are you saying that my query doesn't resolve the NULL issue? I know that I've done just that with tables containing NULL values. Maybe I screwed up the format.

Your adjustment will filter out any nulls that would have otherwise been in the output. The issue was not NULLs in the output (there was nothing in the output), but NULLs in the subquery in the NOT IN clause. You could address this by filtering inside the subquery:

    SELECT * FROM bar WHERE foo NOT IN (SELECT foo FROM tabl WHERE foo IS NOT NULL)

Making this a little more realistic:

    SELECT * FROM activation_code
    WHERE code NOT IN (
        SELECT activation_code_used FROM user
    );

If that query was previously returning many rows, I contend that many people would not expect it to start returning zero rows just because a user was created without using an activation code.

Thanks. I am sometimes just so perfectly assbackwards, and so blind to it ;)

Reply on news.ycombinator.com