Then why on earth is NULL being treated the same as false for the purposes of a WHERE clause.
Simple point:
SELECT 'foo' WHERE bar = baz FROM quux
UNION ALL
SELECT 'bar' WHERE bar <> baz FROM quux
To me, there are two acceptable outcomes of this situation:
1) The result is a single row containing "foo" or "bar".
2) The result is an error message that the truthiness of 'bar = baz' cannot be determined.
However, ansi-null-compatible-SQL just will give you an empty resultset if bar or baz are null. That's not the server saying "I don't know" to "bar = baz", that's the server saying "neither" which is very freaking different. "I don't know" is an acceptable answer. "neither" is a lie.
That is unacceptable, and I'm utterly bewildered that people can insist that this is okay.
Simple point:
To me, there are two acceptable outcomes of this situation:1) The result is a single row containing "foo" or "bar".
2) The result is an error message that the truthiness of 'bar = baz' cannot be determined.
However, ansi-null-compatible-SQL just will give you an empty resultset if bar or baz are null. That's not the server saying "I don't know" to "bar = baz", that's the server saying "neither" which is very freaking different. "I don't know" is an acceptable answer. "neither" is a lie.
That is unacceptable, and I'm utterly bewildered that people can insist that this is okay.