3 ms·
They forgot "IS NOT DISTINCT FROM", which is, IMHO, the best god damn operator ever invented. It works like "=", but adequately responds to NULL: postgres=#
by ainar-g 7y ago
They forgot "IS NOT DISTINCT FROM", which is, IMHO, the best god damn
operator ever invented. It works like "=", but adequately responds to
NULL:
postgres=# SELECT 1 IS NOT DISTINCT FROM 1;
?column?
----------
t
(1 row)
postgres=# SELECT NULL IS NOT DISTINCT FROM NULL;
?column?
----------
t
(1 row)
- kingdomcome50 7y agoWait... You don't like: `ISNULL(NULLIF(A, B), NULLIF(B, A)) IS NULL` What are you? human?
- oefrha 7y agoCame here to say the same thing. An article about NULL without even a passing mention of IS DISTINCT FROM is embarrassing. Truth table here: https://wiki.postgresql.org/wiki/Is_distinct_from#Truth_Table https://wiki.postgresql.org/wiki/Is_distinct_from#Truth_Tabl...
- pavel_lishin 7y agoOh my god. We had a bug awhile back due to null equality comparison treating nulls as non-equal; I wish I'd known about mysql's equivalent ("<=>") back then!
- carterehsmith 7y ago"SELECT NULL IS NOT DISTINCT FROM NULL" ?column? ---------- t That bothers me. It is like saying that "infinity" (+inf) equals other infinity. Which is just wrong. Some other SQL dialects will return "UNKNOWN" rather than "t" (true), which imho makes way more sense.
- Tostino 7y ago...I think you are misunderstanding the whole reason for the IS NOT DISTINCT FROM operator, which is exactly that. "SELECT NULL = NULL" will return NULL, use that if you want the standard way. There are many cases where you want it to return true though, and the boilerplate for that sucks, so IS NOT DISTINCT FROM has a very useful place in the dialect.
- ibrar74 7y agoActually this blog is not about how many ways you can use NULL in the database. It's all about the people who have programming experience (C/C++/JAVA) and try to write the SQL queries.