SELECT wat FROM sql

Re:SELECT wat FROM sql by Pxtl on Monday April 27, 2020 @03:48PM (#22999268)

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.

[ Parent | on HN ]

Re:SELECT wat FROM sql by da_chicken on Monday April 27, 2020 @05:57PM (#23000592)

> Then why on earth is NULL being treated the same as false for the purposes of a WHERE clause.

Because, by definition, the WHERE clause returns a record when it evaluates to TRUE. FALSE and UNKNOWN are not TRUE.

Second, you absolutely can do what you're trying to here, but not the way you've done it:

  SELECT CASE WHEN bar = baz THEN 'foo' ELSE 'bar' END AS corge
  FROM quux

You're asking the wrong question and wondering why you're getting the wrong answer.

[ Parent | on HN ]

Re:SELECT wat FROM sql by atlantique on Monday April 27, 2020 @05:03PM (#22999992)

It sounds like it is operating according to a closed-world assumption, in which this is reasonable behavior.

[ Parent | on HN ]