I've written a bajillion queries and have tons of nitpicks, but it's the twin meanings of NULL that really kills me.
NULL can be the value of a field in a record, but it is also used to indicate the lack of a record in a JOIN.
If I run:
SELECT x.a, y.b FROM x LEFT JOIN y on x.a = y.a
and I get back
[5, NULL]
I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table y that starts with 5.
Obviously there are lots of workarounds, but to me this is a truly fundamental design flaw. SQL should have specified another NULL-like value, call it EMPTY, used only for joins that find no data. And analagous to IS NULL, it would be checked using IS EMPTY.
NULL can be the value of a field in a record, but it is also used to indicate the lack of a record in a JOIN.
If I run:
and I get back I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table y that starts with 5.Obviously there are lots of workarounds, but to me this is a truly fundamental design flaw. SQL should have specified another NULL-like value, call it EMPTY, used only for joins that find no data. And analagous to IS NULL, it would be checked using IS EMPTY.