4 ms·

I don't know why OP described SQL NULLs as 'three-valued logic' because it's anything but. SQL boolean operators can only result true or false, there isn't a t

by piaste 5y ago

I don't know why OP described SQL NULLs as 'three-valued logic' because it's anything but. SQL boolean operators can only result true or false, there isn't a third option. NULL is a data value for all data types, which happens to return 'true' to the boolean operation 'is null', and 'false' to any other boolean operation. NULL = NULL or NULL = 1 don't return a third-option NULL, there is no such thing, they return a plain old 'false'. (NULL = NULL returns false because otherwise it would make outer joins terrible) As you pointed out, SQL does not implicitly convert boolean values (bits) to or from the result value of boolean operators. Bits are values and can be NULL. Boolean results are not values and can only be true or false. Now, is this optimal? Hell no. (Sum types would probably be the way to go if SQL were designed 50 years later.) But it's consistent.

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 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 ago
    Ah, 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 ago
    It'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.

by piaste 5y ago

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 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 ago
  Ah, 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 ago
  It'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.