Why does SQL treat null = null as null, unlike all other mainstream programming languages where null = null is true?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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).

Answer 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.

Answer 5

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 6

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 (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

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. 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.

Answer 7

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.