WHERE đào sâu ③ — IS NULL và IN cho lọc NULL và tập hợp

Dữ liệu sẽ dùng — bảng customer (có NULL)

Bài đào sâu WHERE thứ 3 trình bày NULL và lọc theo tập hợp. Chúng ta sẽ làm việc với bảng customer (8 hàng) nạp từ CSV, trong đó các cột age / email / country mỗi cột đều có hàng với NULL (giá trị chưa biết). Trong các bảng thực tế, việc các cột không bắt buộc xen kẽ NULL là chuyện bình thường — "email chưa đăng ký," "country để trống," v.v.

Năm bài tập trong bài này đi qua, theo thứ tự: cách đúng để lấy NULL (IS NULL / IS NOT NULL), khớp danh sách ứng viên (IN / NOT IN), và cuối cùng là bẫy dễ bỏ sót khi dùng NOT IN trên dữ liệu chứa NULL.

Trước các bài tập, hãy xác nhận định nghĩa cột và một mẫu dữ liệu của bảng customer.

① Chạy PRAGMA table_info(customer); để kiểm tra tên cột, kiểu, và khóa chính.

② Chạy SELECT * FROM customer LIMIT 5; để xem trước 5 hàng đầu. Để ý các ô là NULL.

NULL và logic ba giá trị — true, false, unknown

Trong hầu hết các ngôn ngữ lập trình, một điều kiện hoặc là TRUE hoặc là FALSE — hai lựa chọn. SQL thêm một trạng thái nữa: NULL (chưa biết). Đây gọi là logic ba giá trị. NULL không có nghĩa là "giá trị trống"; nó nghĩa là "giá trị không được biết." Kể cả NULL = NULL cũng đánh giá thành NULL (chưa biết) — so sánh hai giá trị chưa biết để lại kết quả chưa biết.

WHERE condition chỉ giữ các hàng mà điều kiện là TRUE. Cả hàng NULL và FALSE đều bị loại trừ. Khi quy tắc đó vào đầu, bạn sẽ thấy tại sao WHERE email = NULL luôn trả về rỗng.

Tại sao `email = NULL` không trả về gì cả

So sánh email = NULL luôn trả về NULL (chưa biết) khi một trong hai vế là NULL. 'unknown' = NULL và NULL = NULL đều đánh giá thành NULL. WHERE chỉ giữ các hàng mà điều kiện là TRUE, nên các điều kiện NULL bị loại trừ, để lại 0 hàng.

Để lấy NULL, bạn cần cú pháp dành riêng column IS NULL / column IS NOT NULL. Những cái này không làm so sánh bằng — chúng kiểm tra trực tiếp xem giá trị có phải là NULL không — và kết quả luôn là TRUE hoặc FALSE.

"Không có khách hàng nào chưa rời bỏ"?

Khi lấy "số lượng khách hàng chưa rời bỏ (deleted_at IS NULL)," tôi từng viết SELECT COUNT(*) FROM user WHERE deleted_at = NULL; và kết quả trả về là 0.

Thực tế có hàng ngàn người dùng đang hoạt động, nhưng deleted_at = NULL luôn đánh giá thành NULL (chưa biết) và bị WHERE loại trừ, nên dù có bao nhiêu người dùng kết quả cũng luôn là 0. Đó là lỗi dễ mắc khi mới bắt đầu với SQL — hãy hình thành thói quen dùng `IS NULL` / `IS NOT NULL` cho mọi kiểm tra NULL.

IS NULL và IS NOT NULL — lấy các giá trị chưa biết

Để lấy các hàng có giá trị là NULL (chưa biết), dùng `column IS NULL`. Để lấy các hàng không phải NULL, dùng column IS NOT NULL. Chỉ cần nhớ không bao giờ dùng `= NULL` hoặc `<> NULL` và bạn sẽ né được bẫy logic ba giá trị.

Như đoạn mã dưới đây cho thấy, cú pháp chỉ là IS thay vì = — nhưng kết quả hoàn toàn khác. = NULL không trả về gì; IS NULL trả về đúng các hàng NULL.

-- 1) NG: = NULL không lấy được gì (luôn NULL = loại trừ)
SELECT name, email FROM customer WHERE email = NULL;
-- Kết quả: 0 hàng

-- 2) OK: IS NULL lấy các hàng NULL
SELECT name, email FROM customer WHERE email IS NULL;
-- Kết quả: 4 hàng (Bob / Dave / Frank / Henry)

