<p>In SQL, equality involving <code>null</code> always returns <code>null</code> regardless of the other operand; for example, all of the following evaluate to <code>null</code> in SQL:</p>
<pre class="lang-sql prettyprint-override"><code>0 = null
null = null
0 &lt;&gt; null
null &lt;&gt; null
</code></pre>
<p>SQL requires the use of the <code>is null</code> or <code>is not null</code> operator to test for a <code>null</code> value for a nullable variable, which return <code>true</code> or <code>false</code>; for example, <code>null is null</code> is <code>true</code> while <code>null is not null</code> is <code>false</code>.</p>
<p>In contrast, all mainstream programming languages which support <code>null</code>, have their equality operators behave in the usual and predictable way, treating <code>null</code> no different than any regular values. For example, in Java, equality to <code>null</code> behaves just like equality to any other values:</p>
<pre class="lang-java prettyprint-override"><code>final String x = &quot;&quot;;
x == null; // false
null == null; // true
x != null; // true
null != null; // false
</code></pre>
<p>For both <code>null</code> in SQL and <code>null</code> (or its equivalent) in mainstream programming languages, it is a placeholder for an absence of value. In fact, when we process data returned from SQL databases in a programming language with <code>null</code> value support, a <code>null</code> from SQL is returned as a <code>null</code> in the other programming language. Why are they treated differently in SQL and in other programming languages in equality operators?</p>

<nullability>
Why does SQL treat null = null as null, unlike all other mainstream programming languages where null = null is true?
CC BY-SA 4.0
2023-12-21T16:17:08.267Z
The same is true for the null arithmetic value (usually called "NaN") in many languages, so SQL is hardly unique here.
Toby Speight
CC BY-SA 4.0
2023-12-21T16:42:01.160Z
SQL uses null for conflated purposes.  Sometimes it means missing data, and other times it means not applicable.
Erik Eidt
CC BY-SA 4.0
2023-12-21T18:52:08.243Z
@TobySpeight Not really; `NaN == NaN` is *false*, not NaN.
Karl Knechtel
CC BY-SA 4.0
2023-12-21T18:55:49.670Z
@KarlKnechtel That's more due to technical limitations than an intentional design. IEEE is meant to apply to a wide variety of systems that may not be able to easily support a value other than true or false
mousetail 'he-him'
CC BY-SA 4.0
2023-12-21T22:49:07.377Z
@KarlKnechtel But the main point is that it's not *true* even though it seems like it should be.
Barmar
CC BY-SA 4.0
2023-12-21T23:24:27.920Z
@ErikEidt I think *most* languages use the same value/type for both of those purposes - if you don't know the price of an item in Java, you might well return `null` from `item.getPrice()`  - and does it make sense for two such unknown prices to compare as "equal"?
IMSoP
CC BY-SA 4.0
2023-12-22T12:45:41.123Z
If there is a person table where few people did not specify their age (age = null) then they wont be selected in this `select * from person where age > 18` nor `select * from person where age <= 18`. It makes sense, doesn't it? I mean what would be the criteria of putting people with age = null in exactly one of `<= 18, > 18` groups?
Salman Arshad
CC BY-SA 4.0
2023-12-22T13:09:34.900Z
In a programming language with strong typing, comparing a nullable item (`age`) with a comparison operator is an error, because comparison operators are meaningless on `null`.

