WHERE + NULL three-valued logic — vì sao age != 30 không trả NULL row

Bạn query SELECT * FROM users WHERE age != 30 — không một user nào có age IS NULL được trả về. Không có error, không có warning. Chỉ im lặng. Bug? Hay đúng spec?

Codd 1979 mở rộng relational model để cover "missing data" — và spec chính thức nói NULL không phải value, là "không biết". Hệ quả: mọi so sánh với NULL không cho ra TRUE hay FALSE mà cho ra UNKNOWN — và WHERE chỉ pass row khi predicate là TRUE. Bài này giải thích three-valued logic, 5 NULL pitfall thực chiến (đặc biệt NOT IN, COUNT, JOIN), và defensive query pattern để không bao giờ bị bất ngờ nữa.

1. Analogy — NULL là "không biết", không phải "0"

Hình dung survey khảo sát nhân viên với câu hỏi "Bạn bao nhiêu tuổi?". Có ba loại phản hồi: điền số tuổi thật, điền số 0 (trả lời nhưng câu trả lời vô nghĩa), và bỏ trống (chưa trả lời — không biết). NULL tương ứng với trường hợp thứ ba: ô bỏ trống. Không phải 0. Không phải empty string. Là "dữ liệu còn thiếu, chưa biết".

2. Three-valued logic — TRUE/FALSE/UNKNOWN

SQL không dùng logic hai giá trị (Boolean thông thường) mà dùng three-valued logic: TRUE, FALSE, và UNKNOWN. UNKNOWN xuất hiện bất cứ khi nào một toán hạng trong phép so sánh là NULL.

Hệ quả đáng nhớ nhất không phải bản thân UNKNOWN, mà là chuyện xảy ra khi bạn chạy hai điều kiện nghe như bù nhau trên cùng một bảng:

Bảng truth table — AND:

Bảng truth table — OR:

Bảng truth table — NOT:

Quy tắc cốt lõi: WHERE chỉ pass row khi predicate = TRUE. UNKNOWN bị treat như FALSE → NULL row bị loại.

Demo trên TaskFlow:

-- Row co assignee_id = NULL khong duoc tra ve
SELECT * FROM tasks WHERE assignee_id != 5;
-- Vi sao: NULL != 5 -> UNKNOWN -> WHERE loai row nay

-- Kiem tra: co bao nhieu task chua assigned?
SELECT COUNT(*) FROM tasks WHERE assignee_id IS NULL;
-- Dung IS NULL thay vi = NULL

3. Pitfall 1 — NULL = NULL không phải TRUE

Lỗi phổ biến nhất: dùng = NULL thay vì IS NULL.

-- WRONG: khong tra ve gi ca
SELECT col FROM t WHERE col = NULL;
-- Ly do: NULL = NULL -> UNKNOWN -> WHERE loai

-- RIGHT: IS NULL tra ve TRUE khi col la NULL
SELECT col FROM t WHERE col IS NULL;

-- Kiem tra nhanh trong psql
SELECT NULL = NULL;    -- NULL (UNKNOWN)
SELECT NULL IS NULL;   -- TRUE

SELECT NULL != NULL;   -- NULL (UNKNOWN)
SELECT NULL IS NOT NULL; -- FALSE

Kể cả NULL = NULL cũng không phải TRUE. Hai giá trị "không biết" không nhất thiết bằng nhau — vì cả hai đều không biết. Đây là lý do SQL dùng IS NULL thay vì = NULL để kiểm tra.

Thử ngẫmcode review của bạn thấy đồng nghiệp viết WHERE deleted_at = NULL để lọc row chưa xoá mềm. Query này chạy không lỗi — điều đó nguy hiểm hơn hay dễ phát hiện hơn một lỗi cú pháp?

4. Pitfall 2 — NOT IN với NULL cho empty result

Đây là NULL pitfall nghiêm trọng nhất vì không có error — chỉ trả về 0 row mà không có warning.

-- Table users co 100 row, id tu 1 den 100
-- Tat ca id deu != 1, != 2, != NULL... hay khong?
SELECT * FROM users WHERE id NOT IN (1, 2, NULL);
-- Tra ve 0 row! Du table co 100 row.

