3 ms·
> SQL boolean operators can only result true or false, there isn't a third option. What...? Go type this into your favorite DB and get back to me: SELECT TRUE
by civilized 5y ago
> SQL boolean operators can only result true or false, there isn't a third option.
What...? Go type this into your favorite DB and get back to me:
SELECT TRUE AND NULL
- piaste 5y agoIn 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.