Toán tử và NULL trong SQLite: IS NULL, COALESCE, IFNULL

NULL nghĩa là "không xác định"

Mọi giá trị khác trong SQLite đều biểu diễn một thứ cụ thể: một số, một chuỗi, một blob. NULL thì khác. Nó là chỗ giữ cho một giá trị bị thiếu hoặc chưa biết. Chỉ một ý đó thôi đã giải thích mọi hành vi kỳ lạ của NULL trong truy vấn.

Hãy tạo một bảng nhỏ để thử nghiệm:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT * FROM users;

Hai cột cho phép null. Boris không có email. Cleo không có tuổi. Dan không có cả hai. Phần còn lại của trang nói về cách truy vấn những hàng như vậy mà không bị đánh lừa.

= và <> không dùng được với NULL

Phản xạ đầu tiên của bạn là viết WHERE email = NULL. Trông có vẻ hợp lý. Nhưng nó không trả về gì:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email = NULL;

Không có hàng nào, dù rõ ràng Boris và Dan có email null. Lý do: so sánh bất cứ thứ gì với NULL đều cho ra NULL, không phải đúng hay sai. Mệnh đề WHERE của SQLite chỉ giữ những hàng có điều kiện là đúng, mà NULL thì không phải đúng. Vì vậy hàng đó bị lọc mất.

Cái bẫy tương tự với <>:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email <> 'ada@example.com';

Bạn sẽ nghĩ câu này trả về mọi người trừ Ada. Nhưng nó chỉ trả về Cleo. Boris và Dan, có email null, bị loại, vì NULL <> 'ada@example.com' cũng là NULL, không phải đúng.

Đây là cái bẫy phổ biến nhất trong SQL. Bất cứ khi nào một truy vấn "mất hàng" ngoài dự kiến, hãy nghi ngờ một cột null.

Dùng IS NULL và IS NOT NULL

Cách đúng để kiểm tra null là toán tử IS. Khác với =, nó hiểu null và trả về đúng hoặc sai, không bao giờ trả về null:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name FROM users WHERE email IS NULL;

SELECT name FROM users WHERE email IS NOT NULL;

Truy vấn đầu trả về Boris và Dan. Truy vấn thứ hai trả về Ada và Cleo. IS NULL và IS NOT NULL là hai toán tử được tạo ra riêng để hỏi "giá trị này có bị thiếu không?". Hãy dùng chúng ở bất cứ chỗ nào bạn định viết = NULL hay <> NULL.

Nếu muốn "không phải Ada, tính cả những giá trị chưa biết", hãy kết hợp các điều kiện một cách tường minh:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email
FROM users
WHERE email <> 'ada@example.com' OR email IS NULL;

Giờ Boris, Cleo và Dan đều xuất hiện.

NULL lan truyền qua phép tính và phép nối chuỗi

Quy tắc "không xác định" không chỉ áp dụng cho phép so sánh. Mọi phép toán chạm tới null đều cho ra null:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    age,
    age + 1       AS next_year,
    age * 2       AS doubled,
    'Hi ' || name AS greeting,
    'Age: ' || age AS labelled_age
FROM users;

next_year và doubled là null với Cleo và Dan. labelled_age cũng null với họ: nối một chuỗi với NULL cho ra NULL, không phải 'Age: '. Nếu một cột có thể null và bạn cần một giá trị dùng được ở đầu ra, bạn phải xử lý nó. Đó là lúc hai hàm tiếp theo phát huy tác dụng.

IFNULL: giá trị dự phòng với hai đối số

IFNULL(a, b) trả về a, trừ khi a là null thì trả về b. Đây là cách đơn giản nhất để thay null bằng một giá trị mặc định:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    IFNULL(email, '(no email)') AS email,
    IFNULL(age, 0)              AS age
FROM users;

Boris và Dan nhận (no email). Cleo và Dan nhận 0. Dữ liệu gốc không thay đổi: IFNULL chỉ viết lại đầu ra.

IFNULL luôn nhận đúng hai đối số. Nếu cần nhiều giá trị dự phòng hơn, hãy dùng COALESCE.

COALESCE: giá trị khác NULL đầu tiên thắng