Tại sao? NOT IN (1, 2, NULL) mở rộng thành:

WHERE id != 1 AND id != 2 AND id != NULL
-- id != NULL -> UNKNOWN
-- (TRUE) AND (TRUE) AND UNKNOWN -> UNKNOWN
-- WHERE loai row co UNKNOWN -> loai moi row

Mọi row đều bị loại vì id != NULL luôn là UNKNOWN, và AND UNKNOWN làm cả biểu thức thành UNKNOWN.

-- Fix 1: loc NULL ra khoi subquery truoc
SELECT * FROM users WHERE id NOT IN (1, 2);

-- Fix 2: dung NOT EXISTS thay NOT IN
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM (VALUES (1), (2), (NULL)) AS sub(v)
  WHERE sub.v = u.id
);
-- NOT EXISTS an toan voi NULL vi su dung = (khong IN)

-- Fix 3: filter NULL truoc khi subquery tra ve
SELECT * FROM users
WHERE id NOT IN (
  SELECT id FROM blacklist WHERE id IS NOT NULL
);

5. Pitfall 3 — COUNT(col) skip NULL, COUNT(*) đếm tất cả row

-- TaskFlow: dem task theo trang thai assignment
SELECT
  COUNT(*)                      AS total_rows,
  COUNT(assignee_id)            AS assigned_rows,
  COUNT(DISTINCT assignee_id)   AS unique_assignees
FROM tasks;

-- Ket qua vi du:
-- total_rows=500, assigned_rows=380, unique_assignees=12
-- 120 task co assignee_id = NULL -> COUNT(assignee_id) bo qua

Quy tắc:

• COUNT(*) — đếm mọi row, kể cả row có NULL ở bất kỳ column nào.
• COUNT(col) — chỉ đếm row có col IS NOT NULL.
• COUNT(DISTINCT col) — đếm số distinct non-NULL value của col.
• SUM, AVG, MIN, MAX — tất cả đều skip NULL tự động.

-- Vi du minh hoa su khac biet
SELECT
  AVG(score)                AS avg_with_null_skipped,
  SUM(score) / COUNT(*)     AS avg_counting_nulls_as_zero
FROM student_results;
-- Neu 3/10 row co score = NULL:
-- avg_with_null_skipped: tinh tren 7 row
-- avg_counting_nulls_as_zero: chia cho 10 -> ket qua thap hon

6. Pitfall 4 — string concat với NULL cho NULL

Trong SQL chuẩn, toán tử || (string concatenation) với một toán hạng NULL làm cả expression thành NULL. Đây là behavior mặc định của hầu hết hệ quản trị tuân thủ ANSI SQL (PostgreSQL, Oracle, DB2).

-- ANSI SQL: || operator
SELECT 'Hello, ' || NULL;       -- NULL (khong phai 'Hello, ')
SELECT 'Hello, ' || name        -- NULL neu name la NULL
FROM users WHERE name IS NOT NULL;  -- OK nhung phai loc truoc

-- Fix: COALESCE thay the NULL bang default value
SELECT 'Hello, ' || COALESCE(name, 'Friend') FROM users;
-- name = NULL -> 'Hello, Friend'
-- name = 'Alice' -> 'Hello, Alice'

Thử ngẫmtemplate email của TaskFlow ghép 'Hello, ' || name. Sau một đợt import user hàng loạt thiếu name, vì sao toàn bộ email đó im lặng gửi trống thay vì báo lỗi?

7. Pitfall 5 — JOIN ON không match NULL

-- tasks.assignee_id = NULL khong match voi user nao
SELECT t.id, t.title, u.name
FROM tasks t
LEFT JOIN users u ON t.assignee_id = u.id;
-- Task co assignee_id = NULL: u.name = NULL (khong match)
-- Day la dung behavior voi LEFT JOIN -- nhung can biet

Với INNER JOIN, task có assignee_id = NULL bị loại hoàn toàn:

