Lưu ý khi viết câu lệnh dùng “NOT IN”
Được viết bởi
QuangIT
ngày 31/07/2012 lúc 02:27 PM
Khi bạn viết câu lệnh dùng “NOT IN”, bạn có thể sẽ gặp những tình huống không mong đợi, nếu dữ liệu có chứa giá trị NULL.
Lưu ý khi viết câu lệnh dùng “NOT IN”
Khi bạn viết câu lệnh dùng “NOT IN”, bạn có thể sẽ gặp những tình huống không mong đợi, nếu dữ liệu có chứa giá trị NULL.
Ví dụ:
CREATE TABLE dbo.DienThoai(ID INT PRIMARY KEY, NhaSX NVARCHAR(50), Model VARCHAR(50), TenCSH NVARCHAR(80) )
GO
INSERT INTO dbo.DienThoai
SELECT 1, 'Apple', 'Iphone 4', N'Hồ Ngọc Hà' UNION ALL
SELECT 2, 'Vertu', 'Signature', N'Quang Dũng' UNION ALL
SELECT 3, 'Samsung','S9402 Ego', N'Mỹ Tâm' UNION ALL
SELECT 4, 'HTC',NULL, N'Mỹ Tâm' -- không có model
Tình huống 1. Tìm ra các chủ nhân có model không phải là “Iphone 4″ và “Signature”, bạn viết câu lệnh thế này:
SELECT ID, TenCSH
FROM dbo.DienThoai
WHERE Model NOT IN ('Iphone 4','Signature')ID TenCSH
---------------
3 S9402 Ego
Bạn hy vọng câu lệnh trả về các bản ghi #3 và #4, nhưng thực tế nó chỉ trả về #3. Lý do là vì bản ghi #4 có Model = NULL, làm cho hệ thống không xác định được chân lý của biểu thức logic "Model NOT IN ('Iphone 4','Signature' )". Nói cách khác nó không đánh giá được biểu thức đúng hay sai; trong khi đó yêu cầu của câu lệnh là trả về bản ghi nếu biểu thức đúng.
Để khắc phục lỗi trên bạn có thể thêm "OR Model IS NULL" vào mệnh đề WHERE của câu lệnh, hoặc dùng hàm ISNULL với Model để gán cho nó một giá trị không NULL:
SELECT ID, TenCSH
FROM dbo.DienThoai
WHERE Model NOT IN ('Iphone 4','Signature') OR Model IS NULL
-- hoặc
SELECT ID, TenCSH
FROM dbo.DienThoai
WHERE ISNULL(Model,'-') NOT IN ('Iphone 4','Signature')
SELECT ID, TenCSH
FROM dbo.DienThoai
WHERE Model NOT IN (SELECT Model FROM dbo.DienThoai
WHERE TenCSH = N'Mỹ Tâm')ID TenCSH
------------------
(0 row(s) affected)
Theo suy luận thông thường, bạn mong đợi câu lệnh trả về các bản ghi #1 và #2. Tuy nhiên hoàn toàn ngược lại nó không trả về bản ghi nào. Vì sao vậy? Câu lệnh con ở trên trả về hai model là ”S9402 Ego” và NULL, do đó câu lệnh chính tương đương với:
SELECT ID, TenCSH
FROM dbo.DienThoai
WHERE Model NOT IN ('S9402 Ego',NULL)
Model NOT IN ('S9402 Ego',NULL)
tương đương với:
NOT [Model IN ('S9402 Ego',NULL)]
tương đương với:
NOT [(Model = 'S9402 Ego') OR (Model = NULL)]
tương đương với:
(Model != 'S9402 Ego') AND (Model != NULL)
Biểu thức cuối cùng ở trên muốn đúng đòi hỏi cả hai biểu thức con phải đúng, trong khi (Model != NULL) thì không kết luận được. Vì thế mà cả biểu thức trên luôn luôn không xác định được (và do đó không thể gọi là đúng).
Để khắc phục bạn cũng có thể làm theo hai cách, thêm điều kiện “AND Model IS NOT NULL” vào mệnh đề WHERE của câu lệnh con, hoặc viết lại cả câu lệnh dùng “NOT EXISTS”:
SELECT ID, TenCSH
FROM dbo.DienThoai
WHERE Model NOT IN (SELECT Model FROM dbo.DienThoai
WHERE TenCSH = N'Mỹ Tâm' AND Model IS NOT NULL)
--hoặc
SELECT ID, TenCSH
FROM dbo.DienThoai D1
WHERE NOT EXISTS(SELECT 1 FROM dbo.DienThoai D2
WHERE D2.TenCSH = N'Mỹ Tâm'
AND D2.Model = D1.Model)
Nguồn bài viết:
SqlViet