4 ms·
In your example, "TRUE AND NULL" is a value, it's not a boolean condition. What is the difference? Try this: SELECT CASE WHEN (TRUE AND NULL) THEN 'A' ELSE 'B
by piaste 5y ago
In your example, "TRUE AND NULL" is a value, it's not a boolean condition.
What is the difference? Try this:
SELECT CASE WHEN (TRUE AND NULL) THEN 'A' ELSE 'B' END
Now "TRUE AND NULL" is a boolean condition, and it will return false, and the boolean conditional will evaluate to 'B'. If SQL had three-way logic, some expressions inside the parentheses would make the whole conditional return NULL, but it will always return either 'A' or 'B'.
- civilized 5y agoAh, I got it now. Yeah, it's definitely weird how nulls reproduce themselves in values (when appropriate), but they get treated as zeros or false in boolean conditions. Base R has more consistent behavior, e.g. if you index into a data frame with an NA, the NA will give you a row full of NAs.
- Pxtl 5y agoIt's almost as if the people designing SQL engines realized how furiously inconvenient null boolean semantics were and chose to ignore them wherever it suited them. I just wish they'd ignored them in one more spot: equality checking.