-- 3) Lấy các hàng không NULL
SELECT name, email FROM customer WHERE email IS NOT NULL;
-- Kết quả: 4 hàng (Alice / Carol / Eve / Grace)

Hãy hình dung customer support muốn "danh sách các khách hàng không có email đăng ký để chúng ta có thể gửi yêu cầu đăng ký." (Khi bạn chạy đúng, phần giải thích sẽ hiện ra.)

① Từ customer, lấy các cột name và email.

② Lọc đến các hàng mà `email` là NULL.

③ Xác nhận kết quả là 4 hàng (Bob / Dave / Frank / Henry, tất cả với email NULL).

Hãy hình dung phân phối quảng cáo có target muốn "khách hàng có cả age và email đăng ký," sắp theo tuổi cao nhất trước.

① Từ customer, lấy các cột name, age, và email.

② Lọc đến các hàng mà cả `age` lẫn `email` đều không NULL.

③ Sắp theo `age` giảm dần.

④ Xác nhận kết quả là 3 hàng (Eve 42 / Grace 35 / Alice 30).

IN và NOT IN — khớp với danh sách ứng viên

Khi bạn muốn kiểm tra xem giá trị của một cột có nằm trong danh sách ứng viên — "country là Japan, US, hoặc UK" — đó là IN. Viết column IN (value1, value2, ...) trả về các hàng mà cột khớp với bất kỳ ứng viên nào. Nó nghĩa giống như column = value1 OR column = value2 OR column = value3; mọi người chọn IN thường chỉ vì khả năng đọc.

Thêm NOT IN trả về các hàng không khớp với bất kỳ ứng viên nào.

-- 1) IN: khớp ứng viên bất kỳ
SELECT name, country FROM customer
WHERE country IN ('Japan', 'US');

-- Cùng nghĩa, viết với OR
SELECT name, country FROM customer
WHERE country = 'Japan' OR country = 'US';

-- 2) NOT IN: không khớp ứng viên nào
SELECT name, country FROM customer
WHERE country NOT IN ('Japan');

Hãy hình dung nhu cầu "gửi email thông báo cho các khách hàng ở Japan hoặc US."

① Từ customer, lấy các cột name và country.

② Lọc đến các hàng mà `country` là `'Japan'` hoặc `'US'` (dùng IN).

③ Xác nhận kết quả là 5 hàng (Alice Japan / Bob US / Carol Japan / Frank Japan / Grace US).

Bẫy `NOT IN` + NULL

NOT IN ('Japan') trông như lấy "các khách hàng có country không phải Japan," nhưng nó cũng loại trừ các hàng mà `country` là NULL. Đó là vì country NOT IN ('Japan') nội bộ đánh giá là country <> 'Japan' (không bằng), và NULL <> 'Japan' trả về NULL (chưa biết). NULL không phải TRUE, nên WHERE loại bỏ hàng.

Đi xa hơn, viết NOT IN ('Japan', NULL) — với một NULL trong danh sách ứng viên — khiến mọi hàng đánh giá thành NULL và biến mất. Khi dùng NOT IN trên một cột có thể chứa NULL, luôn làm cho xử lý NULL rõ ràng (column NOT IN (...) AND column IS NOT NULL) hoặc viết lại truy vấn theo IN.

Hãy hình dung nhu cầu "tặng đặc quyền premium cho 2 khách hàng lớn tuổi nhất không-Japan."

① Từ customer, lấy các cột name, age, và country.

② Lọc đến các hàng mà `country` không phải `'Japan'` (NOT IN).

③ Sắp theo `age` giảm dần, và chỉ lấy 2 hàng đầu.

④ Xác nhận kết quả là 2 hàng (Grace 35 US / Henry 29 Italy). Lý do Eve (country NULL) bị loại trừ sẽ được giải thích trong phần kết quả.

Xây dựng trên bẫy từ Bài tập 4, hãy hình dung bạn muốn lấy cả "các khách hàng được xác nhận không sống ở Japan" và "các khách hàng có country chưa biết."

① Từ customer, lấy các cột name, age, và country.

② Bao gồm các hàng mà `country` không phải `'Japan'` cộng với các hàng mà `country` là NULL (nối bằng OR).

③ Sắp theo `age` giảm dần.

④ Xác nhận kết quả là 5 hàng (Eve 42 NULL / Grace 35 US / Henry 29 Italy / Dave 28 UK / Bob 25 US).