Một truy vấn chạy không có lỗi và trả về một số có vẻ hợp lý, nhưng các hàng bị thiếu và giá trị trung bình sai. Các giá trị SQL NULL thường là nguyên nhân. NULL không hoạt động giống như bất kỳ giá trị bình thường nào và điều đó thu hút cả những người mới bắt đầu cũng như các nhà phát triển có kinh nghiệm.

Hướng dẫn này trình bày chính xác cách hoạt động của NULL. Chúng tôi đã chạy mọi truy vấn trong SQLite 3.50 và kết quả hiển thị là từ lần chạy đó. Hầu hết hành vi này là SQL tiêu chuẩn và chúng tôi lưu ý những điểm khác nhau của cơ sở dữ liệu, chẳng hạn như thứ tự sắp xếp NULL và cách xử lý các chuỗi trống của Oracle.

NULL là gì?

NULL là điểm đánh dấu có nghĩa là giá trị bị thiếu hoặc không xác định. Nó không phải là một giá trị theo nghĩa thông thường, vì vậy đây hoàn toàn là những thứ khác nhau:

Sự khác biệt đặc biệt quan trọng đối với số lượng. Tiền thưởng bằng 0 có nghĩa là người đó không nhận được gì. Phần thưởng NULL có nghĩa là chưa có ai biết.

Bảng mẫu

CREATE TABLE employees (
  id INT, name TEXT, phone TEXT, bonus INT, manager_id INT
);

INSERT INTO employees VALUES
 (1,'Meera','555-0101',500,NULL),
 (2,'Arjun',NULL,300,1),
 (3,'Sara','',NULL,1),
 (4,'Ravi',NULL,NULL,2);

Lưu ý rằng Sara có một chuỗi trống cho điện thoại, trong khi Arjun và Ravi có NULL. Hai người được đối xử rất khác nhau.

Kiểm tra NULL

Đây là lỗi mà hầu hết mọi người đều mắc phải lúc đầu:

SELECT name FROM employees WHERE phone = NULL;
-- (no rows)

SELECT name FROM employees WHERE phone IS NULL;
-- Arjun
-- Ravi

SELECT name FROM employees WHERE phone IS NOT NULL;
-- Meera
-- Sara

Truy vấn đầu tiên là SQL hợp lệ và không có lỗi. Đơn giản là nó không trả về gì cả, bởi vì so sánh bất cứ thứ gì với NULL là không bao giờ đúng. Sara xuất hiện dưới IS NOT NULL, vì chuỗi trống là giá trị thực. Tuy nhiên, trong Oracle, một chuỗi trống được coi là NULL, đây là một ngoại lệ đáng chú ý.

Logic ba giá trị

Trong hầu hết các ngôn ngữ lập trình, điều kiện là đúng hoặc sai. SQL thêm kết quả thứ ba: không xác định. Nếu bạn không biết một giá trị, bạn không thể nói liệu nó có bằng 5 hay không. Và hai giá trị chưa biết không được coi là bằng nhau, đó là lý do tại sao NULL = NULL không đúng. Trong thử nghiệm của chúng tôi, tất cả những thứ này đều trả về NULL:

SELECT NULL = NULL, NULL <> 1, 1 + NULL, 'a' || NULL;

-- NULL | NULL | NULL | NULL

Điều kiện kết hợp tuân theo các quy tắc cẩn thận. Đây là kết quả từ lần chạy của chúng tôi, trong đó 1 là đúng và 0 là sai:

Các hàng biến mất khỏi WHERE

WHERE chỉ giữ một hàng nếu điều kiện của nó là true. Các hàng có kết quả không xác định sẽ bị loại bỏ, cùng với những hàng sai. Điều này dẫn đến kết quả đáng ngạc nhiên:

SELECT name FROM employees WHERE phone <> '555-0101';

-- Sara

Bạn có thể mong đợi Arjun và Ravi sẽ xuất hiện vì số điện thoại của họ chắc chắn không phải là số đó. Nhưng điện thoại của họ không rõ nên kết quả không xác định và họ biến mất. Để bao gồm chúng, hãy nói rõ ràng: WHERE phone <> '555-0101' OR phone IS NULL. Phủ định cũng không giúp được gì:

