Chọn những dòng không tồn tại ở bảng nào đó

Đây là yêu cầu khá phổ biến, nhất là trong lãnh vực Data Warehouse.

Giả thử bạn có 2 bảng A và B, liên hệ bởi trường Id. Bây giờ bạn muốn lấy tất cả các dòng ở bảng A với điều kiện trường Id của nó không tồn tại ở bảng B.

 

declare @A table(id varchar(10), c int)
declare @B table(id varchar(10), c int)
insert into @A values(1,2),(3,3),(7,1),(5,6)
insert into @B values(1,1),(7,2),(4,1),(8,9)

select * from @A
select * from @B

Bang A
id         c
---------- -----------
1          2
3          3
7          1
5          6

Bang B
id         c
---------- -----------
1          1
7          2
4          1
8          9


Truy xuat
id         c
---------- -----------
3          3
5          6

 

Có 4 cách thực hiện yêu cầu này.
 

 

1 -
select ba.* from @A ba where ba.Id not in(select bb.Id from @B bb)

 

2 -
select ba.* from @A ba left join @B bb on ba.Id=bb.Id where bb.Id is null

 

3 -
select ba.* from @A ba where not exists(select * from @B bb where ba.Id=bb.Id)

 

4 -

;with cte as
(select a.*, BID = (select ',' + id as 'text()' from @B b for xml path('')) from @A a)
select id as AID, c, BID from cte where charindex(id, BID) = 0

 

Cách 1 ngắn gọn dễ hiểu nhất, nhưng tiềm ẩn đầy rủi ro. Nếu Id chứa null, kết quả sẽ không bảo đảm chính xác (đọc thêm về phần null). Theo tôi bạn không nên sử dụng cách này, cho dù Id của 2 bảng A và B đều có ràng buộc NOT NULL. Tại sao ? Yêu cầu luôn thay đổi và dẫn đến cấu trúc dữ liệu cũng có thể thay đổi theo, với tư cách 1 lập trình viên, bạn phải sẵn sàng cho điều này. Nếu DBA thay đổi cấu trúc khoá Id của bảng A hoặc B, hoặc cả 2, bạn có thể phải thay đổi toàn bộ câu lệnh loại này. Lúc ấy có thể bạn chẳng nhớ nổi có bao nhiêu câu lệnh loại này, ở đâu, liên quan đến bảng nào v.v... 

 

Cách 2 và 3 thì vẫn còn nhiều bất đồng, tuỳ tư duy mỗi người. Có người thấy cách 2 dễ hiểu hơn, có người thấy cách 3 dễ hiểu hơn. Ví dụ trên là đơn giản nhất, trong thực tế mối liên hệ các bảng thường phức tạp hơn nhiều, nên viết câu lệnh cho dễ hiểu cũng là điều quan trọng. Riêng tôi thấy cách 3 dễ hiểu hơn. Về phần tối ưu, cách 2 và 3, cách nào nhanh hơn ? Tuỳ vào từng trường hợp, cả cấu trúc lẫn dữ liệu của 2 bảng. Bạn cần phân tích và chạy thử cho từng trường hợp để khẳng định điều này. Riêng tôi thấy 70% trường hợp cách 3 chạy nhanh hơn.

Cách 4 rườm rà nhất, nhưng nó có thể được sử dụng cho các báo cáo nhanh, khi bạn cần so sánh trường id ở bảng A với toàn bộ trường id ở bảng B để chứng tỏ sự không tồn tại của nó. Cách này không thích hợp khi bảng B rất lớn, nhất là trong lãnh vực Data Warehouse.

AID     c             BID
-------- --------- ----------
3          3           ,1,7,4,8
5          6           ,1,7,4,8