SQL nulls are weird

SQL NULLs aren&#x27;t <i>weird</i>, they&#x27;re just based off of Kleene&#x27;s TRUE-FALSE-UNKNOWN logic!<p>If you want you can read NULL as UNKNOWN and suddenly a whole bunch of operations involving them become a lot more intuitive:<p>1. TRUE <i>OR</i> UNKNOWN = TRUE, because you know you have at least one TRUE already.<p>2. TRUE <i>AND</i> UNKNOWN = UNKNOWN, because you don&#x27;t know whether you have two TRUEs or not. It&#x27;s just out there.<p>3. UNKNOWN <i>XOR</i> UNKNOWN = UNKNOWN, because it could darn near be anything: TRUE XOR TRUE, TRUE XOR FALSE, FALSE XOR FALSE, FALSE XOR TRUE... Internalizing this is where SQL&#x27;s use of NULL &#x2F; UNKNOWN really becomes intuitive.<p>4. (TRUE AND FALSE) XOR (TRUE OR UNKNOWN) = (FALSE) XOR (TRUE) per #1 = TRUE. See, it&#x27;s consistent, you just need to keep in mind that if you have a lot of known UNKNOWNs they&#x27;re quite parasitic and your final outcome is likely to be, itself, an UNKNOWN. Just like in real life!

&gt; select null = null; returns NULL, because each NULL is basically a placeholder representing any “unknown value”. Two unknown values are not necessarily the same value; we can’t say that they are equal, because we don’t know the value of either of them.<p>Agreed with all of this, it would probably have been better if they were named `unknown` instead of reusing the `null` keyword.<p>Note also that since Postgresql 15, you can use `NULLS NOT DISTINCT` when creating a unique index [0]. I&#x27;m less familiar with other databases.<p>0: <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;15&#x2F;sql-createtable.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;15&#x2F;sql-createtable.html</a>

&gt; ... and this is even less obvious if you’re used to using ORMs.<p>Which is why I continue to be such an ORM skeptic. I agree that they&#x27;re convenient. But I do worry that we&#x27;ve now got an entire generation of engineers who regularly interact with relational databases, but have largely been spared the effort of learning how they actually work.<p>As another commenter pointed out, if you&#x27;ve learned basic relational algebra then the way SQL nulls behave seems obvious and logically consistent. The logic is the same as the logic behind the comparison rules for NaN in IEEE floats. It&#x27;s the behavior of C-style nulls that is, always and forever, a billion-dollar mistake.

I expected the article to mention how in Oracle NULLs are equal to empty strings. Now that is weird.

SQL NULLs are not weird once you consider how you want relational logic to work when they is a record with non-existent values.

When the null concept was introduced to me in the seventies, the only thing I could say was that it would be causing a lot of unnecessary confusion in the future. If you have missing values in your datarecord then that datarecord belongs in an exception-queue. And now some 45 years later people are still discussing it like we did then..

SQL nulls in some ways behave in similar to floating point nans. Of course nans are also weird in their own way, but it is a bit comforting that its not so completely singularly weird.

In Object Oriented Context &quot;null&quot; is useful to indicate that some object doesn&#x27;t have value for that property.<p>What&#x27;s interesting is, do we mean that in our data that attribute has no value? Or do we mean the real-world object represented by the data does not have that attribute?<p>Does null mean<p>a) We don&#x27;t know the value of this attribute for this object, or<p>b) We do know that there is no value for this attribute in the real-world object represented by our data.<p>In JavaScript because there is both null and undefined it is easy to assume that undefined means we don&#x27;t know the value and null means we do know it has no value.<p>EXAMPLE: The attribute &#x27;spouse&#x27;. Some people have a spouse some don&#x27;t. So what does it mean if the value of the field &#x27;spouse&#x27; is null? That we know there is no spouse, or that we don&#x27;t know who the spouse is if any.<p>In practical terms we can say null means &quot;We don&#x27;t know&quot; which includes the case that there is no spouse.

Recall this really funny dialogue in one of the Blackadder episodes.<p>The princess eyes are as blue as the stone of Galveston<p>Have you seen the princess eyes?<p>No!<p>Have you seen the blue stone of Galveston?<p>No!<p>So you&#x27;re comparing something you&#x27;ve never seen with something else you&#x27;ve never seen!<p>That&#x27;s NULL comparison

Even if we set three-value logic aside for a moment, this behavior of NULL still makes sense intuitively.<p>The value of NULL in a particular table cell is simply a way to indicate &#x27;no value&#x27;. If you want the values in a column to be unique, cases where there are no values shouldn&#x27;t be considered.<p>This plays out similarly in practice. For example, you may want to allow users to optionally reserve a username, and if they do, those usernames should be unique. It&#x27;s hard to imagine a use case where by wanting a field to be both optional (nullable) and unique, you mean that the field should be optional for a single record (!) and required for all the rest. Of course, you mean that IF there is a value, THEN it should be unique.