-- INNER JOIN: task voi assignee_id = NULL bi loai
SELECT t.id, t.title, u.name
FROM tasks t
INNER JOIN users u ON t.assignee_id = u.id;
-- Unassigned task khong xuat hien trong ket qua

Khi cần NULL-safe comparison — tức là coi NULL = NULL là TRUE:

-- IS NOT DISTINCT FROM: NULL-safe equality (ANSI SQL:1999)
SELECT * FROM tasks t
LEFT JOIN users u ON t.assignee_id IS NOT DISTINCT FROM u.id;
-- NULL IS NOT DISTINCT FROM NULL -> TRUE -> match

-- IS DISTINCT FROM: nguoc lai cua IS NOT DISTINCT FROM
SELECT * FROM tasks WHERE assignee_id IS DISTINCT FROM 5;
-- Tra ve ca row co assignee_id != 5 VA assignee_id = NULL

IS NOT DISTINCT FROM là NULL-safe =. IS DISTINCT FROM là NULL-safe !=. Đây là cú pháp chuẩn ANSI SQL:1999 — PostgreSQL, DB2, và nhiều RDBMS hiện đại hỗ trợ; MySQL dùng <=> (NULL-safe equality operator) thay thế cho IS NOT DISTINCT FROM.

8. Defensive query patterns

Bốn pattern thực tế để xử lý NULL an toàn:

-- Pattern 1: kiem tra NULL dung cach
WHERE col IS NULL
WHERE col IS NOT NULL

-- Pattern 2: include NULL row khi filter !=
-- Thay vi: WHERE assignee_id != 5
-- Dung:
WHERE assignee_id != 5 OR assignee_id IS NULL

-- Pattern 3: COALESCE cho default value trong predicate
WHERE COALESCE(assignee_id, -1) != 5
-- NULL duoc treat nhu -1, nen -1 != 5 -> TRUE -> pass

-- Pattern 4: IS NOT DISTINCT FROM cho NULL-safe comparison (ANSI SQL:1999)
WHERE col IS NOT DISTINCT FROM other_col
-- Equivalent = nhung an toan voi NULL (dialect: MySQL dung <=>)

Khi nào dùng COALESCE vs IS NULL:

• IS NULL — khi cần kiểm tra sự vắng mặt của value.
• COALESCE(col, default) — khi muốn xử lý NULL như một default value cụ thể trong expression hoặc predicate.
• IS NOT DISTINCT FROM (hoặc <=> trong MySQL) — khi cần so sánh hai column cả hai đều có thể NULL (thường trong JOIN condition hoặc MERGE).

9. NULL trong constraint vs storage

NOT NULL constraint chặn NULL từ lúc insert — phòng ngừa tại nguồn:

-- Tao column voi NOT NULL constraint
ALTER TABLE tasks ADD COLUMN priority INT NOT NULL DEFAULT 3;
-- Insert thieu priority -> ERROR, khong insert duoc

DEFAULT value thay NULL bằng giá trị mặc định khi insert không cung cấp:

ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';
-- Insert khong co status -> status = 'active' tu dong
-- Khong bao gio co NULL trong column nay

COALESCE trong query xử lý NULL ở runtime — không sửa data, chỉ thay thế khi đọc:

SELECT COALESCE(assignee_id, 0) AS assignee_or_default FROM tasks;
-- assignee_id = NULL -> tra ve 0
-- Data trong table van la NULL

Ba cách handle missing data theo thứ tự ưu tiên:

1. NOT NULL constraint — tốt nhất, ngăn NULL vào database.
2. DEFAULT value — thay NULL bằng giá trị có ý nghĩa khi insert.
3. COALESCE trong query — fallback khi column đã nullable và không thể thay đổi schema.

Thử ngẫmbảng tasks đã có 50.000 row với priority NULL rải rác trước khi bạn thêm NOT NULL DEFAULT 3. Riêng lệnh ALTER TABLE đó có tự sửa những row cũ không?

10. Pitfall — ORM silent NULL bug

Prisma where: { age: { not: 30 } } mặc định không match NULL row — giống SQL. Nhiều developer expect ORM "thông minh hơn" nhưng ORM chỉ generate SQL chuẩn.

