4 ms·
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
by yahelc 11y ago
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).
- mirimir 11y agoSELECT * FROM bar WHERE foo IS NOT NULL AND foo NOT IN (SELECT foo FROM tabl)
- dllthomas 11y agoThe fact that you seem to think that changes anything clearly demonstrates that this behavior is confusing.
- mirimir 11y agoOK, 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.
- dllthomas 11y agoYour 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.
- mirimir 11y agoThanks. I am sometimes just so perfectly assbackwards, and so blind to it ;)