Surprised that this doesn&#x27;t mention &quot;IS DISTINCT FROM&quot; ( <a href="https:&#x2F;&#x2F;modern-sql.com&#x2F;caniuse&#x2F;is-distinct-from" rel="nofollow">https:&#x2F;&#x2F;modern-sql.com&#x2F;caniuse&#x2F;is-distinct-from</a> )<p>(Although in rare cases that is even weirder: <a href="https:&#x2F;&#x2F;stackoverflow.com&#x2F;a&#x2F;58998043" rel="nofollow">https:&#x2F;&#x2F;stackoverflow.com&#x2F;a&#x2F;58998043</a> )

I actually like how NULLs behave in SQL. They mean &quot;I don&#x27;t know&quot; In the modern programming language we all care about Null safety. But no matter how you model your data, you will always run into the situations when you don&#x27;t know everything. So I believe NOT NULL is not very practical. NULLs in SQL handle these case very well - when the input is unknown your output is unknown

NULLs are weird because they are basically two different types under the same name. The 3-value logic type is useful for representing &quot;missing&quot; foreign keys, but 2-value logic type is arguably more useful when searching&#x2F;sorting&#x2F;aggregating.<p>I think we would have been better-off by treating FKs (and maybe outer JOINs) as a special case, and using 2-value logic everywhere else.

FWIW, you can explicitly change this behavior in Postgres as of version 15 - include &quot;NULLS NOT DISTINCT&quot; when creating the unique index.

If you want equality testing with nulls, you want to use `is (not) distinct from` instead of `=` and `&lt;&gt;` &#x2F; `!=`.<p>`1 is not distinct from NULL` =&gt; false<p>`NULL is not distinct from NULL` =&gt; true<p>`0 is not distinct from 1` =&gt; false

I don&#x27;t see why this is weird. Unique means no duplicates. Nulls can&#x27;t be duplicates of each other because they&#x27;re not equal to each other.<p>If you don&#x27;t like null semantics, you&#x27;re free to use sentinel values. You can make all the sentinel values the same, or you can make them all different. Either way, you or someone who has to use your system will be back here tomorrow complaining about how weird it is.

There&#x27;s another good, technical write-up on NULL behavior in SQL at modern-sql.com<p><a href="https:&#x2F;&#x2F;modern-sql.com&#x2F;concept&#x2F;null" rel="nofollow">https:&#x2F;&#x2F;modern-sql.com&#x2F;concept&#x2F;null</a><p>(Note: I am not affiliated with that bloh&#x2F;website in any way, shape, or form.)

This has always made queries unpredictable in many scenarios and it should be a feature to turn nulls off entirely and swap them out with Option&lt;T&gt; instead.

Weird as they seem at first, SQL null handling ends up being convenient the way it is. Part of this is because left&#x2F;right join give you nulls.

For Postgres specific approach, you may refer to<p><a href="https:&#x2F;&#x2F;blog.rustprooflabs.com&#x2F;2022&#x2F;07&#x2F;postgres-15-unique-improvement-with-null" rel="nofollow">https:&#x2F;&#x2F;blog.rustprooflabs.com&#x2F;2022&#x2F;07&#x2F;postgres-15-unique-im...</a><p>Practically speaking, I go with not null, and always set default value.

This reminds me back in the day when I was writing a DSL for a project.<p>Since the data we were getting was sourced from an RDBMS, I wanted NULL to be a first class concept in the DSL, with similar traits.<p>Early on, I simply made any expression that involved a NULL result in NULL. Naively this was all well and good, but it failed spectacularly in condition statements.<p>Instead of A = NULL == false, I had A = NULL == NULL. And, as you can imagine, a single NULL in the expression would just pollute the entire thing, and since NULL was considered as FALSE for conditionals, any NULL in an expression made the entire thing, eventually, FALSE.<p>Naturally I went back and made the comparison operators always return booleans. But it was a fun little side effect at the time.

I have deep but vague thoughts around the concept. My first intuition is that we have put too many things under NULL and None and such.<p>Partially, we use ... and I&#x27;ll be very broad here ... &quot;variables&quot; as boxes we look in for answers. Answers to questions, answers to &quot;Hey I put something in there for the time being to reference later.&quot; If I went into programming terms rather than just SQL, sometimes we get meta-answers.<p>You haven&#x27;t made the box yet (declared the variable).<p>You haven&#x27;t decided how the box is structured (picked a type or a length or something).<p>Okay, you did those but the box is <i>virgin</i> (nothing has been placed in the box yet).<p>That kind of thing. An empty set for &quot;yes, you asked but nothing meets those criteria.&quot;

It is not supposed that null is the bottom value in the universe of all the values that your program can recognize? Why people need to complicate it?, and yeah in that definition `null == null`, but a `null_pointer != null` because null pointer is at the bottom of all the possible pointer value, and null by itself is not a pointer. The same for (0,null), (false, null) and (&quot;&quot;, null). null should only be equal to itself.<p>And lastly undefined != null, because undefined is related with structures indicating that a field was not defined when the structure was created