SELECT name FROM employees WHERE NOT (bonus > 400);

-- Arjun

Sara và Ravi không có phần thưởng nên họ không xuất hiện trong bonus > 400 cũng như không xuất hiện trong phần đối lập. Đây là điều quan trọng cần nhớ: một điều kiện và sự phủ định của nó không phải lúc nào cũng bao gồm mọi hàng.

COALESCE: cung cấp một phương án dự phòng

COALESCE trả về giá trị không NULL đầu tiên trong danh sách của nó và nó hoạt động trên tất cả các cơ sở dữ liệu chính:

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- third

SELECT name, COALESCE(phone, 'no phone') FROM employees;
-- Meera | 555-0101
-- Arjun | no phone
-- Sara  |
-- Ravi  | no phone

Sara vẫn hiển thị trống vì chuỗi trống không phải là NULL. Các hàm tương tự tồn tại, chẳng hạn như IFNULL trong MySQL và SQLite, ISNULL trong SQL Server và NVL trong Oracle. COALESCE là sự lựa chọn di động. Quan điểm trước đây của chúng tôi về tính toán cũng được áp dụng ở đây. bonus + 100 cung cấp NULL cho bất kỳ ai không có tiền thưởng, vì vậy hãy viết COALESCE(bonus, 0) + 100 nếu coi khoản tiền thưởng bị thiếu là 0 là đúng cho doanh nghiệp của bạn.

NULLIF: biến một giá trị thành NULL

NULLIF(a, b) trả về NULL nếu hai giá trị bằng nhau và nếu không thì trả về a. Hai công dụng đặc biệt hữu ích. Nó có thể dọn sạch các giá trị giữ chỗ:

SELECT name, NULLIF(phone, '') FROM employees WHERE id = 3;
-- Sara | NULL

SELECT name FROM employees
WHERE COALESCE(NULLIF(phone, ''), '') = '';
-- Arjun
-- Sara
-- Ravi

Truy vấn thứ hai đó là một cách thuận tiện để tìm các giá trị “thiếu hoặc trống”. Nó cũng có thể ngăn chặn việc chia cho 0, điều này gây ra lỗi trong nhiều cơ sở dữ liệu:

SELECT total / NULLIF(quantity, 0) FROM sales;

Khi quantity bằng 0, điều này mang lại giá trị NULL thay vì thất bại. Trong thử nghiệm của chúng tôi, SELECT 100 / NULLIF(0, 0) trả về NULL.

Tổng hợp bỏ qua NULL

Đây là nơi NULL gây thiệt hại nhiều nhất cho các báo cáo:

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus),
       AVG(COALESCE(bonus, 0))
FROM employees;

-- 4 | 2 | 800 | 400.0 | 200.0

Vậy tiền thưởng trung bình là bao nhiêu, 400 hay 200? Nó phụ thuộc vào ý nghĩa của NULL trong dữ liệu của bạn. Nếu NULL có nghĩa là “không trả tiền thưởng”, thì 200 là đúng. Nếu nó có nghĩa là “chưa quyết định” thì 400 có thể đúng. Cơ sở dữ liệu không thể đưa ra quyết định này cho bạn, vì vậy câu hỏi phải được gửi đến người biết về doanh nghiệp.

Một cái bẫy liên quan: SUM không có giá trị nào là NULL thay vì 0. Khi chúng tôi chỉ tính tổng các hàng không có phần thưởng, chúng tôi nhận được NULL, trong khi COUNT trả về 0. Mã ứng dụng mong đợi một số có thể gặp sự cố với điều này, vì vậy hãy gói nó lại: COALESCE(SUM(bonus), 0).

NHÓM THEO, sắp xếp và KHÁC BIỆT

Mặc dù các NULL “không bằng” khi so sánh, nhưng các thao tác này coi chúng là một nhóm:

SELECT bonus, COUNT(*) FROM employees GROUP BY bonus ORDER BY bonus;
-- NULL | 2
--  300 | 1
--  500 | 1

