EXISTS và truy vấn con tương quan

Dữ liệu dùng trong bài viết này — employee / sales / department

EXISTS là một truy vấn con chỉ kiểm tra liệu có ít nhất một hàng khớp tồn tại hay không, và bạn dùng nó khá giống IN.

Trong bài viết này bạn sẽ đi qua những điều cơ bản của EXISTS / NOT EXISTS, cách né an toàn cạm bẫy NOT IN + NULL bằng cách viết nó với EXISTS, và cách chọn giữa EXISTS và IN dựa trên khả năng đọc.

Dữ liệu là ba bảng dữ liệu nhân viên.

Dùng employee (30 nhân viên), sales (50 dòng doanh số), và department (6 phòng ban), bạn sẽ lấy ra "nhân viên có / không có doanh số" và "phòng ban không có nhân viên".

Trước khi lao vào các bài tập, hãy xem định nghĩa cột và dữ liệu mẫu của ba bảng mà bài viết này dùng — employee / sales / department.

① Chạy PRAGMA table_info(...) để kiểm tra định nghĩa cột của ba bảng.

② Chạy SELECT * FROM table_name LIMIT 5; để xem trước 5 hàng đầu tiên của mỗi bảng. Việc employee.dept_id có thể là NULL cũng xuất hiện trong một bài tập sau.

EXISTS / NOT EXISTS — chỉ kiểm tra liệu một hàng có tồn tại hay không

WHERE EXISTS (SELECT ... ) là true nếu truy vấn con trả về dù chỉ một hàng, và false nếu nó không trả về hàng nào cả.

Truy vấn con select gì không ảnh hưởng đến kết quả, nên theo quy ước bạn viết SELECT 1 (giá trị không được dùng — bạn chỉ quan tâm liệu một hàng có tồn tại hay không).

Dạng cơ bản là một truy vấn con tương quan tham chiếu một cột từ truy vấn chính bên trong EXISTS.

"Nhân viên có doanh số" là WHERE EXISTS (SELECT 1 FROM sales sale WHERE sale.emp_id = emp.emp_id), và "nhân viên không có doanh số" là phủ định của nó, NOT EXISTS.

EXISTS ngừng đánh giá ngay khi nó tìm thấy một hàng, điều này khiến nó phù hợp cho việc kiểm tra tồn tại.

-- EXISTS: nhân viên có doanh số
SELECT emp.emp_id, emp.name
FROM employee emp
WHERE EXISTS (
  SELECT 1 FROM sales sale WHERE sale.emp_id = emp.emp_id
)
ORDER BY emp.emp_id;

Hãy hình dung yêu cầu "tôi muốn danh sách những nhân viên không xuất hiện trong các dòng doanh số dù chỉ một lần (nhân viên chưa ghi nhận doanh số nào)." (Chạy đúng thì phần giải thích sẽ hiện ra.)

① Đặt cho bảng employee bí danh e và lấy ra emp_id và name.

② Dùng NOT EXISTS để thu hẹp về các hàng mà nhân viên đó không có hàng nào cả trong sales. Tương quan truy vấn con với truy vấn chính theo dạng SELECT 1 FROM sales sale WHERE sale.emp_id = emp.emp_id.

③ Sắp xếp theo emp_id tăng dần.

Cạm bẫy NOT IN + NULL — EXISTS thì an toàn

Nếu bạn viết "lọc phủ định" bằng NOT IN, kết quả có thể trả về 0 hàng khi một NULL bị trộn vào phía truy vấn con.

x NOT IN (1, 2, NULL) được đánh giá là "x không phải 1, không phải 2, và không phải NULL," nhưng x <> NULL không bao giờ thành true và luôn là NULL (không xác định), nên toàn bộ NOT IN không bao giờ thành true.

Lý lẽ đầy đủ được giải thích trong Truy vấn con ① (cạm bẫy NOT IN + NULL).

Vì employee.dept_id trộn NULL, viết "phòng ban không có nhân viên" thành WHERE dept_id NOT IN (SELECT dept_id FROM employee) khiến Legal (một phòng ban hoàn toàn không có nhân viên), lẽ ra phải có, biến mất và kết quả trả về 0 hàng.

NOT EXISTS chỉ nhìn vào liệu một hàng có tồn tại hay không và không bị NULL ảnh hưởng, nên trong tình huống này NOT EXISTS trả về kết quả đúng.

-- Cạm bẫy: một truy vấn con chứa NULL + NOT IN trả về 0 hàng
SELECT dept_id, dept_name
FROM department
WHERE dept_id NOT IN (SELECT dept_id FROM employee);
-- → 0 hàng vì employee.dept_id chứa NULL

-- An toàn: NOT EXISTS không bị NULL ảnh hưởng
SELECT d.dept_id, d.dept_name
FROM department d
WHERE NOT EXISTS (
  SELECT 1 FROM employee emp WHERE emp.dept_id = d.dept_id
);

Hãy hình dung yêu cầu "tôi muốn liệt kê những phòng ban hoàn toàn không có nhân viên nào được phân vào." Vì employee.dept_id trộn NULL, hãy viết nó an toàn với NOT EXISTS.

① Đặt cho bảng department bí danh d và lấy ra dept_id và dept_name.

② Dùng NOT EXISTS để thu hẹp về các hàng mà phòng ban đó không có hàng nhân viên nào cả trong employee. Tương quan truy vấn con theo dạng SELECT 1 FROM employee emp WHERE emp.dept_id = d.dept_id.

③ Sắp xếp theo dept_id tăng dần.

Kiểm tra điều gì xảy ra khi bạn viết bài tập trước với NOT IN. Bài tập này nhằm cho bạn trải nghiệm sai lầm trực tiếp, nên hãy chạy nó dưới dạng NOT IN và quan sát rằng kết quả trả về 0 hàng.

① Lấy ra dept_id và dept_name từ bảng department.

② Thử liệt kê "các phòng ban không có nhân viên" dùng WHERE dept_id NOT IN (SELECT dept_id FROM employee).

③ Chạy nó và xác nhận kết quả là 0 hàng (phòng ban Legal không ra). Hãy nghĩ tại sao kết quả khác với phiên bản NOT EXISTS ở Thực hành 2.

Chọn EXISTS theo khả năng đọc

EXISTS chỉ nhìn vào liệu một hàng có tồn tại hay không, nên nó an toàn ngay cả khi có NULL, nhưng viết một truy vấn con tương quan có thể khiến nó trông dài hơn IN.

Với một phép kiểm tra tồn tại đơn giản có những tình huống mà IN đọc tốt hơn, và trong công việc thực tế bạn chọn dựa trên ngữ cảnh.

Mặt khác, với một phủ định như NOT IN nơi NULL phá vỡ kết quả, hoặc khi bạn muốn phán định một cách chắc chắn chỉ liệu một hàng có tồn tại hay không, EXISTS / NOT EXISTS là lựa chọn an toàn.