COALESCE(a, b, c, ...) duyệt các đối số từ trái sang phải và trả về đối số đầu tiên khác null. Nó mở rộng IFNULL cho số lượng giá trị dự phòng bất kỳ:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    COALESCE(email, name || '@unknown.local', 'anonymous') AS contact
FROM users;

Với Ada và Cleo, email được dùng. Với Boris và Dan, email là null, nên SQLite thử đối số thứ hai: một địa chỉ được tạo từ tên. Nếu cả nó cũng null, kết quả sẽ rơi xuống 'anonymous'.

COALESCE là lựa chọn dùng được ở mọi nơi: mọi cơ sở dữ liệu SQL lớn đều hỗ trợ nó theo cùng một cách. IFNULL là tiện ích của SQLite/MySQL cho trường hợp hai đối số. Hãy mặc định chọn COALESCE; chỉ dùng IFNULL khi bạn thật sự chỉ có hai đối số và muốn tên ngắn hơn.

NULL không phải chuỗi rỗng

Một nhầm lẫn phổ biến: coi NULL và '' là như nhau. Chúng không giống nhau.

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    '' IS NULL          AS empty_is_null,
    NULL IS NULL        AS null_is_null,
    length('')          AS empty_length,
    length(NULL)        AS null_length,
    '' = ''             AS empty_equals_empty,
    NULL = NULL         AS null_equals_null;

'' là một chuỗi thật, chỉ là có 0 ký tự. NULL là sự vắng mặt của giá trị. length('') là 0; length(NULL) thì chính nó là NULL. Và NULL = NULL là NULL, không phải 1: đó chính là lý do IS NULL tồn tại.

Nếu một cột có thể chứa cả '' lẫn NULL, hãy quyết định cái nào nghĩa là "thiếu" và giữ nhất quán. Trộn lẫn hai cái buộc mọi truy vấn phải xử lý hai trường hợp, và chắc chắn sẽ có lúc bạn quên một trường hợp.

NULL trong IN, NOT IN và DISTINCT

Còn vài chỗ khác mà null bất ngờ gây rắc rối.

IN với một danh sách có chứa null có thể cho kết quả đáng ngạc nhiên, nhất là với NOT IN:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, age FROM users WHERE age NOT IN (25, NULL);

Bạn có thể nghĩ sẽ nhận được mọi người có tuổi khác 25. Nhưng bạn chẳng nhận được gì. SQLite triển khai NOT IN (25, NULL) gần giống age <> 25 AND age <> NULL, mà age <> NULL luôn là NULL, nên toàn bộ điều kiện không bao giờ đúng. Cách sửa là lọc null khỏi danh sách (hoặc khỏi cột) trước khi so sánh.

Ngược lại, DISTINCT coi các giá trị null là bằng nhau khi loại bỏ trùng lặp:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT DISTINCT email FROM users;

Bạn nhận được ba hàng: email của Ada, email của Cleo và một NULL duy nhất (gộp từ Boris và Dan). GROUP BY và UNION cũng vậy: chúng coi các null là một nhóm, ngược hẳn với cách = xử lý. SQL không phải lúc nào cũng nhất quán ở điểm này; nên biết rõ mỗi toán tử nằm ở phía nào.

Danh sách kiểm tra nhanh

• Kiểm tra giá trị bị thiếu bằng IS NULL / IS NOT NULL. Không bao giờ dùng = NULL.
• Mọi phép tính, phép nối chuỗi hay phép so sánh chạm tới NULL đều trả về NULL.
• Dùng COALESCE(a, b, c, ...) để thay null bằng giá trị dự phòng. Dùng IFNULL(a, b) cho cách viết tắt hai đối số.
• Chuỗi rỗng '' không giống NULL. Ở mỗi cột, hãy chọn một trong hai để biểu thị "thiếu".
• NOT IN (..., NULL) gần như luôn là bug. Hãy loại null khỏi danh sách trước.

Tiếp theo: sắp xếp kết quả

Khi đã lọc hàng đúng cách, kể cả các hàng null, bước tiếp theo là sắp xếp chúng theo thứ tự hữu ích. ORDER BY là nội dung của trang tiếp theo, và nó có quy tắc riêng về vị trí của null trong kết quả đã sắp xếp.