Bài 17: Truy vấn con và EXISTS

Cô Mai cần mời những học viên đã đăng ký ít nhất một lớp. Cô chỉ cần danh sách người, không cần lặp tên theo từng lớp. Thầy Nam lại muốn tìm người có số xu cao hơn mức trung bình hiện tại. Để lọc đúng người, ta cần kiểm tra đăng ký của từng học viên hoặc tính mức xu trung bình trước.

Truy vấn con cho phép đặt bước kiểm tra hay tính toán ấy ngay trong câu truy vấn chính.

Một truy vấn con có thể cung cấp giá trị so sánh

Truy vấn con (subquery) là câu SELECT nằm bên trong một câu lệnh khác. Bài 16 dùng nó như một bảng nguồn. Khi cần một giá trị, ta dùng truy vấn con chỉ trả một cột và một dòng. Cách dùng này gọi là truy vấn con vô hướng (scalar subquery).

Trước khi lọc người, hãy chạy riêng câu tính trung bình:

SELECT AVG(coins) AS average_coins
FROM students;

Kết quả là 130 xu. Hàm AVG gộp số dư của toàn bộ sáu học viên thành một giá trị. Vì không có GROUP BY, nó không tạo một mức trung bình riêng cho từng khối. Ta có thể dùng mức trung bình này làm ngưỡng để so với số xu của từng học viên.

Ta đặt câu vừa kiểm vào ngoặc sau dấu >:

SELECT id, name, coins
FROM students
WHERE coins > (
  SELECT AVG(coins)
  FROM students
)
ORDER BY id;

Kết quả giữ Chi, Huy và Lan với số xu 180, 180, 150. An có 120 nên không đạt điều kiện. Truy vấn ngoài đọc từng học viên rồi so số xu của người đó với cùng một mức trung bình. Bạn không phải gõ cố định số 130 vào điều kiện.

Khi dữ liệu thay đổi, mức trung bình cũng thay đổi theo. Viết WHERE coins > 130 chỉ đúng với bộ mẫu hiện tại, chưa diễn tả đầy đủ yêu cầu. Đặt câu tính trung bình trong truy vấn con giúp báo cáo tự tính lại ngưỡng từ dữ liệu hiện có. Đây là khác biệt giữa một ngưỡng cố định và một ngưỡng được tính.

EXISTS hỏi có ít nhất một dòng liên quan hay không

EXISTS là phép kiểm tồn tại: nó cho kết quả đúng khi truy vấn con trả ít nhất một dòng. Phép kiểm chỉ quan tâm có dòng hay không, không quan tâm giá trị của cột được chọn. Vì vậy, cách viết quen thuộc là SELECT 1 trong truy vấn con. Số 1 không phải mã lớp cần tìm.

Ta dùng EXISTS để lấy những người có đăng ký:

SELECT s.id, s.name
FROM students AS s
WHERE EXISTS (
  SELECT 1
  FROM enrollments AS e
  WHERE e.student_id = s.id
)
ORDER BY s.id;

Kết quả có An, Bình, Chi, Dung và Huy. An có hai đăng ký nhưng chỉ có một dòng trong danh sách. Truy vấn ngoài vẫn đọc một dòng cho mỗi học viên.

EXISTS chỉ quyết định giữ hay loại dòng học viên. Nó không ghép thêm các đăng ký vào kết quả như phép nối.

Với câu hỏi “ai có đăng ký?”, bạn có thể đối chiếu ba cách viết sau với hiện tượng nhân dòng ở bài 16:

DISTINCT ở đây giữ cả mã và tên để hai người trùng tên vẫn là hai người khác nhau. Với EXISTS, số đăng ký không làm lặp dòng học viên.

Truy vấn con tương quan (correlated subquery) dùng giá trị từ truy vấn ngoài. Điều kiện e.student_id = s.id hỏi về đăng ký của đúng người đang xét. Với An, nó kiểm mã 1; với Lan, nó kiểm mã 6. Lan không có dòng phù hợp nên bị loại.

Bạn có thể thêm điều kiện e.status = 'active' vào truy vấn con nếu chỉ cần người đang học. Khi đó Dung không được giữ vì đăng ký của Dung là paused. Bạn cần kiểm tra đúng loại đăng ký mà đề yêu cầu. Có đăng ký bất kỳ khác với có đăng ký đang học, tức trạng thái active.

Hãy quan sát danh sách sau khi bỏ điều kiện tương quan:

SELECT s.id, s.name
FROM students AS s
WHERE EXISTS (
  SELECT 1
  FROM enrollments AS e
)
ORDER BY s.id;

Kết quả có cả sáu người, gồm Lan chưa đăng ký. Chỉ cần bảng có một đăng ký của bất kỳ ai, truy vấn con đã trả về dòng. Điều kiện khi đó đúng với mọi học viên, kể cả người chưa đăng ký.

SQL không tự thêm liên kết dù các bảng đều có mã học viên. Hãy kiểm tra xem truy vấn con đã dùng mã học viên từ truy vấn ngoài để tìm đúng đăng ký hay chưa.

