31
$\begingroup$

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?

$\endgroup$
21
  • 22
    $\begingroup$ The same is true for the null arithmetic value (usually called "NaN") in many languages, so SQL is hardly unique here. $\endgroup$ Dec 21, 2023 at 16:17
  • 9
    $\begingroup$ SQL uses null for conflated purposes. Sometimes it means missing data, and other times it means not applicable. $\endgroup$
    – Erik Eidt
    Dec 21, 2023 at 16:42
  • 13
    $\begingroup$ @TobySpeight Not really; NaN == NaN is false, not NaN. $\endgroup$ Dec 21, 2023 at 18:52
  • 6
    $\begingroup$ @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 $\endgroup$
    – mousetail
    Dec 21, 2023 at 18:55
  • 11
    $\begingroup$ @KarlKnechtel But the main point is that it's not true even though it seems like it should be. $\endgroup$
    – Barmar
    Dec 21, 2023 at 22:49

7 Answers 7

39
$\begingroup$

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.

$\endgroup$
7
  • $\begingroup$ 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. $\endgroup$ Dec 22, 2023 at 0:26
  • 9
    $\begingroup$ @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. $\endgroup$
    – JoL
    Dec 22, 2023 at 0:43
  • 7
    $\begingroup$ @MichaelTsang In this hypothetical case some cars are unregistered and have no license plate $\endgroup$
    – mousetail
    Dec 22, 2023 at 7:17
  • 9
    $\begingroup$ 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 $\endgroup$ Dec 22, 2023 at 12:46
  • 3
    $\begingroup$ @P.Hopkinson: Well spotted. This is not unique to IBM's database, it appears to be specified in the SQL Standard. SELECT NULL UNION SELECT NULL only returns only one row in all DB systems I am familiar with. $\endgroup$
    – Heinzi
    Dec 23, 2023 at 8:06
23
$\begingroup$

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:

  1. Is value A not definitely the same as value B? (Strict inverse of question 1)
  2. 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.

$\endgroup$
1
12
$\begingroup$

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

$\endgroup$
7
  • $\begingroup$ 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. $\endgroup$ Dec 22, 2023 at 9:39
  • $\begingroup$ @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. $\endgroup$
    – Steve
    Dec 22, 2023 at 11:34
  • $\begingroup$ @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. $\endgroup$
    – IMSoP
    Dec 22, 2023 at 13:04
  • $\begingroup$ @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. $\endgroup$
    – Steve
    Dec 22, 2023 at 15:14
  • 5
    $\begingroup$ @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). $\endgroup$
    – Kevin
    Dec 22, 2023 at 16:43
4
$\begingroup$

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.

$\endgroup$
15
  • $\begingroup$ 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. $\endgroup$
    – IMSoP
    Dec 22, 2023 at 8:12
  • $\begingroup$ @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. $\endgroup$
    – Quassnoi
    Dec 22, 2023 at 9:13
  • $\begingroup$ 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. $\endgroup$
    – IMSoP
    Dec 22, 2023 at 9:56
  • 1
    $\begingroup$ @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. $\endgroup$
    – Steve
    Dec 22, 2023 at 11:08
  • 1
    $\begingroup$ @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. $\endgroup$
    – IMSoP
    Dec 22, 2023 at 12:54
3
$\begingroup$

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.

$\endgroup$
3
$\begingroup$

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.

$\endgroup$
2
  • 2
    $\begingroup$ 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. $\endgroup$
    – Steve
    Dec 28, 2023 at 10:46
  • $\begingroup$ @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. $\endgroup$
    – qwr
    Dec 29, 2023 at 0:26
0
$\begingroup$

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.

$\endgroup$
3
  • $\begingroup$ 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. $\endgroup$
    – qwr
    Dec 26, 2023 at 19:33
  • $\begingroup$ @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. $\endgroup$
    – user23013
    Jan 1 at 20:39
  • $\begingroup$ @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. $\endgroup$
    – user23013
    Jan 1 at 20:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .