Cách xử lý các giá trị NULL trong SQL đúng cách

Mẹo và thủ thuật để xử lý giá trị NULL trong T-SQL

Ảnh bởi Jeremy Perkins trên Hủy kết nối

Lời nói đầu

Một người bạn mới bắt đầu học SQL đã hỏi tôi về giá trị NULL và cách xử lý với họ. Nếu bạn chưa quen với SQL, hướng dẫn này sẽ cung cấp cho bạn thông tin chi tiết về một chủ đề có thể gây nhầm lẫn cho người mới bắt đầu.

NULL là gì?

NULL được sử dụng trong SQL để chỉ ra rằng một giá trị không tồn tại trong cơ sở dữ liệu.
Nó không bị nhầm lẫn với một chuỗi trống hoặc giá trị bằng không.

Trong khi NULL chỉ ra sự vắng mặt của một giá trị, thì chuỗi rỗng và không đều đại diện cho các giá trị thực tế. Để sử dụng phép loại suy, vì thiếu câu trả lời không nhất thiết có nghĩa là câu trả lời là “không”, thiếu giá trị không có nghĩa là nó bằng không.

Hiểu sai cách NULL hoạt động trong SQL có thể dẫn đến lỗi không mong muốn và đầu ra, do đó, không cần thêm quảng cáo, hãy xem chúng ta có thể giải quyết nó như thế nào.

Đối với bài tập này, chúng ta sẽ làm việc với bảng tblSouthPark .

< pre class = "kv kw kx ky gt nh bt ni">

CHỌN *
FROM tblSouthPark

 
  CHỌN 
  ID, 
 Sinh viên, 
 Email1, 
 Email2 
  FROM  tblSouthPark 
  WHERE  Email1 < strong class = "ng jg"> LÀ KHÔNG ĐỦ  VÀ   Email 2  KHÔNG ĐỦ 
 ĐƠN HÀNG THEO  ID 
 

Truy vấn trên tạo ra tất cả các bản ghi trong đó cả cột Email1 và Email2 đều là KHÔNG.

• KHÔNG ĐỦ: Như tên cho thấy, nó là nghịch đảo của IS NULL.

Cú pháp: biểu thức KHÔNG ĐẦY ĐỦ

 
  CHỌN 
  ID, 
 Sinh viên, 
 Email1, 
 Email2 
  FROM  tblSouthPark  
 TRONG ĐÓ  Email1  KHÔNG ĐỦ  VÀ   Email2  KHÔNG ĐẦY ĐỦ 
 ĐẶT HÀNG THEO  ID 
 

Truy vấn trên tạo ra tất cả các bản ghi trong đó cả cột Email1 và Email2 KHÔNG ĐẦY ĐỦ.

2. Hàm ISNULL ()

Hàm ISNULL trả về giá trị được chỉ định nếu biểu thức đã cho là NULL. Ngược lại, nếu biểu thức không phải là NULL, nó sẽ trả về chính biểu thức đó.

Cú pháp: ISNULL (biểu thức, giá trị)

Hãy hiểu điều này bằng cách chạy một truy vấn đơn giản trên SouthPark bảng.

 
  SELECT 
  ID, 
 Student, 
  ISNULL  (Father, 'Missing')  AS  Father 
  FROM  tblSouthPark 
 < strong class = "ng jg"> ĐẶT HÀNG THEO  ID 
 

Truy vấn này cho kết quả sau:

Vì biểu thức trong trường hợp Eric Cartman được đánh giá là NULL nên Hàm ISNULL trả về giá trị, Thiếu . Đối với các sinh viên khác, biểu thức không phải là NULL nên nó trả về chính biểu thức đó.

3. Hàm COALESCE ()

Hàm COALESCE trả về giá trị không phải NULL đầu tiên trong một danh sách nhất định. Không giống như hàm ISNULL , hàm này có thể chấp nhận nhiều biểu thức.