NOT EXISTS phân biệt không có điểm với không có điểm cao

NOT EXISTS đảo phép kiểm tồn tại: nó đúng khi truy vấn con không trả dòng nào. Muốn tìm học viên chưa làm bài, ta kiểm không có bất kỳ dòng điểm nào của người ấy. Điều kiện liên kết bằng mã vẫn cần giữ nguyên.

Câu dưới tìm người hoàn toàn chưa có lần chấm:

SELECT s.id, s.name
FROM students AS s
WHERE NOT EXISTS (
  SELECT 1
  FROM scores AS t
  WHERE t.student_id = s.id
)
ORDER BY s.id;

Kết quả chỉ có Lan. Dung có điểm 50 nên không được chọn, dù điểm ấy thấp. Câu hỏi đang nói chưa làm bài chứ không nói chưa đạt điểm cao. Nếu coi điểm thấp như chưa có điểm, bạn sẽ chọn cả Dung dù bạn ấy đã làm bài.

Bây giờ cô Mai cần tìm những người chưa từng đạt từ 90 điểm trở lên. Điều kiện điểm phải nằm cùng điều kiện người trong truy vấn con:

SELECT s.id, s.name
FROM students AS s
WHERE NOT EXISTS (
  SELECT 1
  FROM scores AS t
  WHERE t.student_id = s.id AND t.score >= 90
)
ORDER BY s.id;

Lần này Bình, Dung và Lan đều xuất hiện. Bình và Dung có điểm nhưng không có điểm đạt ngưỡng. Lan không có điểm nào nên cũng không có điểm đạt ngưỡng.

NOT EXISTS chỉ kiểm tra có lần chấm nào thỏa điều kiện bên trong hay không. Nó không yêu cầu học viên phải từng làm bài trước đó.

Khi viết điều kiện, bạn hãy thử đọc yêu cầu thành một câu đầy đủ. “Không có lần chấm của người này đạt từ 90” bao gồm cả người chưa được chấm. “Có điểm dưới 90” lại có thể chọn An dù An đã có một điểm 90. Vì vậy, tìm người không có điểm cao và tìm người có điểm thấp có thể cho hai danh sách khác nhau.

NOT IN cần xét cả giá trị NULL trong tập

IN kiểm một giá trị có thuộc một tập giá trị hay không. NOT IN kiểm điều ngược lại, nhưng NULL có thể khiến kết luận trở thành chưa biết. Muốn thấy điều đó, ta tạo danh sách loại trừ minh họa. Bảng riêng excluded chứa mã 1 và một mã chưa xác định.

Bảng excluded chỉ là dữ liệu riêng của ví dụ. Trong bảng điểm chuẩn scores, cột student_id bắt buộc có mã học viên, không chứa NULL. Dữ liệu riêng chỉ dùng để quan sát một tập có giá trị thiếu. Câu sau thử loại người có mã thuộc danh sách đó:

SELECT id, name
FROM students
WHERE id NOT IN (
  SELECT student_id
  FROM excluded
)
ORDER BY id;

Kết quả rỗng. Mã 1 bị loại vì đã có trong danh sách. Với các mã khác, phép so sánh với NULL cho kết quả chưa biết, nên điều kiện không thể là đúng.

WHERE chỉ giữ điều kiện đúng, nên những dòng ấy cũng không qua. Bạn không thể coi NULL như một mã chắc chắn khác mọi người.

Ta viết lại điều kiện: chỉ giữ học viên khi không có dòng nào trong danh sách loại trừ khớp mã của người đó:

SELECT s.id, s.name
FROM students AS s
WHERE NOT EXISTS (
  SELECT 1
  FROM excluded AS x
  WHERE x.student_id = s.id
)
ORDER BY s.id;

Kết quả giữ Bình, Chi, Dung, Huy và Lan. Dòng NULL trong excluded không khớp một mã người cụ thể. Trong cách viết này, bạn kiểm tra trực tiếp mã nào khớp với học viên đang xét. Nó giúp yêu cầu dễ đọc hơn, nhưng không có nghĩa lúc nào cũng chạy nhanh hơn cách viết khác.

Lỗi thường gặp

Tóm tắt

• Truy vấn con vô hướng cung cấp một giá trị cho phép so sánh.
• EXISTS giữ học viên khi có ít nhất một dòng phù hợp. Mỗi học viên vẫn chỉ xuất hiện một lần, dù có nhiều đăng ký.
• Điều kiện tương quan nối câu hỏi bên trong với dòng đang xét bên ngoài.
• NOT EXISTS kiểm không có dòng thỏa toàn bộ điều kiện bên trong.
• Nếu danh sách dùng cho NOT IN có NULL, điều kiện có thể trả về kết quả chưa biết và loại cả những người bạn muốn giữ.

Kiểm tra nhanh

Đọc thêm

• SQLite giải thích EXISTS và truy vấn con tương quan.
• SQLite đối chiếu IN và NOT IN khi có NULL.