NULL is the absence of a value. If you try and treat it as a value, you&#x27;re going to have a bad time. So an attempted UNIQUE(email_address, deleted_at) constraint is fundamentally flawed. If you treated NULL as a value that could be unique, you&#x27;re going to break foreign keys.<p>But let&#x27;s continue the logic of deleted_at being NULL indicating an active account, which seems to the intent here. You end up doing things like:<p><pre><code> SELECT &#x2F;* ... *&#x2F; FROM accounts WHERE email_address = &#x27;...&#x27; AND deleted_at IS NOT NULL </code></pre> Depending on your database, that may or may not index well. More problematic, you may end up with privacy leaks if someone forgets the last conditional.<p>If anything, you want to reverse this so someone has to go out of their way to explicitly select deleted accounts. There are multiple strategies for this eg using an active_accounts view or table.<p>Lastly, there are lots of potential reasons for an account to be disabled or otherwise not visible&#x2F;accessible. Takedowns, court orders, site safety, hacked accounts and so on.<p>Overloading deleted_at to have a semantic meaning for an active account is just fundamentally bad design.

SQL NULL is also coerced to different boolean values depending on context e.g. in a WHERE clause NULL is coerced to false, whereas in a CHECK constraint NULL is coerced to true<p><a href="https:&#x2F;&#x2F;dbfiddle.uk&#x2F;C5JqMP8O" rel="nofollow">https:&#x2F;&#x2F;dbfiddle.uk&#x2F;C5JqMP8O</a>

That&#x27;s why I like EdgeDB and EdgeQL: no null [1]<p>[1] <a href="https:&#x2F;&#x2F;www.edgedb.com&#x2F;blog&#x2F;we-can-do-better-than-sql" rel="nofollow">https:&#x2F;&#x2F;www.edgedb.com&#x2F;blog&#x2F;we-can-do-better-than-sql</a>

And SQL null shares the feature with many other languages that any type can be a NULL (although a column can be set NOT NULL). Much like Java, it is no end of grief that a type that claims to be, say, a “NUMBER” is actually “NUMBER | NULLType”.

All of this would be avoided if NULL in sql was just called &quot;UNKNOWN&quot;. Which is what it is. Terrible name :&#x2F;<p>Imo, SQL should add &quot;NOTHING&quot;, add &quot;UNKNOWN&quot; as a synonym for &quot;NULL&quot;, and deprecate &quot;NULL&quot;.

null != null is pretty bizarre at first, until you understand the reason the did it was to try to make sense of null-able indexed columns. Not sure why we couldnt have our cake and eat it, but instead we got IS NOT NULL is not the same as != NULL

To me, &quot;Unknown&quot; almost implies the possiblity of a value, whereas I&#x27;ve always thought of NULL as being an absence of a value.<p>edit: an empty string, false, 0 are all values.

The simplest end-run around this is to avoid NULLs entirely, which normalization (even just the first normal form) requires.

Weirder still are floating point numbers in SQL.

I think (blasphemous hot take ahead) that the standards of implementation of relational models are wrong.<p>NULLs still have their (rare) place, but the foremost issue with query results is that they are tabular rather than hierarchical. The main culprits being (1) outer joins that represent or induce nonsensical operations and (2) lack of non-null “zero” values for types like date. Of course hierarchies can make querying more complex, but mostly in cases where the relational logic goes crazy itself and you had to go tabular anyway.<p>If you think of it, distinct, group by and windowing feel like workarounds in tabular mode but would be natural to hierarchies, because everything is naturally distinct and grouped-by by design and windows are basically subtables in these rows.<p>Bonus points you could fetch “SELECT FROM a, b_rows LEFT JOIN b AS b_rows …” in a single query without duplicating `a`s and nullifying `b`s when N &lt;&gt; 1. And when you aggregate through a column in `b`, there’s no headache what to do with join-produced NULLs (unless `b` columns are nullable by your design, then it’s on you). And when it all arrives to a client, it’s already well-shaped for ui, processing, etc. No more:<p><pre><code> last_a_id = undefined for (row of rows) { if (row.id != last_a_id) { … last_a_id = row.id } … } </code></pre> I’m pretty sure you recognize this programming idiom immediately.<p>Before you criticize, I’m not talking about hierarchical&#x2F;OO tables. Only about ways of getting and handling query results. You still can reshape a relation like you want. The difference is that a database engine doesn’t have to put it all onto a (N x M x …) table and instead creates sort of a subset of relations which is efficient in space and natural to walk through. It already does that when walking through indexes, selects are naturally hierarchical. All it has to do is to track relations it went through rather than just dumping rows from a set of cursors that it <i>knows</i> the start and end points of, but loses this knowledge by writing into a plain table.

Ah yes, someone discovering the existence of three value logic in SQL and expecting 2VL behavior. Classic. We&#x27;ve all been there, right?<p>Personally I wish more languages were like python or ruby and had chosen None or Nil over Null which alleviates the confusion a bit, as those names better indicates that it&#x27;s NOT an &quot;unknown&quot; (1 unknown value != 1 other unknown, which intuitively makes sense.) In ruby or python it&#x27;s more obvious that None and Nil are &quot;nothing&quot; types and therefore equivalence makes sense (nil == nil, None == None are both true)

The NULLs in unique constraints quirk actually works differently in ORACLE databases, which is infuriating to say the least. Apparently this comes from some ambiguity in some sql standard, anyone know more about this?