SELECT DISTINCT bonus FROM employees;
-- 500, 300, NULL

Xem SQL GROUP BY và HAVING để biết thêm về cách nhóm. Thứ tự sắp xếp là nơi cơ sở dữ liệu thực sự khác nhau. SQLite và MySQL coi NULL là giá trị nhỏ nhất, trong khi PostgreSQL và Oracle coi nó là giá trị lớn nhất. Vì vậy, ORDER BY bonus DESC tương tự có thể đặt NULL cuối cùng trong cái này và đầu tiên trong cái khác. PostgreSQL, Oracle và SQLite hiện đại cho phép bạn nêu điều này một cách trực tiếp bằng cách sử dụng NULLS FIRST hoặc NULLS LAST.

Tham gia

Điều kiện nối sử dụng = không bao giờ khớp với NULL, vì vậy các hàng có khóa NULL sẽ bị loại khỏi phép nối bên trong:

SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id;
-- Arjun, Sara, Ravi    (Meera is gone)

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- Meera | NULL
-- Arjun | Meera
-- Sara  | Meera
-- Ravi  | Arjun

Meera không có người quản lý, do đó, một kết nối bên trong sẽ âm thầm loại bỏ hàng đó. A LEFT JOIN giữ nó và nó cũng tạo ra các NULL của riêng nó cho bên bị thiếu. Đọc thêm trong Giải thích về các phép nối SQL.

Bẫy KHÔNG VÀO

Một NULL duy nhất trong danh sách NOT IN sẽ xóa sạch mọi kết quả:

SELECT name FROM employees WHERE manager_id NOT IN (1);
-- Ravi

SELECT name FROM employees WHERE manager_id NOT IN (1, NULL);
-- (no rows)

Dạng thứ hai có nghĩa là manager_id <> 1 AND manager_id <> NULL và phần cuối cùng đó không bao giờ có thể đúng. Điều này đặc biệt nguy hiểm với các truy vấn phụ, trong đó NULL có thể đến mà không được chú ý. Thay vào đó, hãy sử dụng NOT EXISTS, như được giải thích trong Hướng dẫn về truy vấn con SQL của chúng tôi. Meera cũng bị thiếu trong kết quả đầu tiên, vì manager_id của hàng đó là NULL.

Thiết kế với NULL

• Sử dụng NOT NULL theo mặc định. Chỉ cho phép NULL khi một giá trị thực sự có thể bị thiếu.
• Thêm các giá trị mặc định hợp lý. Ví dụ: quantity INT NOT NULL DEFAULT 0.
• Không sử dụng các giá trị ma thuật. Ngày 1900-01-01 hoặc độ tuổi -1 thay thế cho “không xác định” sẽ làm hỏng tính toán của bạn.
• Biết hành vi ĐỘC ĐÁO. Hầu hết các cơ sở dữ liệu cho phép một số giá trị NULL trong một cột UNIQUE và thử nghiệm của chúng tôi đã chèn hai giá trị không có lỗi. SQL Server là một ngoại lệ, vì nó thường cho phép một ngoại lệ.
• Hãy nhớ rằng khóa chính không thể là NULL. Xem khóa chính và khóa ngoại.
• Thử nghiệm với NULL. Dữ liệu thử nghiệm không có NULL sẽ che giấu những lỗi này. Hãy cố ý đưa chúng vào, giống như cách bạn làm với các trường hợp đặc biệt khác.

Lời kết

Hãy coi NULL là “không xác định” và hầu hết hành vi của nó bắt đầu có ý nghĩa: không xác định cộng với một là không xác định và không xác định bằng không xác định là không được biết là đúng. Kiểm tra một trong các báo cáo của riêng bạn trong tuần này. Kiểm tra các cột có thể rỗng bằng cách sử dụng COUNT(*) so với COUNT(column), tìm bất kỳ NOT IN nào và quyết định ý nghĩa của NULL trong mỗi mức trung bình.

Câu hỏi thường gặp

Tiếp tục học hỏi