4 ms·

From the article: "The SQL null value basically means “could be anything”." I wouldn't call the NULL an unknown or as a "could be anything". It's tempting to t

• _pmf_ 9y ago
  > but I choose to interpret the NULL is a less sexy way: Unknown means that the state DOES NOT EXIST That's wrong.
  • ak39 9y ago
    Why is it wrong?
    • the_mitsuhiko 9y ago
      Because it represents (and behaves) as unknown and not as absent. Practical example is `1 not in (null)`
      • MarkusWinand 9y ago
        Just my 2c from SQL standards perspective: > “Every [SQL] data type includes a special value, called the null value,” “that is used to indicate the absence of any data value”. (http://modern-sql.com/concept/null http://modern-sql.com/concept/null) For the Boolean type, null = unknown: > Note that the truth value unknown is indistinguishable from the null for the Boolean type. Otherwise, the Boolean type would have four logical values. (http://modern-sql.com/concept/three-valued-logic http://modern-sql.com/concept/three-valued-logic) Although they are very closely related, I believe the general `null` value (for any data type) and three-valued logic (null for Boolean) should be separated (that's why I have two articles).
      • dghf 9y ago
        > Because it represents (and behaves) as unknown and not as absent. It can represent both. For example, in a table of biographical data, a NULL value for DateOfDeath could represent N/A for someone still living, unknown for a dead person whose date of death is not known, and total uncertainty for a missing person who could be alive or dead.
        • ak39 9y ago
          I've seen this design in the wild quite often. It's most often attributed to the abuse of an intended original design. The original designer most likely intended the value populated in the "DateofDeath" column to imply death. The intention didn't account for someone down the system's evolution to imply "ALLOW NULL" to be interpreted as optional. If your table is expected to answer "how many dead people whose date of death is unknown" then you'll most certainly also need an "IsDead" (true/false) NOT NULL column. (Best separate the table into "Death" with NOT NULL DateofDeath.)
          • dllthomas 9y ago
            Operational concerns might push you otherwise, but I think in terms of correctly representing the world what makes the most sense is a separate table of deaths with a primary key that's also a foreign key into the table of people, and which has a nullable date of death. That lets you represent all three of "they are living", "they are dead but we don't know when they died", and "they are dead and we know when they died".
      • ak39 9y ago
        I agree with the first part of your statement: "Because it represents (and behaves) as unknown". But I'm also inclined to think of the unknown BECAUSE of (or as a consequence of) absence of data. And most importantly, this absence of data (non-existence) is useful too. I have a problem with the logic that intends to over fit absence/unknown to mean "it could be true or it could be false" ... or worse ... "it could be both". If you are doing this in SQL WHERE predicates: (T-SQL) WHERE IsNull(IsHivPositive, 0) = 1 I would strongly argue to reimagine the use of that "boolean" column.
        • lifepillar 9y ago
          However you interpret a NULL (as "value existent but unknown", "value not existent", or "no information, i.e., value might exist or not"), you will have a hard time matching your interpretation with SQL's behaviour, no matter what the standard says. In fact, interpreting NULLs as "value existent but unknown" is the most troublesome of the three interpretations, both because of SQL's rules (for instance, select * from T where A=A is empty if A is NULL, but an existent value should be certainly equal to itself even if you don't know what it is) and due to the intrinsic computational complexity of related problems (basically, related to the intractability of recognising tautologies). The closest to a sensible semantics that I have seen is a 2012's paper titled "On the Logic of SQL Nulls", which argues that the "correct" interpretation of NULL is "value not existent". It is a quite theoretical paper, though, and it covers only a subset of SQL. If you want to use NULLs meaning "value exists but is unknown" I recommend that you the papers written by Hans-Joachim Klein, mostly in the '90s. His "switching semantics" provides some very interesting rules for writing queries with NULLs, which are at least guaranteed to output a subset of the correct answers wrt some reasonable definition of "correctness" (some of his results were rediscovered recently by Libkin btw).
          • cwyers 9y ago
            Why would NULL equal itself? You're basically asking for SQL to special-case the Boolean logic so that if a null value is compared to itself it returns true. Who needs that?
    • jhanschoo 9y ago
      It represents something that is there that you don't know the value of. So when you use it as part of an expression even as a counterpart to a term that you know the value of, it "corrupts/interacts" so that the return value of the expression is unknown whereever the expression's return value is dependent on the value that we had chosen to represent with NULL. This is different semantics from say, taking the AND with a term that is not present, which in most conventions would have us substitute the identity value (i.e. TRUE), so that the return value is TRUE if and only if the known value is TRUE; instead, we have that the return value in SQL is NULL.
• igravious 9y ago
  NULLs are built into the algebraic and arithmetic machinery of SQL. You can see how the equality operator (=) acts in the face of NULLs. = | F | T | N ---+---+---+--- F | 1 | 0 | ---+---+---+--- T | 0 | 1 | ---+---+---+--- N | | | Using the SELECT 1=NULL; syntax of SQLite we can see that SQLite does not even attempt to provide an answer. What would we say of something that cannot be determined? We say that it is _indeterminate_. So we can find out how NULLs behave by seeing what our database does (or reading the SQL spec.) But how we _interpret_ NULL is context and application specific. It's completely valid to interpret NULL as "data-is-missing" or "does-not-exist" if you so wish. Maybe the more reasonable interpretation is "unknown" as the article urges. However you have urged us to regard it (not the result of its operations which is another thing) as – at one and the same time – "the state does-not-exist", "zero", "irrelevant" which is unreasonable. Treating NULLs as unknowns and the results of operations on NULLs as indeterminate is a better recommendation I think even though ultimately the interpretation is application and context specific.
• deleted 9y ago
  [deleted]
• snarfy 9y ago
  I like to think of it as 'outside the current set'. Saying it doesn't exist is like saying sqrt(-1) doesn't exist, yet it does, it's just 'outside' the set of real numbers. In a strongly typed language with a boolean type, a comparison to null would require a type change. It's 'outside' the set of the boolean type.
  • techno_modus 9y ago
    > I like to think of it as 'outside the current set'. Yes, if it exists (as an element of some set), then it is definitely outside the declared set of values. The main question is how we can formalize it, particularly, by defining the set NULL is a member of. For example, the following general approaches are possible: o Extend (any or some) set of values with a special element (NULL) o NULL = 0 (empty set) o NULL = <> (empty tuple) o Treat NULL as non-existence which entails having no-op if it is met, for example, 2 + NULL + 3 = 5 (just delete NULL which means skip it).