A Brief Discussion on NULL Evaluation in SQL Server WHERE Clauses
Recently at work, I heard something quite surprising: a colleague did not know that when checking if a value is NULL in a SQL WHERE clause, one must use IS NULL instead of = NULL. To me, this is basic knowledge. In my understanding, whether learning databases from a teacher or self-studying from a SQL book, this is something one should know when learning about WHERE clauses. I can only say that, in this regard, ORMs have spoiled some people.
What surprised me even more was that a colleague with more seniority than me was also unaware of this. However, thinking about it carefully, perhaps I didn't hear clearly during the meeting and misunderstood; maybe they were just checking someone else's error rather than not knowing it themselves.
Therefore, I decided to pull my lovely junior colleague aside to supplement some basic knowledge, and write a note for her to read. In reality, I wanted to confirm the details myself.
Comparison Results of NULL
First, many people mistakenly believe that the logical comparison results in SQL are only TRUE and FALSE, but in reality, there is also UNKNOWN. Although I knew that NULL checks required IS NULL or IS NOT NULL when I first learned SQL, I only learned about the concept of UNKNOWN a few months ago.
Because NULL represents an unknown value, any comparison between a value (including NULL) and NULL will result in UNKNOWN, except when using IS NULL or IS NOT NULL. In a WHERE clause, only data that evaluates to TRUE will be included in the query results.
Logical Operations of UNKNOWN
The following table lists the results of Expression 1 AND Expression 2 when one of the expressions has a value of UNKNOWN.
| Expression 1 | Expression 2 | Result |
|---|---|---|
| TRUE | UNKNOWN | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN |
| FALSE | UNKNOWN | FALSE |
The following table lists the results of Expression 1 OR Expression 2 when one of the expressions has a value of UNKNOWN.
| Expression 1 | Expression 2 | Result |
|---|---|---|
| TRUE | UNKNOWN | TRUE |
| UNKNOWN | UNKNOWN | UNKNOWN |
| FALSE | UNKNOWN | UNKNOWN |
To be honest, it's a bit hard to remember, so I suggest trying to avoid involving UNKNOWN in your logic whenever possible.
SQL Standards and Not-Equal Operators
This section is unrelated to the main topic but is recorded here for reference.
SQL Standards
Common SQL standards include:
- ANSI SQL: The SQL standard established by the American National Standards Institute (ANSI).
- T-SQL: Microsoft SQL Server's implementation of the ANSI SQL standard, with additional features and extensions.
- PL/SQL: Oracle's implementation of the ANSI SQL standard, with additional features and extensions.
Not-Equal Operators
In early ANSI SQL standards, <> was the only explicitly defined not-equal operator. Since SQL-92, some database systems have gradually supported != as an optional not-equal operator. It is speculated that this is because other programming languages use != as the not-equal operator, but I am not sure if it was subsequently included in the SQL standard. There are still a very small number of databases, such as Microsoft Access, that do not support !=.
Although SQL Server supports !=, the official documentation still uses <> as the standard not-equal operator.
References
Change Log
- 2024-07-24 Initial document creation.