// KHONG tra ve user co age = null
const users = await prisma.user.findMany({
  where: { age: { not: 30 } }
});
// Generate: SELECT ... WHERE age != 30
// NULL row bi loai -- giong SQL

// Fix: explicit OR cho NULL
const users = await prisma.user.findMany({
  where: {
    OR: [
      { age: { not: 30 } },
      { age: null }
    ]
  }
});
// Generate: WHERE age != 30 OR age IS NULL
// Bao gom ca row co age = null

11. Applied — TaskFlow scenario

"List task của user khác user 5 — kể cả unassigned":

-- WRONG: bo qua unassigned task (assignee_id = NULL)
SELECT id, title, assignee_id
FROM tasks
WHERE assignee_id != 5;
-- NULL row bi loai vi NULL != 5 -> UNKNOWN -> WHERE loai

-- RIGHT: explicit NULL handling voi OR
SELECT id, title, assignee_id
FROM tasks
WHERE assignee_id != 5 OR assignee_id IS NULL;
-- Bao gom ca task chua assigned

-- ALT: COALESCE treat NULL nhu sentinel -1
SELECT id, title, assignee_id
FROM tasks
WHERE COALESCE(assignee_id, -1) != 5;
-- NULL -> COALESCE(NULL, -1) = -1 -> -1 != 5 -> TRUE -> pass
-- Luu y: chi dung -1 neu khong co user nao co id = -1

Kết quả khác nhau rõ ràng:

-- Kiem tra: bao nhieu task bi WRONG query bo qua
SELECT COUNT(*) FROM tasks WHERE assignee_id IS NULL;
-- Ket qua nay = so task bi mat trong WRONG query

12. Deep Dive — NULL semantics

13. Liên hệ các bài khác

• Bài 01 — SELECT, projection & alias: thứ tự xử lý FROM → WHERE → SELECT giải thích tại sao NULL trong WHERE ảnh hưởng trước khi SELECT clause chạy.
• Bài 03 — ORDER BY + pagination: NULL ordering (NULLS FIRST/LAST) và NULL trong cursor keyset — hai hệ quả trực tiếp của three-valued logic.
• Bài 06 — CASE, COALESCE, NULLIF: COALESCE và NULLIF là công cụ xử lý NULL trong expression — bổ sung cho các defensive pattern WHERE.
• Module 3 — JOIN, aggregation & window: OUTER JOIN và NULL propagation qua nhiều bảng — mở rộng trực tiếp từ Pitfall 5 (JOIN ON không match NULL).

14. Tóm tắt

• NULL không phải value — là sự vắng mặt của value. Không phải 0, không phải empty string, không phải false.
• Three-valued logic: mọi comparison với NULL cho kết quả UNKNOWN. WHERE chỉ pass row khi predicate = TRUE — UNKNOWN bị treat như FALSE.
• 5 pitfall thực chiến: = NULL (dùng IS NULL), NOT IN với NULL (dùng NOT EXISTS hoặc filter NULL), COUNT(col) skip NULL (COUNT(*) đếm hết), string || NULL cho NULL (dùng COALESCE), JOIN không match NULL (dùng IS NOT DISTINCT FROM nếu cần).
• Defensive pattern: WHERE col IS NULL/IS NOT NULL, WHERE col != 5 OR col IS NULL, COALESCE(col, default) trong predicate, IS NOT DISTINCT FROM cho NULL-safe equality.
• ORM không tự động include NULL row trong != value filter — cần explicit OR col IS NULL.
• NOT NULL constraint là cách tốt nhất ngăn NULL vào database; DEFAULT thay NULL khi insert; COALESCE xử lý NULL khi đọc.
• Forward link: Module 3 — JOIN, aggregation & window của khoá này phân tích NULL trap trong JOIN phức tạp hơn — OUTER JOIN và NULL propagation qua nhiều bảng.

15. Tự kiểm tra

Bài tiếp theo: ORDER BY + pagination — vì sao OFFSET 100k chậm 200x