4 ms·

Yup. The semantics are great for joins. Two missing bits of data do not make a found.

• Pxtl 5y ago
  Then why doesn't it error out when faced with NULL? I mean, we hear over and over again when learning SQL "NULL bool and FALSE are different things"... But the SQL engine itself treats NULL and FALSE as equivalent all the time. DECLARE @x INT = NULL SELECT 'foo' WHERE @x = 1 If three-value logic were truly embraced, that should be an error, because we don't know the state of @x. But SQL talks out of both sides of its mouth about Booleans. It freely just assumes that null is false, which we, as developers, are never ever ever allowed to do. There isn't even an operator included in ANSI SQL to let us convert null boolean values into false, because for some reason bits and bools are two different things and you can't just say COALESCE(x = y, false) It's not just that three-value-logic is painful, it's that SQL makes working with them miserable because, at a linguistic level, it seems to politely forget that three-value-logic exists.
  • Taywee 5y ago
    Because then you couldn't do a join across non-mandatory foreign keys. Practically, making all NULL comparisons error out would make them almost entirely useless.
  • 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.
  • deleted 5y ago
    [deleted]
  • dragonwriter 5y ago
    > There isn't even an operator included in ANSI SQL to let us convert null boolean values into false, because for some reason bits and bools are two different things “bit” is not a standard SQL type at all, it's an SQL server proprietary 1-bit numeric type. boolean is an standard SQL type, but SQL Server doesn't support it as a storage type or consistently elsewhere (it does exist, annoyingly, in some contexts, but is limited in where it or expressions producing it can appear, which—not any limitation of standard SQL—is the source of your problem.) > and you can't just say COALESCE(x = y, false) In something that supports standard SQL in the relevant parts, like Postgres, yes, you absolutely can. In SQL Server, you have to do something clumsy like: CASE WHEN x=y THEN 1 ELSE 0 END But that is “SQL Server is broken and barely supports booleans even as intermediate values” not “standard SQL is broken”. There are plenty of valid criticisms of standard SQL around NULLs, but the particular brokenness of SQL Server’s handling of booleans is not one of them.
    • Pxtl 5y ago
      AFAIK MySQL and Oracle have the same flaws.
      • dragonwriter 5y ago
        MySQL and Oracle also lack standard boolean support while allowing true boolean values as results in certain positions, AFAIK, yes, although some versions of MySQL use “Boolean” as an alias for tinyint(1) and even more support TRUE and FALSE (without quotes) as aliases for 1 and 0, which confuses the issue a bit. SQLite has a similar problem, averts the practical because it doesn't use booleans natively internally in which they apply, instead natively using 0 and 1 (with TRUE and FALSE as aliases) and then converting to boolean in the narrow set of boolean contexts. This means that can use COALESCE as described (and, generally, SQLite will behave as you would expect with real Boolean support, even though it doesn't actually have it.) DB2, from what I can find, supports BOOLEAN from v11.1. In any case, the problem here is not a standard SQL problem, even if it is a problem of several popular SQL implementations.