Lưu ý mệnh đề NOT IN trong SQL

Xin chào mọi người! Hôm nay mình vừa gặp một vấn đề tốn kha khá thời gian liên quan tới SQL. Thật ra đây không phải lần đầu mình gặp phải nó, tuy nhiên khi làm việc rất dễ quên vấn đề này. Đó là sử dụng mệnh đề NOT IN trong SQL mà dữ liệu có thể chứa giá trị NULL.

Giả sử bạn đang phải truy vấn một câu query như sau: SELECT … WHERE x NOT IN (…). Bạn có chắc chắn hiểu điều gì sẽ xảy ra khi mà “x” sẽ có giá trị NULL không? Mình cùng tìm hiểu nhé!

Trường hợp đơn giản nhất: “x” là cột được tạo bằng mệnh đề NOT NULL khi tạo bảng. Ngon rồi, chúng sẽ không bao giờ NULL và bạn không phải bận tâm đến nữa

Tiếp theo chúng ta sẽ xem xét trường hợp phức tạp hơn: “x” có thể nhận giá trị NULL. Sự phức tạp bắt nguồn từ thực tế rằng NULL có thể được hiểu là không xác định, hoặc là bất cứ thứ gì và do đó, quan điểm của SQL là không thể biết liệu NULL có bằng “ngôi nhà”, “máy bay” hay “quyển sách” hay không. Câu trả lời không phải là TRUE, cũng không phải FALSE, mà là UNKNOWN, và SQL in dưới dạng NULL

À, để tiếp tục thì chúng ta nên nhớ hai điều sau về SQL:

• Mệnh đề WHERE kiểm tra một điều kiện đối với một hàng và cho phép hàng đó vượt qua khi và chỉ khi điều kiện này trả về TRUE (nó không chấp nhận FALSE và UNKNOWN).
• NOT(TRUE) là FALSE, NOT(FALSE) là TRUE, và NOT(UNKNOWN) là UNKNOWN.

Chúng ta cùng xem xét ví dụ cụ thể:

create table houses (address varchar(100) not null, heating varchar(30));

Đây là một danh sách các ngôi nhà, và đối với mỗi ngôi nhà, chúng ta biết loại năng lượng được sử dụng để sưởi ấm (có thể là: than, gỗ, khí ga,… hoặc NULL: không sưởi ấm).

Lấy danh sách tất cả ngôi nhà sưởi ấm bằng than và gỗ:

select * from houses where heating in ("coal", "wood");

Thường thì chúng ta lấy danh sách còn lại như sau:

select * from houses where heating not in ("coal", "wood");

Chúng ta thêm ngôi nhà A không có hệ thống sưởi ấm và ngôi nhà B dùng dầu hỏa để sưởi:

insert into houses values ("A", NULL), ("B", "oil");

Khi test thử với nhà A thì:

• heating IN (“coal”, “wood”) -> UNKNOWN. Do: SQL hiểu rằng NULL có thể là than, có thể là gỗ hoặc bất cứ thứ gì và nó không biết chính xác
• heating NOT IN (“coal”, “wood”) -> UNKNOWN. Do: áp dụng “NOT(UNKNOWN) là UNKNOWN”

Kết quả là:

select * from houses where heating IN ("coal", "wood")
  -- NO ROW
select * from houses where heating NOT IN ("coal", "wood")
  -- (B,"oil")

Do mệnh đề WHERE sẽ loại bỏ những hàng mà khi xem xét điều kiện không trả về TRUE nên cả hai câu SELECT ở trên đều không thể lấy ra nhà A. Đây là đúng theo quan điểm của SQL, vấn đề là liệu nó có đúng với quan điểm của người thiết kế và truy xuất dữ liệu không?

Nếu đúng thì tốt quá, không cần xem xét thêm nữa

Nhưng một số người trong đó có cả mình đã từng không mong đợi một kết quả như vậy. Mình đã từng gặp lỗi và không hiểu tại sao khi cả điều kiện IN và NOT IN đều không lấy ra được nhà A

Bản chất của vấn đề: khi thiết kế bảng “houses”, ý của mình: NULL để hiểu rằng ngôi nhà đó không cần (không có) hệ thống sưởi ấm. Tuy nhiên SQL lại hiểu rằng NULL có thể là than, gỗ hoặc bất cứ thứ gì

Nếu theo ý định của mình thì NULL chắc chắn không phải than hay gỗ, và mình mong muốn điều kiện NOT IN sẽ trả về ngôi nhà A. Rất may là SQL cũng có một giải pháp: thay vì dùng NOT IN, ta sẽ dùng IN IS NOT TRUE

select * from houses where heating IN ("coal", "wood") IS NOT TRUE;

Câu lệnh này sẽ giúp chúng ta lấy được những hàng mà điều kiện IN trả về FALSE hoặc UNKNOW, và sẽ lấy về cả nhà A và B như chúng ta mong đợi 😁

Bài viết của mình đến đây là kết thúc rồi. Hi vọng sau bài viết, mình và các bạn sẽ không còn bối rối khi gặp vấn đề này nữa