If the same design is applied in SQL, you won't be able to type `age > 18` if `age` is a nullable column, and you are forced to handle it separately.
Michael Tsang
CC BY-SA 4.0
2023-12-22T13:20:15.753Z
Well c# allows it and the result is roughly similar to SQL.
Salman Arshad
CC BY-SA 4.0
2023-12-22T15:08:44.213Z
@MichaelTsang Raising an error is certainly an alternative strategy, but not one I've seen implemented very commonly for simple comparisons. For instance in C#, this outputs "equal": `String x = null; String y = null; Console.WriteLine((x == y) ? "equal" : "not equal");` and this outputs "not equal": `String x = "hello"; String y = null; Console.WriteLine((x == y) ? "equal" : "not equal");`
IMSoP
CC BY-SA 4.0
2023-12-22T17:14:01.423Z
@IMSoP: I think "relational comparison" was meant, not "equality comparison"
Ben Voigt
CC BY-SA 4.0
2023-12-22T17:24:09.353Z
SQL tries to implement 3-valued logic, like that of Kleene 3VL. There have been proposals for separate "missing" and "NA", but I don't think any of those 4VLs have taken off.
qwr
CC BY-SA 4.0
2023-12-22T17:43:15.580Z
@IMSoP: You replied to a comment by MichaelTsang saying comparisons don't work, with `age > 18` as an example.  Your response was (to paraphrase), sure comparisons do work, with `x == y` as an example.  You substituted Michael's relational comparison with an equality test.  (C# does support relational comparisons on nullables, as "lifted operators", with some non-obvious semantics)
Ben Voigt
CC BY-SA 4.0
2023-12-22T17:53:45.520Z
@BenVoigt Ah, right; the word "relational" confused me given the context of the conversation. It makes no difference, though, for nullable types where `<` is defined, C# happily applies it to null values: https://dotnetfiddle.net/DWJs0l My point is that while I'm sure there are languages which throw errors for operators acting on null values, it's definitely not universal in any language with strong typing, as implied by the comment I was replying to.
IMSoP
CC BY-SA 4.0
2023-12-22T21:10:16.183Z
*"In contrast, all mainstream programming languages which support null, have their equality operators behave in the usual and predictable way, treating null no different to any regular values."* Counter example: VBA and Visual Basic.NET. Granted, they are not the most popular languages, but I guess they'd count as "mainstream".
Heinzi
CC BY-SA 4.0
2023-12-24T12:20:14.123Z
@qwr, Codd himself tried to make a 4VL-style logical system which distinguished "missing" and "inapplicable". One problem is that it produces a vast and unmanageable number of possible logical operators. But in my opinion, the main reason it doesn't work is because the programmer often doesn't actually know whether the information is missing or inapplicable, so you end up with known-missing, known-inapplicable, and unknown, and sometimes whether something is missing or inapplicable depends on the use/purpose of the query.
Steve
CC BY-SA 4.0
2024-01-02T22:45:27.700Z
@Steve: How would known-missing be different from existing "nothing to see here" values like `FALSE`, `0`, or `''` (empty string)?
dan04
CC BY-SA 4.0
2024-01-03T08:30:06.347Z
@dan04, it would be different from those values in the same way that Null in SQL (which represents all three of known-missing, known-inapplicable, and unknown) is different from them. Your question is potentially confusing to me, because I'm not sure whether you are asking specifically about some subtlety of Codd's 4VL idea, or whether you are asking a very general question about why it is useful to have a separate Null value in SQL?
Steve
CC BY-SA 4.0
2024-01-03T10:19:27.407Z
@Steve: I *do* see the value in distinguishing between absence of knowledge (e.g., `MiddleName = NULL` when we don't know a person's middle name) and knowledge of absence (`MiddleName = ''` when we've confirmed that they don't have one).  What I (and apparently the designers of SQL) don't get is the need for a third type of "nothing" that's neither of those things.
dan04
CC BY-SA 4.0
2024-01-03T14:16:52.347Z
@dan04, if you read all my contributions on this question, including my answer and my various comments, you'll see that I don't accept that Null represents only "missing" or "unknown" information. It is actually a multi-purpose value that represents many meanings. As I say in my comment above, it is standard to consider that Null breaks down into "missing" and "inapplicable". But in my view, there is a third case of how Null is used, which is where the meaning is certainly either one of missing or inapplicable, but the organisation doesn't and cannot know which. (1/2)
Steve
CC BY-SA 4.0
2024-01-03T14:18:07.110Z
In my answer, I give the example of a vet treating an injured dog, and not knowing whether it is a dog whose owner is unknown for the time being, or an unowned (wild) dog. What unites all these different cases or meanings, is the need usually for some kind of special handling. That's what Null provides in SQL. Many people get hung up on what Null means about the world, rather than what it means for the automatic data processing which the database performs. Has that clarified anything? (2/2)
Steve
<p>For booleans, NULL means &quot;the whole Boolean domain&quot;, or the set {true, false}</p>
<p>In SQL, the expression <code>TRUE OR NULL</code> is true (not a null, as some might expect), and the expression <code>FALSE AND NULL</code> is false.</p>
<p>If we treat the expression <code>TRUE OR X</code> as a boolean function over the boolean X, its domain is {true, false} and its image is {true}. This function is a constant which doesn't depend on its input. When passed NULL, which we can treat as &quot;any value from the Boolean domain&quot;, its result is still true, that's why it evaluates to true.</p>
<p>If we take something like <code>TRUE AND X</code>, whose image is {true, false}, and pass it &quot;any Boolean value&quot;, the result can be &quot;any Boolean value&quot; as well, i.e. NULL.</p>
<p>The NULL comparison behavior seems to be the remnant of a half-assed attempt to extend this logic to other types. For instance, <code>10 NOT IN (NULL, 5)</code> will evaluate to NULL (because we can make it both true and false, depending on what concrete integer we put instead of the NULL), but <code>10 NOT IN (NULL, 10)</code> will evaluate to false (because no matter what other value we put instead of NULL, it will always be false).</p>
<p>In the same vein, <code>NULL = NULL</code> can be made either true or false by substituting different concrete values on either side of the comparison, so it evaluates to NULL.</p>
<p>Of course, had this logic been seen through, we would expect something like <code>0 * NULL</code> to evaluate to 0 and <code>COUNT(NULL)</code> evaluate to the same thing as <code>COUNT(*)</code>, but it didn't happen (nor it realistically could, if you start thinking about finer details, like what should <code>a - a</code> evaluate to).</p>
<p>The operator <code>x IS NOT DISTINCT FROM y</code> returns true for two nulls, and so does <code>EXISTS (SELECT x INTERSECT SELECT y)</code> which I use a lot in SQL Server.</p>

CC BY-SA 4.0
2023-12-22T01:24:39.140Z
2023-12-22T01:24:39.140Z
Quassnoi
CC BY-SA 4.0
2023-12-22T08:12:36.190Z
I don't think your examples justify the label "half-assed". Multiplying by zero is an interesting edge case which could be defined that way, but wouldn't be very useful in practice. The behaviour of `COUNT` is perfectly logical: the *only* thing it does with its optional argument (where `COUNT(*)` can be seen as a funny way of writing it with no argument) is examine whether it is null or not; if it didn't do that, there would be no point in the argument at all.
IMSoP
CC BY-SA 4.0
2023-12-22T09:13:56.313Z
@IMSoP: what you are saying is true, but I fail to see how it makes this behavior "perfectly logical". To be consistent with the logic of `IN`, the aggregate functions should be instantly poisoned by a single null, and if you wanted to skip the null values, you would need to filter them out in the `WHERE` clause. I'm not saying it would be better or easier to use, just that it's inconsistent.
Quassnoi
CC BY-SA 4.0
2023-12-22T09:56:54.790Z
I didn't say it was "perfectly logical", just that it wasn't "half-assed". I think the clearest description of aggregate behaviour is that the aggregate itself is never examining `NULL` values; rather, the definition of which values to feed into it is "values which aren't null". If you wanted to model them as set-operating functions, you could say that SQL's `COUNT(x)` is defined as `count_items( filter_nulls( evaluate_per_row(x) ) )`. In other words, it's more a short-hand in the way the _syntax_ of the language works, rather than in the _semantics_ of the operations.
IMSoP
CC BY-SA 4.0
2023-12-22T11:08:10.640Z
@Quassnoi, we had an interesting discussion of this a few months ago on SE.SE. The basic principle is that scalar operators propagate nulls, and aggregate operators eliminate nulls. The reason for this distinction in approach is because each caters to the most common use, *and* the opposite behaviour is easily achieved with a few extra words of syntax localised to the operator in question. If either kind of operator worked like the other, then the current behaviour which each kind of operator has becomes extremely difficult to achieve. So any extra consistency would be foolish.
Steve
CC BY-SA 4.0
2023-12-22T12:54:04.713Z
@Steve Yes, I hadn't thought before just how difficult the opposite would be! Given my pseudo-code above, you can easily change the expression `x` to never evaluate to `null` (e.g. with `coalesce`), but if the expansion was `count_items( evaluate_per_row(x) )`, there would be no change you could make to `x` that would emulate `filter_nulls`, because the expression is evaluated _per row_. `AVG(x)` is probably a better example, since its value depends on both the number of items in the set and their values, so there's no natural value that you can add to the set which doesn't affect the result.
IMSoP
CC BY-SA 4.0
2023-12-22T14:03:44.380Z
@IMSoP: the behavior of `NOT IN` with nulls is probably _the_ most confusing thing in whole SQL. People expect it just to filter out the nulls, i.e. do what aggregates do, and I don't think I've ever seen the case where the defined behavior of `NOT IN` would be actually useful. The same is true for `GREATEST` and `LEAST`. The latter is not even consistent across platforms, i.e. PostgreSQL and SQL Server do the right thing and ignore the nulls, Oracle doesn't. The unintuitive behavior is kept solely because of the legacy code. This is what I mean by "half-assed"
Quassnoi
CC BY-SA 4.0
2023-12-22T15:00:49.440Z
@Quassnoi Unlike with aggregates, you have full control over the set passed into the right-hand side of `IN` and `NOT IN`, so they act as pure set-wise operations - `x IN (a,b)` could be written `x = ANY( [a,b] )`, or `x=a OR x=b`. `x NOT IN (a,b)` is the complement of that, which is `x <> ALL( [a,b] )`, or `x<>a AND x<>b`. In my experience, people don't find [De Morgan's laws](https://en.wikipedia.org/wiki/De_Morgan%27s_laws) intuitive, so extending them to 3-valued logic is probably why `NOT IN` trips so many people up.
IMSoP
CC BY-SA 4.0
2023-12-22T15:02:56.543Z
@Quassnoi, I'm sure I occasionally use `NOT IN` on non-nullable columns - so it's not completely without usefulness - but it's become second-nature to me to scrutinise filtering very carefully whenever nullable columns are involved. It's certainly an area of the language that feels like it could withstand some kind of improvement.
Steve
CC BY-SA 4.0
2023-12-22T15:07:06.760Z
@IMSoP: what do you mean by "full control"? `NOT IN (SELECT field FROM table)` cannot evaluate to true if there's but a single null in the result of the query. How is control on this query "unlike" control on the query driving aggregates?
Quassnoi
CC BY-SA 4.0
2023-12-22T15:12:15.963Z
@Quassnoi Because the argument `(SELECT field FROM table)` is an expression which returns a set, and you can choose what's in that set, e.g. with a `WHERE` clause. With an aggregate, the argument is an expression that returns a _single value_, and you have no control over when it's evaluated - in `SELECT AVG(x), AVG(y) FROM z` you can't choose a different set of `x` values and `y` values in order to explicitly filter nulls before passing to `AVG`.
IMSoP
CC BY-SA 4.0
2023-12-22T19:09:07.117Z
The logical comparison behavior is like Kleene 3VL. It doesn't apply for set and union or joins though.
qwr
CC BY-SA 4.0
2023-12-22T19:49:09.060Z
@IMSoP: in e.g. PostgreSQL, you can both filter the aggregates and use tuples in `IN` queries: `(1, 2) IN (SELECT a, b FROM mytable)`. My point was that `IN`, `GREATEST` and other variadic functions should behave like aggregates, and `IS DISTINCT FROM` should have been there from the beginning (maybe as `==` or something like this). A function for a scalar sum (like `a + b + c + d`  but ignore nulls) is also something that I would need more often than the default poisonous null behavior. Something like `a FULL JOIN b FULL JOIN c` is a pain to write if your system doesn't support `USING`…
Quassnoi
CC BY-SA 4.0
2023-12-22T19:52:18.697Z
@IMSoP: … and so on and so forth. In my book, this all falls under the definition of a "half-assed design", is all I'm saying. I can totally see how it wound up to be the way it is, but it doesn't make it any better.
Quassnoi
CC BY-SA 4.0
2023-12-22T20:36:06.687Z
@Quassnoi My point is that for an aggregate, you have to introduce extra syntax to express the filter, because by default you're only specifying what should happen on each row, not which rows to consider. The designers of SQL could have required us to write `AVG(x WHERE x IS NOT NULL)` but they decided it was simpler both for the user and the implementation to assume that by default, and let us write `AVG(COALESCE(x, 0))` for the opposite case. With functions that take a rowset as their argument, the language already has all the facilities we need to apply whatever filter we want.
IMSoP
CC BY-SA 4.0
2023-12-22T20:45:48.243Z
@Quassnoi I don't agree that there's a necessary equivalence between aggregates and rowset-consuming functions / operators. To define a custom aggregate in Postgres, you don't create a function that takes a rowset as input, you create a function that accepts each value in turn, along with a carried partial result; this has the useful effect of also making them available as window functions. I see nothing "half-assed" about including both set-wise functions and iterative aggregates in the language, and choosing useful semantics for each.
IMSoP
<p>In an outer join (<code>A LEFT JOIN B</code>), a null value in the table <code>B</code> could mean the value is nonexistent. That means the whole row shouldn't exist in an inner join. If someone adds more restrictions about the values in table <code>B</code> after the join (in the <code>WHERE</code> or <code>HAVING</code> part instead of the <code>ON</code> part) without null checks, it effectively falls back to an inner join, that is, making the extra rows in an outer join always discarded, by implementing comparison this way.</p>
<p>I can't think of a good example for now. But think about saving the result of an outer join in another table. It makes sense to keep the semantics of the data unchanged and accessible. You shouldn't get another row if you just compared two different indicators about the row should be nonexistent, if a single indicator worked as expected.</p>
<p>In SQL, nulls work like unknown / partially, somewhere between true and false, in logical operations. That is, <code>NULL AND TRUE</code> is null, but <code>NULL AND FALSE</code> is false. The null result from equality comparison matches
the result of xnor (<code>(NULL AND NULL) OR (NOT NULL AND NOT NULL)</code>). So it's not really something unexpected.</p>

CC BY-SA 4.0
2023-12-22T08:37:08.753Z
2023-12-22T08:37:08.753Z
user23013
CC BY-SA 4.0
2023-12-26T19:33:02.923Z
From the T-SQL docs on joins: The results do not make it easy to distinguish a NULL in the data from a NULL that represents a failure to join. When NULL values are present in data being joined, it is usually preferable to omit them from the results by using a regular join.
qwr
CC BY-SA 4.0
2024-01-01T20:39:49.553Z
@qwr If you use nulls in the data with other semantics in mind, most likely the semantics in the language making queries, and don't consider their semantics in SQL, it's always better to just match the logic of the other language, making any argument moot. But as SQL has ways to generate nulls, it's more productive to think in the SQL way and use nulls to store data with similar semantics, and consider values not fit different kinds of special values. It's not so productive to avoid using SQL features because you are using them as placeholders for values not even fit in the SQL logic.
user23013
CC BY-SA 4.0
2024-01-01T20:40:23.050Z
@qwr And if you really do follow that recommendation, that you don't use outer joins at all when nulls are present in the data, it means as long as you use outer joins, nulls are only used for what I wrote and you don't need to consider anything else, as they are not present elsewhere. So I don't quite understand how it changes anything in my answer.
user23013
<p>Most programming languages implement Boolean, or 2-valued logic, with the familiar TRUE and FALSE. SQL implements a <strong>3-valued logic</strong> with TRUE, FALSE, and UNKNOWN. In most SQL DBs, <a href="https://stackoverflow.com/questions/41180547/is-sql-unknown-identical-to-null">NULLs are treated in comparisons as UNKNOWNs</a> (I believe, T-SQL might be different).</p>
<p>When we use the logical operators AND, OR, and NOT, we sometimes get the same output if we replaced UNKNOWN with TRUE and separately replaced UNKNOWN with FALSE.</p>
<ul>
<li>For example, for <code>TRUE OR UNKNOWN</code>, replacing UNKNOWN with TRUE and replacing UNKNOWN with FALSE both still result in the expression evaluating TRUE.</li>
<li>On the other hand, <code>TRUE AND UNKNOWN</code> would be TRUE if the UNKNOWN were TRUE, and FALSE if the UNKNOWN were FALSE.</li>
</ul>
<p>Just like for 2VL logical operations, we can create truth tables for 3VL that match this &quot;unknowns logic&quot;, a subset of Kleene's K3 logic. Since these operations are commutative, I'll present the main cases:</p>
<div class="s-table-container">
<table class="s-table">
<thead>
<tr>
<th>A</th>
<th>B</th>
<th>A AND B</th>
<th>A OR B</th>
<th>A = B</th>
</tr>
</thead>
<tbody>
<tr>
<td>TRUE</td>
<td>UNKNOWN</td>
<td>UNKNOWN</td>
<td>TRUE</td>
<td>UNKNOWN</td>
</tr>
<tr>
<td>UNKNOWN</td>
<td>UNKNOWN</td>
<td>UNKNOWN</td>
<td>UNKNOWN</td>
<td>UNKNOWN</td>
</tr>
<tr>
<td>FALSE</td>
<td>UNKNOWN</td>
<td>FALSE</td>
<td>UNKNOWN</td>
<td>UNKNOWN</td>
</tr>
</tbody>
</table>
</div>
<p>The symmetry is reminiscent of the symmetry in Boolean logic.</p>
<p><code>=</code> in SQL treats all NULLs as distinct, so <code>A = NULL</code> is always UNKNOWN. Use <code>A IS NULL</code> to check if A is populated with NULL. <code>NOT UNKNOWN</code> is <code>UNKNOWN</code>.</p>
<div class="s-table-container">
<table class="s-table">
<thead>
<tr>
<th>A</th>
<th>NOT A</th>
<th>A IS NULL</th>
</tr>
</thead>
<tbody>
<tr>
<td>TRUE</td>
<td>FALSE</td>
<td>FALSE</td>
</tr>
<tr>
<td>UNKNOWN</td>
<td>UNKNOWN</td>
<td>TRUE</td>
</tr>
<tr>
<td>FALSE</td>
<td>TRUE</td>
<td>FALSE</td>
</tr>
</tbody>
</table>
</div>
<p>This niceness goes out the window when it comes to other syntax. <code>WHERE</code> will only return rows when the search condition is TRUE, not for FALSE nor UNKNOWN. For a left (outer) join, for any rows from the left table that don't have a match in the right table, the right table rows get a NULL value. From the <a href="https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver16#nulls_joins" rel="nofollow noreferrer">T-SQL docs on joins</a></p>
<blockquote>
<p>The results do not make it easy to distinguish a NULL in the data from a NULL that represents a failure to join. When NULL values are present in data being joined, it is usually preferable to omit them from the results by using a regular join.</p>
</blockquote>
<p>See also <a href="https://stackoverflow.com/questions/53956546/how-null-values-are-treated-in-union-union-all-in-mysql">How NULL values are treated in UNION and UNION ALL</a>. I will defer to other answers on this, as I always have to look up how different SQL keywords handle NULL. It is not intuitive.</p>
<hr />
<p>If you think about NULL carefully, you'll realize it serves at least two purposes in SQL: 1. unknown/missing data, and 2. not applicable data. (3. NULLs introduced by joins.) This opens the door for having separate UNKNOWN and NA values, and 4-valued or further many-valued logic, but that gets even more complicated. Lots of literature has been written about SQL's treatment of NULL and why it is not intuitive. See <a href="https://langdev.stackexchange.com/questions/3411/why-does-sql-treat-null-null-as-null-unlike-all-other-mainstream-programming?noredirect=1#comment9655_3411">Steve's insightful comment on why this is worse.</a></p>

CC BY-SA 4.0
2023-12-26T03:11:56.990Z
2023-12-27T22:47:13.040Z
2023-12-27T22:47:13.040Z
qwr
CC BY-SA 4.0
2023-12-28T10:46:29.660Z
I've found out why so much Microsoft documentation refers to `UNKNOWN` despite no relevant use of that keyword in TSQL. It's because the ISO/IEC 9075 standard itself uses the Unknown term. But the standard also states that Unknown is synonymous with Null typed as the Boolean/Bit data type.
Steve
CC BY-SA 4.0
2023-12-29T00:26:33.750Z
@Steve yeah it's hard for me to find out exactly when SQL treats NULL as UNKNOWN. NULL could be in any data type and SQL implementations don't have to implement their Boolean type.
qwr
<p>Simply put, in the world of SQL, <code>NULL</code> means that the value is unknown. It <em>can</em> mean that the value simply doesn't exist, like it does in other languages. But, since it can mean that the value is unknown, then comparing two items, where one or both are of an unknown value (<code>NULL</code>) results in an unknown value--<code>NULL</code> in SQL.</p>

CC BY-SA 4.0
2023-12-22T15:01:23.773Z
2023-12-22T15:01:23.773Z
Jim
<p>I find it can be quite fiendish to explain this aspect of SQL convincingly, because of the sheer depth of an explanation which fully justifies the design and rebuts superficial objections. I don't know whether I have the capability to deliver that explanation.</p>
<p><strong>Relational Algebra</strong></p>
<p>The first thing to mention is EF Codd's Relational Algebra. Although SQL and RA are not completely synonymous, RA was the main theoretical foundation for the design of the SQL language.</p>
<p>Some of the main conceptual features of RA is the &quot;relation&quot; (what in SQL is simply called a &quot;table&quot;), the &quot;relational operators&quot; (including the &quot;joins&quot;), the &quot;Null&quot; value, and a system of so-called 3-valued logic (including a certain approach to handling the Null value).</p>
<p><strong>3VL</strong></p>
<p>I'll use &quot;3VL&quot; as a shorthand for referring to the system of both the Null value itself and the handling of it by operators.</p>
<p>It's a bit of a misnomer here in the sense that Null is a value which is available in the domain of all SQL data types, including those which have many more than 3 possible values. It is not simply limited to supplementing the Boolean/logical/bit type with a third value. But there's no better and more commonly understood term than &quot;3VL&quot;.</p>
<p>There are also many more confusing details, though not necessary to examine for this answer.</p>
<p><strong>Joins</strong></p>
<p>The way the join operators work in RA and SQL depends inextricably on 3VL.</p>
<p>Firstly, each join operation can produce Null values to represent the case where certain rows in input tables were not joined.</p>
<p>Secondly, each join operator can have Null values in its inputs. This may be either because these Nulls exist in static data, or because a previous join operation (in a query consisting of more than one join) has produced Nulls at an earlier stage.</p>
<p>The only sensible behaviour of the join operators, is that Nulls do not join to one another. Therefore, at least in the context of the join control expressions, Null compared to Null must be false.</p>
<p><strong>Justification of how joins work</strong></p>
<p>This might not seem very intuitive or obviously correct at first glance.</p>
<p>But there are two main justifications.</p>
<p>The first is that join operators with this exact behaviour have important algebraic properties (hence the name of the theoretical framework, &quot;Relational Algebra&quot;), and these algebraic properties are crucial for optimisation and performance when dealing with &quot;large shared data banks&quot; (as EF Codd described what we now describe as &quot;databases&quot; serving typical &quot;OLTP loads&quot;). Alterations to the behaviour of the join operators potentially destroy their algebraic properties, and with it the crucial optimising capability.</p>
<p><strong>The meaning of Null</strong></p>
<p>The second justification relies on explaining what Null means, and how it is used in practice.</p>
<p>To many programmers who are new to SQL but familiar with other languages, the word &quot;Null&quot; is what linguists call a &quot;false friend&quot;. There is no analogy in any mainstream programming language, for how Null works in SQL.</p>
<p>Typically in other languages, Null is associated with the &quot;null pointer&quot;, which is invariably the zero-valued pointer on any hardware architecture I'm familiar with. Not so in SQL, which has no concept of memory pointers in its syntax, and where Null definitely does not mean zero.</p>
<p>The Null value in SQL broadly represents the same thing as a &quot;blank space on a paper form&quot;. That is, it's meaning is very ambiguous and inconsistent, but it broadly means either &quot;<em>missing</em>&quot; or &quot;<em>inapplicable</em>&quot;.</p>
<p>&quot;Missing&quot; broadly means that a certain value should be recorded or relates to a fact that is capable of being recorded, but for whatever reason isn't recorded.</p>
<p>&quot;Inapplicable&quot; broadly means that a certain field is somehow not applicable to a particular case. For example, a vet might typically record a dog's &quot;owner name and address&quot;, but if the dog is a stray born in the wild and has no owner, then the owner name and address is inapplicable to the vet's record about that dog.</p>
<p>Very often, it may not be clear whether data is missing or inapplicable - for example, at the time of veterinary treatment, it may not be possible to distinguish between a dog which has an unknown owner, and an unowned dog. On a paper form, blank would be left for either case.</p>
<p>But there is one consistent thing about &quot;paper-blank&quot;, which is that a blank on one paper form doesn't mean an association with blanks on other paper forms. If you have a series of forms with names missing from many of them, this doesn't mean all the ones with blank names belong to the same person (who has no name). They almost certainly belong to different people, all of whose names happen not to be recorded on the forms.</p>
<p>If you understand that analogy, you understand why Null doesn't join to Null in SQL. Because blanks don't connect to blanks when dealing with paper records.</p>
<p><strong>Records and keys</strong></p>
<p>There is an underlying conceptualisation here which is about &quot;records&quot; and &quot;keys&quot; - the concept of which predates SQL, RA, and is a common practice when dealing with paper records.</p>
<p>What you commonly have with business records are linkages between different records based on &quot;keys&quot; - for example, a customer account number is a &quot;key&quot;. When a customer places an order, you record the details of the order on the form, and you also record a &quot;key&quot; on the same form which is the customer account number. Detailed information about the customer account, and which defines the &quot;key&quot; for that particular account, will be recorded separately from information about each order.</p>
<p>The use of the key on the order forms means that all orders can be linked via the account number. This linkage allows a business to do certain things which depend on organising or analysing all the orders of a particular account together, like controlling the total amount of credit extended to a particular customer.</p>
<p>Now, if there are order forms with no customer account number recorded, these do not link to a single customer account whose key is &quot;blank&quot;. Rather, the blank means those forms are unassociated with any customer account - that the customer account is <em>missing or inapplicable</em>.</p>
<p>So that's what practice the join operators in SQL are reflecting. I hope at this stage I've explained why Nulls shouldn't join to Nulls, and by implication, why Null doesn't compare equal to Null.</p>
<p><strong>Why the equals operator is defined as it is</strong></p>
<p>Because SQL was designed primarily around it's relational algebra capability and the concept of joining tables, as well as the 3VL concept, the designers have prioritised terseness when using that functionality, and comparisons involving Nulls (such as equality using <code>=</code>, but also including the other standard comparison operators) are never true.</p>
<p>Instead, when Nulls must be specifically compared, the special <code>IS NULL</code> operator is used.</p>
<p>It is still possible to perform all kinds of comparisons in SQL. It is simply more long-winded to write comparison expressions which treat Nulls as equal, such as having to write <code>(x = y) OR (x IS NULL AND y IS NULL)</code>.</p>
<p><strong>A final word of warning</strong></p>
<p>You might find explanations in this area which attempt to describe Null as &quot;the absence of a value&quot;.</p>
<p>In my view, that does not describe the reality. Null is very much an explicit value, because computers only work with values (or symbols) and cannot encode or process pure non-values, but it is a value which is frequently (not always) used to represent the fact that there was an absence of recordable information available when the computer record was made.</p>
<p>You might also see explanations of how Null comparison is handled, which involve saying that you can't tell whether two Nulls are different or not.</p>
<p>In many cases, this begs the question of whether Null is in fact being used to represent missing/unknown information. If Null is used in a capacity of being a marker of inapplicability, or as a default value, then there is no natural reason why these markers should not be considered equal.</p>
<p>But it's also a red herring. The main explanation for the behaviour of Null is how it integrates with the behaviour of the join operators, and how those join operators model the linkages between records, where the presence of Null should almost always mean &quot;do not join&quot; (without necessarily implying missing information).</p>

CC BY-SA 4.0
2023-12-22T06:49:55.837Z
2023-12-22T06:55:46.257Z
2023-12-22T06:55:46.257Z
Steve
CC BY-SA 4.0
2023-12-22T09:39:04.120Z
In programming languages with dynamic typing, such as PHP or JavaScript, `null` (also `undefined` in JavaScript) is also used as a placeholder for a missing or inapplicable value in place of any data type as well. Their type system (Typescript for JavaScript) explicitly defines a nullable type as a union of the original type and `null`, a data type itself.
Michael Tsang
CC BY-SA 4.0
2023-12-22T11:34:18.477Z
@MichaelTsang, yes many languages have something called null and it is used for some of the same purposes. But the overall mechanics are quite different. Also, I'm not quite sure whether SQL treats nullable fields as being union types. On the whole, I'd be inclined to say it treats the domains of all data types as intrinsically containing a null value - rather than thinking of it as being like a conventional data type plus a bolted-on null value. These kinds of subtleties are why a so-called "impedance mismatch" occurs between SQL and other conventional programming languages.
Steve
CC BY-SA 4.0
2023-12-22T13:04:29.943Z
@Steve Treating null as an implicitly valid value for all data types is actually quite common, particularly in languages which expose pointers, or make explicit the concept of "reference types". Often, you can actually have null pointers of different types. In that sense, SQL is treating it _more_ like a union type, since there is only one type of `null`, and operations are overloaded to give specific results for that type.
IMSoP
CC BY-SA 4.0
2023-12-22T15:14:36.070Z
@IMSoP, again I'm thinking on the hoof, but I'm not sure there is "only one type of Null". Certainly, there is no independent Null data type. And the Null constant/keyword can be cast to a specific type. Perhaps the design of SQL has been muddled in this area.
Steve
CC BY-SA 4.0
2023-12-22T16:43:38.617Z
@IMSoP: The problem with bringing types into this is that SQL is not a type-theoretic language. It is a set-theoretic language, which makes limited use of type theory for the sake of coherence and optimization. While nullable fields may coincidentally share some features with a union type, SQL has no real notion of union types or ADTs in general. Rather than thinking of `null` as a unit type, it is probably better to think of it as the "missing" output of a partial function (in the set theory sense of "function," not the type theory sense).
Kevin
CC BY-SA 4.0
2023-12-27T22:58:22.687Z
I think SQL treats the 3VL values as TRUE, FALSE, and UNKNOWN?
qwr
CC BY-SA 4.0
2023-12-28T01:07:02.123Z
@qwr, yes in logical expressions that is correct, although I don't recall the SQL syntax explicitly acknowledging an `UNKNOWN` keyword - it appears to be a term that is only used in documentation. But as I say in my answer, the use of Null goes beyond logical contexts, far beyond any 3VL theoretical formalism. Every data type includes Null in its domain, and all kinds of operators have some kind of special handling for Null. Like how bats resemble both birds and mice, SQL draws clearly upon many preceding theories, but without fully corresponding to any of them.
Steve
<p>It's convenient for the way SQL is typically used. Consider this statements:</p>
<pre class="lang-sql prettyprint-override"><code>SELECT people.name, cars.model FROM people
INNER JOIN cars
    ON people.car_licenceplate = cars.licenceplate
</code></pre>
<p>If <code>null = null</code>, then this would return all pairs of people with no license plate with all unregistered cars in the database, a usually undesirable result.</p>
<p>It's particularly convenient that, even if you use any null value even in a more complex expression, you won't get a value back, even if other values may also happen to be null. In other languages you'd need to null check everything in advance to get that behavior, having it by default is very convenient for the type of things SQL is typically used for.</p>
<p><code>null</code> in SQL is exempt from a lot of other rules too. For example they are excluded from unique constraints. All indicating it represents more the absence of a value rather than a special value.</p>
<p>Some other languages do also have a <code>ThreeValueBoolean</code> or a similar type that behaves more like a SQL null, though only for booleans. Also most every language has similar non self-equality for NaN. It's not a concept unique to SQL.</p>

CC BY-SA 4.0
2023-12-21T16:07:58.523Z
2023-12-21T21:57:32.780Z
2023-12-21T21:57:32.780Z
mousetail 'he-him'
CC BY-SA 4.0
2023-12-22T00:26:02.027Z
This example is flawed. The ownership of cars should be a 1 person to N cars relationship, therefore if a person has no cars, there should be no entries for that person when doing the join.
Michael Tsang
CC BY-SA 4.0
2023-12-22T00:43:50.920Z
@MichaelTsang Yeah, the example is set up for 1 car to N people. Imagine instead of using the license plate as key, they use the driver's license. After all, at least where I'm at, one can't get a license plate or renew it without a valid driver's license. So, `people.drivers_license = cars.owners_drivers_license`. If `null = null` were true, then that would pair people without a driver's license to cars that aren't registered for circulation.
JoL
CC BY-SA 4.0
2023-12-22T07:17:38.420Z
@MichaelTsang In this hypothetical case some cars are unregistered and have no license plate
mousetail 'he-him'
CC BY-SA 4.0
2023-12-22T12:46:31.117Z
NaN != NaN is widely considered a mistake. All languages implement it not because it's desirable behavior, but because they're required to in order to follow IEEE 754
BlueRaja - Danny Pflughoeft
CC BY-SA 4.0
2023-12-22T13:53:12.693Z
@BlueRaja-DannyPflughoeft I believe the main issue is that `NaN != NaN` too. A proper third output for equality would allow not worrying about NaN in complex expressions and being confident it wouldn't cause a undesired true result. As it is now you basically need to explicitly check for it every time even if you want to treat is in a special way
mousetail 'he-him'
CC BY-SA 4.0
2023-12-23T00:19:15.377Z
Just to add to a great answer.... https://www.ibm.com/docs/ro/psfa/7.2.1?topic=except-handle-nulls not all versions of SQL evaluate null=null as false.  In netezza the outcome is situational, which is because the result is convenient in some cases but not others.
P. Hopkinson
CC BY-SA 4.0
2023-12-23T08:06:42.580Z
@P.Hopkinson: Well spotted. This is not unique to IBM's database, it appears to be [specified in the SQL Standard](https://stackoverflow.com/q/53956546/87698). `SELECT NULL UNION SELECT NULL` only returns only one row in all DB systems I am familiar with.
Heinzi
<p>One way to look at this is to compare these two questions:</p>
<ol>
<li>Is value A <em>definitely the same as</em> value B?</li>
<li>Is value A <em>definitely different from</em> value B?</li>
</ol>
<p>On the face of it, these are symmetrical: if question 1 is true, question 2 is false, and vice versa.</p>
<p>But what if both A and B are missing or invalid data points?</p>
<ol>
<li>False. We can't know for sure that the two missing or invalid data points are the same.</li>
<li>False. We can't know for sure that the two missing or invalid data points are different.</li>
</ol>
<p>That puts us in a peculiar position: <code>A = B</code> and <code>A &lt;&gt; B</code> should both be false, but that means that <code>NOT (A = B)</code> is no longer the same as <code>A &lt;&gt; B</code>, which is surprising.</p>
<p>SQL handles this by returning a further <code>NULL</code> - if the data for <code>A</code> and <code>B</code> is missing, then the information about whether they are the same or different is <em>also</em> missing. This is consistent with other operations on <code>NULL</code>, e.g. <code>NULL + NULL</code> is <code>NULL</code>, because adding two unknown numbers gives you a third unknown number. And since that also includes boolean negation - if <code>A</code> is <code>NULL</code>, then <code>NOT A</code> is also <code>NULL</code>, the result of <code>NOT (A = B)</code> is always the same as <code>A &lt;&gt; B</code>, as we'd intuitively expect.</p>
<p>However, there are situations where we want to ask the strict negation of those questions:</p>
<ol start="3">
<li>Is value A <em>not definitely the same as</em> value B? (Strict inverse of question 1)</li>
<li>Is value A <em>not definitely different from</em> value B? (Strict inverse of question 2)</li>
</ol>
<p>For these, SQL provides the <code>DISTINCT FROM</code> and <code>NOT DISTINCT FROM</code> operators.</p>
<p>More commonly, you want to know explicitly that a particular value is or is not null, for which there are the operators <code>IS NULL</code> and <code>IS NOT NULL</code>.</p>

CC BY-SA 4.0
2023-12-21T17:50:32.253Z
2023-12-22T12:55:49.857Z
2023-12-22T12:55:49.857Z
IMSoP
CC BY-SA 4.0
2023-12-23T18:19:14.070Z
**Comments have been [moved to chat](https://chat.stackexchange.com/rooms/150482/discussion-on-answer-by-imsop-why-does-sql-treat-null-null-as-null-unlike-all); please do not continue the discussion here.** Before posting a comment below this one, please review the [purposes of comments](/help/privileges/comment). Comments that do not request clarification or suggest improvements usually belong as an [answer](/help/how-to-answer), on [meta], or in [chat]. Comments continuing discussion may be removed.
Michael Homer
# Why does SQL treat null = null as null, unlike all other mainstream programming languages where null = null is true?
Posted by: username_0 on 2023-12-21
In SQL, equality involving null always returns null regardless of the other operand; for example, all of the following evaluate to null in SQL:
0 = null
null = null
0 <> null
null <> null

SQL requires the use of the is null or is not null operator to test for a null value for a nullable variable, which return true or false; for example, null is null is true while null is not null is false.
In contrast, all mainstream programming languages which support null, have their equality operators behave in the usual and predictable way, treating null no different than any regular values. For example, in Java, equality to null behaves just like equality to any other values:
final String x = "";
x == null; // false
null == null; // true
x != null; // true
null != null; // false

For both null in SQL and null (or its equivalent) in mainstream programming languages, it is a placeholder for an absence of value. In fact, when we process data returned from SQL databases in a programming language with null value support, a null from SQL is returned as a null in the other programming language. Why are they treated differently in SQL and in other programming languages in equality operators?
**Tags:** nullability
> **Comments:**
> **username_1**: The same is true for the null arithmetic value (usually called "NaN") in many languages, so SQL is hardly unique here.  
> **username_2**: SQL uses null for conflated purposes.  Sometimes it means missing data, and other times it means not applicable.  
> **username_3**: @TobySpeight Not really; `NaN == NaN` is *false*, not NaN.  
> **username_4**: @KarlKnechtel That's more due to technical limitations than an intentional design. IEEE is meant to apply to a wide variety of systems that may not be able to easily support a value other than true or false  
> **username_5**: @KarlKnechtel But the main point is that it's not *true* even though it seems like it should be.  
> **username_6**: @ErikEidt I think *most* languages use the same value/type for both of those purposes - if you don't know the price of an item in Java, you might well return `null` from `item.getPrice()`  - and does it make sense for two such unknown prices to compare as "equal"?  
> **username_7**: If there is a person table where few people did not specify their age (age = null) then they wont be selected in this `select * from person where age > 18` nor `select * from person where age <= 18`. It makes sense, doesn't it? I mean what would be the criteria of putting people with age = null in exactly one of `<= 18, > 18` groups?  
> **username_0**: In a programming language with strong typing, comparing a nullable item (`age`) with a comparison operator is an error, because comparison operators are meaningless on `null`.

If the same design is applied in SQL, you won't be able to type `age > 18` if `age` is a nullable column, and you are forced to handle it separately.  
> **username_7**: Well c# allows it and the result is roughly similar to SQL.  
> **username_6**: @MichaelTsang Raising an error is certainly an alternative strategy, but not one I've seen implemented very commonly for simple comparisons. For instance in C#, this outputs "equal": `String x = null; String y = null; Console.WriteLine((x == y) ? "equal" : "not equal");` and this outputs "not equal": `String x = "hello"; String y = null; Console.WriteLine((x == y) ? "equal" : "not equal");`  
> **username_8**: @IMSoP: I think "relational comparison" was meant, not "equality comparison"  
> **username_9**: SQL tries to implement 3-valued logic, like that of Kleene 3VL. There have been proposals for separate "missing" and "NA", but I don't think any of those 4VLs have taken off.  
> **username_8**: @IMSoP: You replied to a comment by MichaelTsang saying comparisons don't work, with `age > 18` as an example.  Your response was (to paraphrase), sure comparisons do work, with `x == y` as an example.  You substituted Michael's relational comparison with an equality test.  (C# does support relational comparisons on nullables, as "lifted operators", with some non-obvious semantics)  
> **username_6**: @BenVoigt Ah, right; the word "relational" confused me given the context of the conversation. It makes no difference, though, for nullable types where `<` is defined, C# happily applies it to null values: https://dotnetfiddle.net/DWJs0l My point is that while I'm sure there are languages which throw errors for operators acting on null values, it's definitely not universal in any language with strong typing, as implied by the comment I was replying to.  
> **username_10**: *"In contrast, all mainstream programming languages which support null, have their equality operators behave in the usual and predictable way, treating null no different to any regular values."* Counter example: VBA and Visual Basic.NET. Granted, they are not the most popular languages, but I guess they'd count as "mainstream".  
> **username_11**: @qwr, Codd himself tried to make a 4VL-style logical system which distinguished "missing" and "inapplicable". One problem is that it produces a vast and unmanageable number of possible logical operators. But in my opinion, the main reason it doesn't work is because the programmer often doesn't actually know whether the information is missing or inapplicable, so you end up with known-missing, known-inapplicable, and unknown, and sometimes whether something is missing or inapplicable depends on the use/purpose of the query.  
> **username_12**: @Steve: How would known-missing be different from existing "nothing to see here" values like `FALSE`, `0`, or `''` (empty string)?  
> **username_11**: @dan04, it would be different from those values in the same way that Null in SQL (which represents all three of known-missing, known-inapplicable, and unknown) is different from them. Your question is potentially confusing to me, because I'm not sure whether you are asking specifically about some subtlety of Codd's 4VL idea, or whether you are asking a very general question about why it is useful to have a separate Null value in SQL?  
> **username_12**: @Steve: I *do* see the value in distinguishing between absence of knowledge (e.g., `MiddleName = NULL` when we don't know a person's middle name) and knowledge of absence (`MiddleName = ''` when we've confirmed that they don't have one).  What I (and apparently the designers of SQL) don't get is the need for a third type of "nothing" that's neither of those things.  
> **username_11**: @dan04, if you read all my contributions on this question, including my answer and my various comments, you'll see that I don't accept that Null represents only "missing" or "unknown" information. It is actually a multi-purpose value that represents many meanings. As I say in my comment above, it is standard to consider that Null breaks down into "missing" and "inapplicable". But in my view, there is a third case of how Null is used, which is where the meaning is certainly either one of missing or inapplicable, but the organisation doesn't and cannot know which. (1/2)  
> **username_11**: In my answer, I give the example of a vet treating an injured dog, and not knowing whether it is a dog whose owner is unknown for the time being, or an unowned (wild) dog. What unites all these different cases or meanings, is the need usually for some kind of special handling. That's what Null provides in SQL. Many people get hung up on what Null means about the world, rather than what it means for the automatic data processing which the database performs. Has that clarified anything? (2/2)  
---
## 7 answers
### Answer by username_4 on 2023-12-21. Score: 40
It's convenient for the way SQL is typically used. Consider this statements:
SELECT people.name, cars.model FROM people
INNER JOIN cars
    ON people.car_licenceplate = cars.licenceplate

If null = null, then this would return all pairs of people with no license plate with all unregistered cars in the database, a usually undesirable result.
It's particularly convenient that, even if you use any null value even in a more complex expression, you won't get a value back, even if other values may also happen to be null. In other languages you'd need to null check everything in advance to get that behavior, having it by default is very convenient for the type of things SQL is typically used for.
null in SQL is exempt from a lot of other rules too. For example they are excluded from unique constraints. All indicating it represents more the absence of a value rather than a special value.
Some other languages do also have a ThreeValueBoolean or a similar type that behaves more like a SQL null, though only for booleans. Also most every language has similar non self-equality for NaN. It's not a concept unique to SQL.
> **Comments:**
> **username_0** (2023-12-22): This example is flawed. The ownership of cars should be a 1 person to N cars relationship, therefore if a person has no cars, there should be no entries for that person when doing the join.  
> **username_17** (2023-12-22): @MichaelTsang Yeah, the example is set up for 1 car to N people. Imagine instead of using the license plate as key, they use the driver's license. After all, at least where I'm at, one can't get a license plate or renew it without a valid driver's license. So, `people.drivers_license = cars.owners_drivers_license`. If `null = null` were true, then that would pair people without a driver's license to cars that aren't registered for circulation.  
> **username_4** (2023-12-22): @MichaelTsang In this hypothetical case some cars are unregistered and have no license plate  
> **username_18** (2023-12-22): NaN != NaN is widely considered a mistake. All languages implement it not because it's desirable behavior, but because they're required to in order to follow IEEE 754  
> **username_4** (2023-12-22): @BlueRaja-DannyPflughoeft I believe the main issue is that `NaN != NaN` too. A proper third output for equality would allow not worrying about NaN in complex expressions and being confident it wouldn't cause a undesired true result. As it is now you basically need to explicitly check for it every time even if you want to treat is in a special way  
> **username_19** (2023-12-23): Just to add to a great answer.... https://www.ibm.com/docs/ro/psfa/7.2.1?topic=except-handle-nulls not all versions of SQL evaluate null=null as false.  In netezza the outcome is situational, which is because the result is convenient in some cases but not others.  
> **username_10** (2023-12-23): @P.Hopkinson: Well spotted. This is not unique to IBM's database, it appears to be [specified in the SQL Standard](https://stackoverflow.com/q/53956546/87698). `SELECT NULL UNION SELECT NULL` only returns only one row in all DB systems I am familiar with.  
---
### Answer by username_6 on 2023-12-21. Score: 23
One way to look at this is to compare these two questions:

1. Is value A *definitely the same as* value B?
2. Is value A *definitely different from* value B?

On the face of it, these are symmetrical: if question 1 is true, question 2 is false, and vice versa.

But what if both A and B are missing or invalid data points?

1. False. We can't know for sure that the two missing or invalid data points are the same.
2. False. We can't know for sure that the two missing or invalid data points are different.

That puts us in a peculiar position: `A = B` and `A <> B` should both be false, but that means that `NOT (A = B)` is no longer the same as `A <> B`, which is surprising.

SQL handles this by returning a further `NULL` - if the data for `A` and `B` is missing, then the information about whether they are the same or different is *also* missing. This is consistent with other operations on `NULL`, e.g. `NULL + NULL` is `NULL`, because adding two unknown numbers gives you a third unknown number. And since that also includes boolean negation - if `A` is `NULL`, then `NOT A` is also `NULL`, the result of `NOT (A = B)` is always the same as `A <> B`, as we'd intuitively expect.

However, there are situations where we want to ask the strict negation of those questions:

3. Is value A *not definitely the same as* value B? (Strict inverse of question 1)
4. Is value A *not definitely different from* value B? (Strict inverse of question 2)

For these, SQL provides the `DISTINCT FROM` and `NOT DISTINCT FROM` operators.

More commonly, you want to know explicitly that a particular value is or is not null, for which there are the operators `IS NULL` and `IS NOT NULL`.
> **Comments:**
> **username_20** (2023-12-23): **Comments have been [moved to chat](https://chat.stackexchange.com/rooms/150482/discussion-on-answer-by-imsop-why-does-sql-treat-null-null-as-null-unlike-all); please do not continue the discussion here.** Before posting a comment below this one, please review the [purposes of comments](/help/privileges/comment). Comments that do not request clarification or suggest improvements usually belong as an [answer](/help/how-to-answer), on [meta], or in [chat]. Comments continuing discussion may be removed.  
---
### Answer by username_11 on 2023-12-22. Score: 13
I find it can be quite fiendish to explain this aspect of SQL convincingly, because of the sheer depth of an explanation which fully justifies the design and rebuts superficial objections. I don't know whether I have the capability to deliver that explanation.

**Relational Algebra**

The first thing to mention is EF Codd's Relational Algebra. Although SQL and RA are not completely synonymous, RA was the main theoretical foundation for the design of the SQL language.

Some of the main conceptual features of RA is the "relation" (what in SQL is simply called a "table"), the "relational operators" (including the "joins"), the "Null" value, and a system of so-called 3-valued logic (including a certain approach to handling the Null value).

**3VL**

I'll use "3VL" as a shorthand for referring to the system of both the Null value itself and the handling of it by operators.

It's a bit of a misnomer here in the sense that Null is a value which is available in the domain of all SQL data types, including those which have many more than 3 possible values. It is not simply limited to supplementing the Boolean/logical/bit type with a third value. But there's no better and more commonly understood term than "3VL".

There are also many more confusing details, though not necessary to examine for this answer.

**Joins**

The way the join operators work in RA and SQL depends inextricably on 3VL.

Firstly, each join operation can produce Null values to represent the case where certain rows in input tables were not joined.

Secondly, each join operator can have Null values in its inputs. This may be either because these Nulls exist in static data, or because a previous join operation (in a query consisting of more than one join) has produced Nulls at an earlier stage.

The only sensible behaviour of the join operators, is that Nulls do not join to one another. Therefore, at least in the context of the join control expressions, Null compared to Null must be false.

**Justification of how joins work**

This might not seem very intuitive or obviously correct at first glance.

But there are two main justifications.

The first is that join operators with this exact behaviour have important algebraic properties (hence the name of the theoretical framework, "Relational Algebra"), and these algebraic properties are crucial for optimisation and performance when dealing with "large shared data banks" (as EF Codd described what we now describe as "databases" serving typical "OLTP loads"). Alterations to the behaviour of the join operators potentially destroy their algebraic properties, and with it the crucial optimising capability.

**The meaning of Null**

The second justification relies on explaining what Null means, and how it is used in practice.

To many programmers who are new to SQL but familiar with other languages, the word "Null" is what linguists call a "false friend". There is no analogy in any mainstream programming language, for how Null works in SQL.

Typically in other languages, Null is associated with the "null pointer", which is invariably the zero-valued pointer on any hardware architecture I'm familiar with. Not so in SQL, which has no concept of memory pointers in its syntax, and where Null definitely does not mean zero.

The Null value in SQL broadly represents the same thing as a "blank space on a paper form". That is, it's meaning is very ambiguous and inconsistent, but it broadly means either "*missing*" or "*inapplicable*".

"Missing" broadly means that a certain value should be recorded or relates to a fact that is capable of being recorded, but for whatever reason isn't recorded.

"Inapplicable" broadly means that a certain field is somehow not applicable to a particular case. For example, a vet might typically record a dog's "owner name and address", but if the dog is a stray born in the wild and has no owner, then the owner name and address is inapplicable to the vet's record about that dog.

Very often, it may not be clear whether data is missing or inapplicable - for example, at the time of veterinary treatment, it may not be possible to distinguish between a dog which has an unknown owner, and an unowned dog. On a paper form, blank would be left for either case.

But there is one consistent thing about "paper-blank", which is that a blank on one paper form doesn't mean an association with blanks on other paper forms. If you have a series of forms with names missing from many of them, this doesn't mean all the ones with blank names belong to the same person (who has no name). They almost certainly belong to different people, all of whose names happen not to be recorded on the forms.

If you understand that analogy, you understand why Null doesn't join to Null in SQL. Because blanks don't connect to blanks when dealing with paper records.

**Records and keys**

There is an underlying conceptualisation here which is about "records" and "keys" - the concept of which predates SQL, RA, and is a common practice when dealing with paper records.

What you commonly have with business records are linkages between different records based on "keys" - for example, a customer account number is a "key". When a customer places an order, you record the details of the order on the form, and you also record a "key" on the same form which is the customer account number. Detailed information about the customer account, and which defines the "key" for that particular account, will be recorded separately from information about each order.

The use of the key on the order forms means that all orders can be linked via the account number. This linkage allows a business to do certain things which depend on organising or analysing all the orders of a particular account together, like controlling the total amount of credit extended to a particular customer.

Now, if there are order forms with no customer account number recorded, these do not link to a single customer account whose key is "blank". Rather, the blank means those forms are unassociated with any customer account - that the customer account is *missing or inapplicable*.

So that's what practice the join operators in SQL are reflecting. I hope at this stage I've explained why Nulls shouldn't join to Nulls, and by implication, why Null doesn't compare equal to Null.

**Why the equals operator is defined as it is**

Because SQL was designed primarily around it's relational algebra capability and the concept of joining tables, as well as the 3VL concept, the designers have prioritised terseness when using that functionality, and comparisons involving Nulls (such as equality using `=`, but also including the other standard comparison operators) are never true.

Instead, when Nulls must be specifically compared, the special `IS NULL` operator is used.

It is still possible to perform all kinds of comparisons in SQL. It is simply more long-winded to write comparison expressions which treat Nulls as equal, such as having to write `(x = y) OR (x IS NULL AND y IS NULL)`.

**A final word of warning**

You might find explanations in this area which attempt to describe Null as "the absence of a value".

In my view, that does not describe the reality. Null is very much an explicit value, because computers only work with values (or symbols) and cannot encode or process pure non-values, but it is a value which is frequently (not always) used to represent the fact that there was an absence of recordable information available when the computer record was made.

You might also see explanations of how Null comparison is handled, which involve saying that you can't tell whether two Nulls are different or not.

In many cases, this begs the question of whether Null is in fact being used to represent missing/unknown information. If Null is used in a capacity of being a marker of inapplicability, or as a default value, then there is no natural reason why these markers should not be considered equal.

But it's also a red herring. The main explanation for the behaviour of Null is how it integrates with the behaviour of the join operators, and how those join operators model the linkages between records, where the presence of Null should almost always mean "do not join" (without necessarily implying missing information).
> **Comments:**
> **username_0** (2023-12-22): In programming languages with dynamic typing, such as PHP or JavaScript, `null` (also `undefined` in JavaScript) is also used as a placeholder for a missing or inapplicable value in place of any data type as well. Their type system (Typescript for JavaScript) explicitly defines a nullable type as a union of the original type and `null`, a data type itself.  
> **username_11** (2023-12-22): @MichaelTsang, yes many languages have something called null and it is used for some of the same purposes. But the overall mechanics are quite different. Also, I'm not quite sure whether SQL treats nullable fields as being union types. On the whole, I'd be inclined to say it treats the domains of all data types as intrinsically containing a null value - rather than thinking of it as being like a conventional data type plus a bolted-on null value. These kinds of subtleties are why a so-called "impedance mismatch" occurs between SQL and other conventional programming languages.  
> **username_6** (2023-12-22): @Steve Treating null as an implicitly valid value for all data types is actually quite common, particularly in languages which expose pointers, or make explicit the concept of "reference types". Often, you can actually have null pointers of different types. In that sense, SQL is treating it _more_ like a union type, since there is only one type of `null`, and operations are overloaded to give specific results for that type.  
> **username_11** (2023-12-22): @IMSoP, again I'm thinking on the hoof, but I'm not sure there is "only one type of Null". Certainly, there is no independent Null data type. And the Null constant/keyword can be cast to a specific type. Perhaps the design of SQL has been muddled in this area.  
> **username_16** (2023-12-22): @IMSoP: The problem with bringing types into this is that SQL is not a type-theoretic language. It is a set-theoretic language, which makes limited use of type theory for the sake of coherence and optimization. While nullable fields may coincidentally share some features with a union type, SQL has no real notion of union types or ADTs in general. Rather than thinking of `null` as a unit type, it is probably better to think of it as the "missing" output of a partial function (in the set theory sense of "function," not the type theory sense).  
> **username_9** (2023-12-27): I think SQL treats the 3VL values as TRUE, FALSE, and UNKNOWN?  
> **username_11** (2023-12-28): @qwr, yes in logical expressions that is correct, although I don't recall the SQL syntax explicitly acknowledging an `UNKNOWN` keyword - it appears to be a term that is only used in documentation. But as I say in my answer, the use of Null goes beyond logical contexts, far beyond any 3VL theoretical formalism. Every data type includes Null in its domain, and all kinds of operators have some kind of special handling for Null. Like how bats resemble both birds and mice, SQL draws clearly upon many preceding theories, but without fully corresponding to any of them.  
---
### Answer by username_13 on 2023-12-22. Score: 4
For booleans, NULL means "the whole Boolean domain", or the set {true, false}

In SQL, the expression `TRUE OR NULL` is true (not a null, as some might expect), and the expression `FALSE AND NULL` is false.

If we treat the expression `TRUE OR X` as a boolean function over the boolean X, its domain is {true, false} and its image is {true}. This function is a constant which doesn't depend on its input. When passed NULL, which we can treat as "any value from the Boolean domain", its result is still true, that's why it evaluates to true.

If we take something like `TRUE AND X`, whose image is {true, false}, and pass it "any Boolean value", the result can be "any Boolean value" as well, i.e. NULL.

The NULL comparison behavior seems to be the remnant of a half-assed attempt to extend this logic to other types. For instance, `10 NOT IN (NULL, 5)` will evaluate to NULL (because we can make it both true and false, depending on what concrete integer we put instead of the NULL), but `10 NOT IN (NULL, 10)` will evaluate to false (because no matter what other value we put instead of NULL, it will always be false).

In the same vein, `NULL = NULL` can be made either true or false by substituting different concrete values on either side of the comparison, so it evaluates to NULL.

Of course, had this logic been seen through, we would expect something like `0 * NULL` to evaluate to 0 and `COUNT(NULL)` evaluate to the same thing as `COUNT(*)`, but it didn't happen (nor it realistically could, if you start thinking about finer details, like what should `a - a` evaluate to).

The operator `x IS NOT DISTINCT FROM y` returns true for two nulls, and so does `EXISTS (SELECT x INTERSECT SELECT y)` which I use a lot in SQL Server.
> **Comments:**
> **username_6** (2023-12-22): I don't think your examples justify the label "half-assed". Multiplying by zero is an interesting edge case which could be defined that way, but wouldn't be very useful in practice. The behaviour of `COUNT` is perfectly logical: the *only* thing it does with its optional argument (where `COUNT(*)` can be seen as a funny way of writing it with no argument) is examine whether it is null or not; if it didn't do that, there would be no point in the argument at all.  
> **username_13** (2023-12-22): @IMSoP: what you are saying is true, but I fail to see how it makes this behavior "perfectly logical". To be consistent with the logic of `IN`, the aggregate functions should be instantly poisoned by a single null, and if you wanted to skip the null values, you would need to filter them out in the `WHERE` clause. I'm not saying it would be better or easier to use, just that it's inconsistent.  
> **username_6** (2023-12-22): I didn't say it was "perfectly logical", just that it wasn't "half-assed". I think the clearest description of aggregate behaviour is that the aggregate itself is never examining `NULL` values; rather, the definition of which values to feed into it is "values which aren't null". If you wanted to model them as set-operating functions, you could say that SQL's `COUNT(x)` is defined as `count_items( filter_nulls( evaluate_per_row(x) ) )`. In other words, it's more a short-hand in the way the _syntax_ of the language works, rather than in the _semantics_ of the operations.  
> **username_11** (2023-12-22): @Quassnoi, we had an interesting discussion of this a few months ago on SE.SE. The basic principle is that scalar operators propagate nulls, and aggregate operators eliminate nulls. The reason for this distinction in approach is because each caters to the most common use, *and* the opposite behaviour is easily achieved with a few extra words of syntax localised to the operator in question. If either kind of operator worked like the other, then the current behaviour which each kind of operator has becomes extremely difficult to achieve. So any extra consistency would be foolish.  
> **username_6** (2023-12-22): @Steve Yes, I hadn't thought before just how difficult the opposite would be! Given my pseudo-code above, you can easily change the expression `x` to never evaluate to `null` (e.g. with `coalesce`), but if the expansion was `count_items( evaluate_per_row(x) )`, there would be no change you could make to `x` that would emulate `filter_nulls`, because the expression is evaluated _per row_. `AVG(x)` is probably a better example, since its value depends on both the number of items in the set and their values, so there's no natural value that you can add to the set which doesn't affect the result.  
> **username_13** (2023-12-22): @IMSoP: the behavior of `NOT IN` with nulls is probably _the_ most confusing thing in whole SQL. People expect it just to filter out the nulls, i.e. do what aggregates do, and I don't think I've ever seen the case where the defined behavior of `NOT IN` would be actually useful. The same is true for `GREATEST` and `LEAST`. The latter is not even consistent across platforms, i.e. PostgreSQL and SQL Server do the right thing and ignore the nulls, Oracle doesn't. The unintuitive behavior is kept solely because of the legacy code. This is what I mean by "half-assed"  
> **username_6** (2023-12-22): @Quassnoi Unlike with aggregates, you have full control over the set passed into the right-hand side of `IN` and `NOT IN`, so they act as pure set-wise operations - `x IN (a,b)` could be written `x = ANY( [a,b] )`, or `x=a OR x=b`. `x NOT IN (a,b)` is the complement of that, which is `x <> ALL( [a,b] )`, or `x<>a AND x<>b`. In my experience, people don't find [De Morgan's laws](https://en.wikipedia.org/wiki/De_Morgan%27s_laws) intuitive, so extending them to 3-valued logic is probably why `NOT IN` trips so many people up.  
> **username_11** (2023-12-22): @Quassnoi, I'm sure I occasionally use `NOT IN` on non-nullable columns - so it's not completely without usefulness - but it's become second-nature to me to scrutinise filtering very carefully whenever nullable columns are involved. It's certainly an area of the language that feels like it could withstand some kind of improvement.  
> **username_13** (2023-12-22): @IMSoP: what do you mean by "full control"? `NOT IN (SELECT field FROM table)` cannot evaluate to true if there's but a single null in the result of the query. How is control on this query "unlike" control on the query driving aggregates?  
> **username_6** (2023-12-22): @Quassnoi Because the argument `(SELECT field FROM table)` is an expression which returns a set, and you can choose what's in that set, e.g. with a `WHERE` clause. With an aggregate, the argument is an expression that returns a _single value_, and you have no control over when it's evaluated - in `SELECT AVG(x), AVG(y) FROM z` you can't choose a different set of `x` values and `y` values in order to explicitly filter nulls before passing to `AVG`.  
> **username_9** (2023-12-22): The logical comparison behavior is like Kleene 3VL. It doesn't apply for set and union or joins though.  
> **username_13** (2023-12-22): @IMSoP: in e.g. PostgreSQL, you can both filter the aggregates and use tuples in `IN` queries: `(1, 2) IN (SELECT a, b FROM mytable)`. My point was that `IN`, `GREATEST` and other variadic functions should behave like aggregates, and `IS DISTINCT FROM` should have been there from the beginning (maybe as `==` or something like this). A function for a scalar sum (like `a + b + c + d`  but ignore nulls) is also something that I would need more often than the default poisonous null behavior. Something like `a FULL JOIN b FULL JOIN c` is a pain to write if your system doesn't support `USING`…  
> **username_13** (2023-12-22): @IMSoP: … and so on and so forth. In my book, this all falls under the definition of a "half-assed design", is all I'm saying. I can totally see how it wound up to be the way it is, but it doesn't make it any better.  
> **username_6** (2023-12-22): @Quassnoi My point is that for an aggregate, you have to introduce extra syntax to express the filter, because by default you're only specifying what should happen on each row, not which rows to consider. The designers of SQL could have required us to write `AVG(x WHERE x IS NOT NULL)` but they decided it was simpler both for the user and the implementation to assume that by default, and let us write `AVG(COALESCE(x, 0))` for the opposite case. With functions that take a rowset as their argument, the language already has all the facilities we need to apply whatever filter we want.  
> **username_6** (2023-12-22): @Quassnoi I don't agree that there's a necessary equivalence between aggregates and rowset-consuming functions / operators. To define a custom aggregate in Postgres, you don't create a function that takes a rowset as input, you create a function that accepts each value in turn, along with a carried partial result; this has the useful effect of also making them available as window functions. I see nothing "half-assed" about including both set-wise functions and iterative aggregates in the language, and choosing useful semantics for each.  
---
### Answer by username_9 on 2023-12-26. Score: 3
Most programming languages implement Boolean, or 2-valued logic, with the familiar TRUE and FALSE. SQL implements a **3-valued logic** with TRUE, FALSE, and UNKNOWN. In most SQL DBs, [NULLs are treated in comparisons as UNKNOWNs](https://stackoverflow.com/questions/41180547/is-sql-unknown-identical-to-null) (I believe, T-SQL might be different).

When we use the logical operators AND, OR, and NOT, we sometimes get the same output if we replaced UNKNOWN with TRUE and separately replaced UNKNOWN with FALSE.

* For example, for `TRUE OR UNKNOWN`, replacing UNKNOWN with TRUE and replacing UNKNOWN with FALSE both still result in the expression evaluating TRUE.
* On the other hand, `TRUE AND UNKNOWN` would be TRUE if the UNKNOWN were TRUE, and FALSE if the UNKNOWN were FALSE.

Just like for 2VL logical operations, we can create truth tables for 3VL that match this "unknowns logic", a subset of Kleene's K3 logic. Since these operations are commutative, I'll present the main cases:

| A | B | A AND B | A OR B | A = B |
| --- | --- | --- | --- | --- |
| TRUE | UNKNOWN | UNKNOWN | TRUE | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
| FALSE | UNKNOWN | FALSE | UNKNOWN | UNKNOWN |

The symmetry is reminiscent of the symmetry in Boolean logic.

`=` in SQL treats all NULLs as distinct, so `A = NULL` is always UNKNOWN. Use `A IS NULL` to check if A is populated with NULL. `NOT UNKNOWN` is `UNKNOWN`.

| A | NOT A | A IS NULL |
| --- | --- | --- |
| TRUE | FALSE | FALSE |
| UNKNOWN | UNKNOWN | TRUE |
| FALSE | TRUE | FALSE |

This niceness goes out the window when it comes to other syntax. `WHERE` will only return rows when the search condition is TRUE, not for FALSE nor UNKNOWN. For a left (outer) join, for any rows from the left table that don't have a match in the right table, the right table rows get a NULL value. From the [T-SQL docs on joins](https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver16#nulls_joins)

> The results do not make it easy to distinguish a NULL in the data from a NULL that represents a failure to join. When NULL values are present in data being joined, it is usually preferable to omit them from the results by using a regular join.

See also [How NULL values are treated in UNION and UNION ALL](https://stackoverflow.com/questions/53956546/how-null-values-are-treated-in-union-union-all-in-mysql). I will defer to other answers on this, as I always have to look up how different SQL keywords handle NULL. It is not intuitive.

---

If you think about NULL carefully, you'll realize it serves at least two purposes in SQL: 1. unknown/missing data, and 2. not applicable data. (3. NULLs introduced by joins.) This opens the door for having separate UNKNOWN and NA values, and 4-valued or further many-valued logic, but that gets even more complicated. Lots of literature has been written about SQL's treatment of NULL and why it is not intuitive. See [Steve's insightful comment on why this is worse.](https://langdev.stackexchange.com/questions/3411/why-does-sql-treat-null-null-as-null-unlike-all-other-mainstream-programming?noredirect=1#comment9655_3411)
> **Comments:**
> **username_11** (2023-12-28): I've found out why so much Microsoft documentation refers to `UNKNOWN` despite no relevant use of that keyword in TSQL. It's because the ISO/IEC 9075 standard itself uses the Unknown term. But the standard also states that Unknown is synonymous with Null typed as the Boolean/Bit data type.  
> **username_9** (2023-12-29): @Steve yeah it's hard for me to find out exactly when SQL treats NULL as UNKNOWN. NULL could be in any data type and SQL implementations don't have to implement their Boolean type.  
---
### Answer by username_15 on 2023-12-22. Score: 3
Simply put, in the world of SQL, `NULL` means that the value is unknown. It *can* mean that the value simply doesn't exist, like it does in other languages. But, since it can mean that the value is unknown, then comparing two items, where one or both are of an unknown value (`NULL`) results in an unknown value--`NULL` in SQL.
---
### Answer by username_14 on 2023-12-22. Score: 0
In an outer join (`A LEFT JOIN B`), a null value in the table `B` could mean the value is nonexistent. That means the whole row shouldn't exist in an inner join. If someone adds more restrictions about the values in table `B` after the join (in the `WHERE` or `HAVING` part instead of the `ON` part) without null checks, it effectively falls back to an inner join, that is, making the extra rows in an outer join always discarded, by implementing comparison this way.

I can't think of a good example for now. But think about saving the result of an outer join in another table. It makes sense to keep the semantics of the data unchanged and accessible. You shouldn't get another row if you just compared two different indicators about the row should be nonexistent, if a single indicator worked as expected.

In SQL, nulls work like unknown / partially, somewhere between true and false, in logical operations. That is, `NULL AND TRUE` is null, but `NULL AND FALSE` is false. The null result from equality comparison matches
the result of xnor (`(NULL AND NULL) OR (NOT NULL AND NOT NULL)`). So it's not really something unexpected.
> **Comments:**
> **username_9** (2023-12-26): From the T-SQL docs on joins: The results do not make it easy to distinguish a NULL in the data from a NULL that represents a failure to join. When NULL values are present in data being joined, it is usually preferable to omit them from the results by using a regular join.  
> **username_14** (2024-01-01): @qwr If you use nulls in the data with other semantics in mind, most likely the semantics in the language making queries, and don't consider their semantics in SQL, it's always better to just match the logic of the other language, making any argument moot. But as SQL has ways to generate nulls, it's more productive to think in the SQL way and use nulls to store data with similar semantics, and consider values not fit different kinds of special values. It's not so productive to avoid using SQL features because you are using them as placeholders for values not even fit in the SQL logic.  
> **username_14** (2024-01-01): @qwr And if you really do follow that recommendation, that you don't use outer joins at all when nulls are present in the data, it means as long as you use outer joins, nulls are only used for what I wrote and you don't need to consider anything else, as they are not present elsewhere. So I don't quite understand how it changes anything in my answer.  
---
