Understanding SQL NULL: Three-Valued Logic, IS NULL vs = NULL, and Common Pitfalls

What NULL Represents

NULL indicates a missing or unknown value in a relational table; it is not a concrete data type. A NULL field is empty, and it differs from 0 or an empty string.

Two Conceptual Types of NULL

Although SQL defines only one NULL, it is often helpful to think of it as belonging to two categories:

Unknown – the value exists but is not known (e.g., a person’s eye colour hidden behind sunglasses).

Not applicable – the attribute does not make sense for the entity (e.g., a refrigerator’s eye colour).

This classification originates from E.F. Codd. The diagram below illustrates his view of “lost information”.

Three‑Valued Logic in SQL

SQL uses three logical values: true, false, and unknown. The unknown value arises when a predicate involves NULL. It is distinct from the Boolean unknown used in programming languages.

To differentiate, the Boolean value is written in lowercase unknown, while the NULL‑related state is written as uppercase UNKNOWN. For example, unknown = unknown → true but UNKNOWN = UNKNOWN → unknown.

Truth tables for the three logical operators are shown below.

Operator precedence in three‑valued logic is:

AND: false > unknown > true OR: true > unknown >

false

Why Use IS NULL Instead of = NULL

Comparing a column with = NULL (or any other comparison operator) always yields unknown. Since a WHERE clause returns rows only when the predicate evaluates to true, such a condition never matches any rows.

Example table and data:

DROP TABLE IF EXISTS t_sample_null;
CREATE TABLE t_sample_null (
  id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  name VARCHAR(50) NOT NULL COMMENT '名称',
  remark VARCHAR(500) COMMENT '备注',
  PRIMARY KEY(id)
) COMMENT 'NULL样例';
INSERT INTO t_sample_null(name, remark)
VALUES('zhangsan', '张三'),('李四', NULL);

Incorrect query:

SELECT * FROM t_sample_null WHERE remark = NULL;

This runs without error but returns no rows because the predicate evaluates to unknown. The correct predicate is:

SELECT * FROM t_sample_null WHERE remark IS NULL;

CASE Expressions and NULL

A simple CASE that tests for NULL will never match because col_1 = NULL is unknown. Use a searched CASE instead:

CASE WHEN col_1 = 1 THEN 'o'
     WHEN col_1 IS NULL THEN 'x'
END

NOT IN vs NOT EXISTS

While IN can be safely rewritten as EXISTS, the opposite is not true for the negated forms. If the subquery used by NOT IN returns any NULL values, the whole predicate evaluates to unknown, producing an empty result set.

Example tables:

-- t_student_A
DROP TABLE IF EXISTS t_student_A;
CREATE TABLE t_student_A (
  id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  name VARCHAR(50) NOT NULL COMMENT '名称',
  age INT(3) COMMENT '年龄',
  city VARCHAR(50) NOT NULL COMMENT '城市',
  remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY(id)
) COMMENT '学生信息';
INSERT INTO t_student_A(name, age, city) VALUES
('zhangsan',25,'深圳市'),('wangwu',60,'广州市'),
('bruce',32,'北京市'),('yzb',NULL,'深圳市'),('boss',43,'深圳市');

-- t_student_B
DROP TABLE IF EXISTS t_student_B;
CREATE TABLE t_student_B (
  id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  name VARCHAR(50) NOT NULL COMMENT '名称',
  age INT(3) COMMENT '年龄',
  city VARCHAR(50) NOT NULL COMMENT '城市',
  remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY(id)
) COMMENT '学生信息';
INSERT INTO t_student_B(name, age, city) VALUES
('马化腾',45,'深圳市'),('马三',25,'深圳市'),
('马云',43,'杭州市'),('李彦宏',41,'深圳市'),
('年轻人',25,'深圳市');

Query using NOT IN to find B‑class students whose age differs from any A‑class student in Shenzhen returns no rows because age in A includes a NULL:

SELECT * FROM t_student_B B
WHERE age NOT IN (
  SELECT age FROM t_student_A WHERE city = '深圳市'
);

Step‑by‑step transformation shows the predicate becomes unknown and the whole WHERE clause never evaluates to true. The proper solution is to use NOT EXISTS:

SELECT * FROM t_student_B B
WHERE NOT EXISTS (
  SELECT * FROM t_student_A A
  WHERE B.age = A.age AND A.city = '深圳市'
);

This query correctly returns the rows for “马化腾” and “李彦宏”.

Other Common NULL Traps

Mixing NULL with comparison predicates (e.g., = NULL, <> NULL) always yields unknown.

Using NOT IN when the subquery may produce NULL values leads to empty results.

Aggregates and functions that ignore NULL can produce misleading statistics.

Summary

NULL represents missing or unknown data; it is not a value and cannot be compared with ordinary predicates.

Predicates involving NULL evaluate to unknown, which never satisfies a WHERE clause. IS NULL (or IS NOT NULL) is the proper way to test for NULL.

Prefer adding NOT NULL constraints to columns whenever possible to avoid NULL‑related logic issues.