Cú pháp: COALESCE (biểu thức [1… ..n])

Ví dụ: SELECT COALESCE (NULL, NULL, 'red', 'blue', NULL) trả về red vì đó là giá trị không phải NULL đầu tiên. Nếu tất cả các giá trị đều là NULL, hàm COALESCE sẽ trả về NULL.

Hãy để chúng tôi sử dụng COALESCE Hàm trên bảng SouthPark của chúng tôi:

 
  CHỌN 
  ID, 
 Sinh viên, 
 < strong class = "ng jg"> COALESCE  (Email1, Email2, 'N / A')  AS  Primary_Email 
  FROM  tblSouthPark 
  ĐẶT HÀNG THEO  ID 
 

Truy vấn trên cho kết quả sau:

Như mong đợi, vì cả Email1 và Email2 đều là rỗng trong trường hợp của Kenny, nên hàm COALESCE trả về N / A as the Primary_Email . Đối với Stan, Email2 được trả về dưới dạng Primary_Email vì nó là giá trị không phải NULL đầu tiên trong Hàm COALESCE . Đối với những người khác, Email1 được trả về dưới dạng Primary_Email .

4. CASE Expression

Chúng tôi cũng có thể sử dụng biểu thức CASE cũ tốt để thay thế các giá trị NULL.

Đây là cú pháp chung cho CASE biểu thức:

 
  TRƯỜNG HỢP  
  KHI NÀO  biểu thức_1  THÌ  result_1 
  WHEN  expression_2  THEN  result_2 
. 
. 
 . 
  WHEN  expression_n  THEN  result_n 
  ELSE < / strong> else_expression 
  HẾT  
 

Chúng ta có thể tạo ra cùng một kết quả mà chúng ta đã nhận bằng cách sử dụng COALESCE ở trên bởi using CASE .

Truy vấn sẽ như sau:

 
  CHỌN 
  ID, 
 Sinh viên,  
 TRƯỜNG HỢP  
  KHI  Email1 KHÔNG ĐỦ  THÌ  Email1 
  KHI  Email2 KHÔNG ĐẦY ĐỦ  THÌ  Email2 
  ELSE  'Không / A' 
  KẾT THÚC NHƯ  Primary_Email 
  FROM  tblSouthPark 
  ĐẶT HÀNG BẰNG  ID 
 

Truy vấn này cho cùng một kết quả:

Lưu ý: Biểu thức CASE là về mặt cú pháp tương tự như hàm COALESCE . Trên thực tế, COALESCE giống như cách viết tắt của CASE . Câu trước ngắn gọn và đơn giản nhưng phần sau rõ ràng và dễ hiểu hơn.

5. Hàm NULLIF ()

Hàm NULLIF nhận hai biểu thức và trả về NULL nếu các biểu thức bằng nhau hoặc biểu thức đầu tiên khác.

Cú pháp: NULLIF (biểu thức 1, biểu thức 2)

 
  NULLIF  ('Red', 'Orange') - Trả về màu đỏ 
  NULLIF  (0, NULL) - Trả về 0 
  NULLIF  (0,0) - Trả về NULL 
 

Trong đó NULLIF rất hữu ích trong trường hợp dữ liệu chứa hỗn hợp các chuỗi rỗng và rỗng trong một cột. Hãy hiểu điều này bằng một ví dụ.

Chúng tôi thấy rằng cột Điện thoại trong bảng của chúng tôi chứa cả chuỗi NULL và rỗng.

Chúng ta có thể chuẩn hóa điều này bằng cách thay đổi khoảng trống chuỗi thành NULL bằng cách sử dụng NULLIF :

 
  SELECT 
  ID, 
 Student, 
  NULLIF  (Điện thoại, '')  AS  Điện thoại 
  FROM  tblSouthPark < br />  ĐẶT HÀNG THEO  ID 
 

Truy vấn trên cho kết quả:

Một trường hợp sử dụng tốt khác cho NULLIF là để ngăn lỗi "chia cho không":

 
 var1 = 1 
 var2 = 0 
 
 var1 / var2 - Điều này sẽ tạo ra lỗi "chia cho không" 
 
 var1 /  NULLIF  (var2,0) - Điều này không gây ra lỗi" chia cho không "
 

Trong trường hợp thứ hai, chúng tôi không nhận được lỗi" chia cho không "vì NULL được trả về ở mẫu số thay cho 0.

Mẹo thưởng

< ul class = "">

COALESCE là ANSI tiêu chuẩn, trong khi ISNULL và NULLIF không. Điều này làm cho COALESCE trở nên tổng quát và di động hơn (trên các phiên bản SQL và RDBMS khác nhau) so với ISNULL và NULLIF .

NULL là giá trị nhỏ nhất trong thứ tự sắp xếp. Nếu chúng ta sắp xếp theo một cột có chứa giá trị NULL, thì các hàng có giá trị NULL sẽ sắp xếp ở trên cùng theo mặc định. Sử dụng thứ tự sắp xếp DESC để sắp xếp ngược lại.

 
  Mẹo:  Để sắp xếp theo thứ tự bảng chữ cái (ASC) với giá trị NULL tại cuối cùng, bạn có thể sử dụng biểu thức CASE trong mệnh đề ORDER BY: 
 
  ĐẶT HÀNG THEO 
 TRƯỜNG HỢP   KHI  column_name  LÀ KHÔNG ĐỦ   THÌ  1  ELSE  0  HẾT, 
  column_name;  

• Các hàm tổng hợp trong SQL ( SUM , COUNT , AVG , MAX , MIN ) không xử lý các giá trị NULL và loại bỏ chúng trước khi thực hiện bất kỳ phép tính nào. Ngoại lệ duy nhất cho điều này là hàm COUNT (*) - nó trả về tổng số của tất cả các hàng, bao gồm cả những hàng mà tất cả các trường đều là NULL.
• Trong trường hợp Mệnh đề GROUP BY , nếu một cột chứa các hàng có giá trị NULL thì các hàng đó sẽ được nhóm lại thành một nhóm.
• Bạn có thể xâu chuỗi COALESCE và NULLIF hàm trong trường hợp cột chứa cả chuỗi NULL và chuỗi rỗng và bạn cần tạo một cột tổng hợp. Ví dụ:

 BẢNG ĐẦU VÀO: Mẫu 
 + ========================== =========== + 
 | Làm việc | Ô | 
 + ============== + ============== + 
 | | 717-735-6382 | 
 + -------------- + -------------- + 
 | 546-373-9363 | 493-353-3638 | 
 + -------------- + -------------- + 
 | NULL | 657-428-3639 | 
 + -------------- + -------------- + 
 
 QUERY 
  CHỌN   COALESCE  ( NULLIF  (Work, ''), Cell)  AS  Primary  FROM  Mẫu 
 
 OUTPUT 
 + ==== ========== + 
 | Chính | 
 + ============== + 
 | 717-735-6382 | 
 + -------------- + 
 | 546-373-9363 | 
 + -------------- + 
 | 657-428-3639 | 
 + -------------- + 
 
 Chúng ta cũng có thể sử dụng biểu thức  CASE  nhanh và bẩn để làm tương tự: 
 
  TRƯỜNG HỢP   KHI NÀO  Làm việc & lt; & gt; ''  THÌ  Làm việc  ELSE  Ô  HẾT  
 

Kết thúc

Bây giờ bạn sẽ có tất cả những gì bạn cần để xử lý thành công các giá trị NULL trong SQL. Tất nhiên, chúng tôi không đề cập đến từng trường hợp cạnh ở đây, nhưng đây phải là một điểm khởi đầu tốt cho người mới bắt đầu. Nếu bạn gặp khó khăn, Stack Overflow là bạn của bạn. Như mọi khi, luyện tập là chìa khóa!