Bài 32: UNION, INTERSECT và EXCEPT

Cô Mai gửi thư mời sinh hoạt cho các bạn đã đăng ký Python hoặc Database. Một bạn có thể đăng ký cả hai lớp nhưng chỉ nên nhận một thư. Lần khác, cô lại cần biết ai học cả Python lẫn Database. Những yêu cầu này cần ghép hoặc so sánh các danh sách, thay vì ghép thêm cột thông tin cho từng dòng.

Ghép hai danh sách và giữ mỗi người một lần

Phép hợp (union) lấy những dòng có trong ít nhất một danh sách. Trong SQL, UNION ghép các dòng từ hai kết quả truy vấn rồi loại dòng trùng. Nó không ghép thêm cột như JOIN.

Trước hết, đọc mã lớp và đăng ký để biết mỗi danh sách lấy từ đâu. Trong bài này, đăng ký tạm dừng vẫn được tính là đã đăng ký:

Lấy mã người đăng ký Python ở nhánh đầu, mã người đăng ký Database ở nhánh sau. Mỗi nhánh là một câu chọn dữ liệu, còn UNION quyết định cách ghép hai kết quả:

SELECT student_id
FROM enrollments
WHERE class_id = 1
UNION
SELECT student_id
FROM enrollments
WHERE class_id = 3
ORDER BY student_id;

Kết quả có bốn mã: 1, 2, 3, 5. Python có các mã 1, 2, 5, còn Database có 1, 3. An có mã 1 ở cả hai nhánh nhưng chỉ xuất hiện một lần. Vì vậy, danh sách này giúp cô gửi mỗi người đúng một thư.

Nếu chỉ cần người đang học, cả hai nhánh phải có điều kiện trạng thái tương ứng. Dung có đăng ký 'paused' ở Scratch nên không thuộc cặp lớp đang xét. Phép hợp không tự biết chính sách nào về trạng thái cần được áp dụng.

Giữ số lần xuất hiện khi mỗi dòng là một việc riêng

UNION ALL ghép hai kết quả và giữ cả dòng trùng. Đây là lựa chọn phù hợp khi mỗi dòng đại diện cho một lần xuất hiện cần đếm. Nó không cộng các giá trị thành một số mới.

Giữ nguyên cặp Python và Database, hãy đổi sang UNION ALL để so hai kết quả:

SELECT student_id
FROM enrollments
WHERE class_id = 1
UNION ALL
SELECT student_id
FROM enrollments
WHERE class_id = 3
ORDER BY student_id;

Kết quả có năm dòng, trong đó mã 1 xuất hiện hai lần. Nếu mục tiêu là mỗi người nhận một thư, đây là danh sách chưa đúng yêu cầu. Nếu mục tiêu là đếm số đăng ký trong hai lớp, việc giữ hai lần của An lại có ý nghĩa.

Để xem trực tiếp cơ chế loại trùng, ta đặt hai dòng có cùng giá trị vào hai nhánh tối giản:

SELECT 20 AS amount
UNION
SELECT 20 AS amount;

Kết quả chỉ còn một dòng có amount = 20, không phải một dòng có 40. Nếu đây là hai khoản thu riêng, loại trùng theo số tiền sẽ làm mất một khoản. Danh tính của sự kiện không thể được xác định chỉ bằng lượng tiền bằng nhau.

Bạn nên xác định một dòng có ý nghĩa gì trước khi chọn phép ghép. Loại trùng không phải bước sửa lỗi mặc định cho mọi báo cáo. Một dòng bị mất sau loại trùng có thể là dữ liệu thật cần được giữ.

Lấy phần chung của hai danh sách

Phép giao (intersection) lấy những dòng có trong cả hai kết quả. Trong SQL, INTERSECT trả phần chung và loại các dòng trùng của kết quả. Muốn tìm người đăng ký cả Python lẫn Database, ta so sánh cùng cột mã người:

SELECT student_id
FROM enrollments
WHERE class_id = 1
INTERSECT
SELECT student_id
FROM enrollments
WHERE class_id = 3
ORDER BY student_id;

Kết quả chỉ có mã 1. Mã 3 có trong Database nhưng không ở Python, nên bị loại. So với phép hợp, câu hỏi đã đổi từ có mặt ở ít nhất một danh sách sang có mặt ở cả hai.

Ta có thể đặt tên cho phần giao rồi ghép tên học viên để đọc dễ hơn. Đây là chỗ CTE ở bài 31 kết hợp với phép tập hợp:

WITH both_classes AS (
  SELECT student_id
  FROM enrollments
  WHERE class_id = 1
  INTERSECT
  SELECT student_id
  FROM enrollments
  WHERE class_id = 3
)
SELECT s.id, s.name
FROM students AS s
JOIN both_classes AS b ON b.student_id = s.id
ORDER BY s.id;

An xuất hiện một dòng vì phần giao chỉ còn một mã. Phép tập hợp so sánh các dòng giữa hai danh sách. Phép nối sau đó bổ sung tên dựa trên quan hệ mã, nên hai công cụ có vai trò khác nhau.

Lấy người thuộc danh sách này nhưng không thuộc danh sách kia

Phép hiệu (difference) giữ dòng có ở kết quả bên trái nhưng không có ở bên phải. SQL dùng EXCEPT cho yêu cầu này. Thứ tự hai nhánh quan trọng vì đổi chiều sẽ đổi câu hỏi.

Ví dụ sau tìm người đăng ký Database nhưng không đăng ký Python:

SELECT student_id
FROM enrollments
WHERE class_id = 3
EXCEPT
SELECT student_id
FROM enrollments
WHERE class_id = 1
ORDER BY student_id;

Chỉ mã 3 được giữ. Mã 1 bị loại vì có mặt cả ở Python. Các mã chỉ ở Python không được thêm vào kết quả, bởi nhánh trái đã xác định danh sách ban đầu cần xét.

Điều này khác tìm tất cả người chỉ học một trong hai lớp. Yêu cầu đó còn phải tính chiều ngược lại rồi ghép hai phần thích hợp. Nếu chỉ đổi EXCEPT thành INTERSECT, bạn sẽ lấy người học cả hai thay vì người học riêng một lớp.

Một người không có đăng ký nào, như Lan trong dữ liệu mẫu, không có ở nhánh nào. Cả hợp, giao và hiệu của các danh sách đăng ký đều không tự thêm người ấy. Muốn tìm người chưa đăng ký, hãy bắt đầu từ toàn bộ học viên rồi trừ danh sách có đăng ký:

SELECT id
FROM students
EXCEPT
SELECT student_id
FROM enrollments
ORDER BY id;

Kết quả chỉ có mã 6 của Lan. Hai nhánh so sánh theo vị trí dù cột nguồn mang tên khác nhau. Cột kết quả lấy tên id từ nhánh đầu, nên quy tắc ghép cột cũng cần được kiểm tra.

So sánh toàn dòng và ghép cột đúng vị trí

Hai nhánh phải trả cùng số cột. Các cột được ghép theo vị trí, vì vậy cùng số cột vẫn chưa bảo đảm báo cáo có nghĩa. SQLite chấp nhận ghép cột name với coins, dù tên và số xu mang nghĩa khác nhau. PostgreSQL báo lỗi vì hai cột thuộc hai nhóm kiểu dữ liệu khác nhau.

Hãy xem SQLite ghép tên và số xu của An vào cùng một cột:

SELECT name AS value
FROM students
WHERE id = 1
UNION ALL
SELECT coins
FROM students
WHERE id = 1;

Cột value chứa một tên và một số. Câu lệnh chạy được trên SQLite nhưng không tạo một cột có ý nghĩa thống nhất. Bạn cần ghép những cột biểu diễn cùng loại thông tin.

Nếu một nhánh có thêm tên mà nhánh kia chỉ có mã, SQLite báo lỗi ngay:

SELECT id, name
FROM students
UNION
SELECT id
FROM students;

Thông báo lỗi giúp nhận ra hai cấu trúc khác nhau. Bạn cần chọn cùng số cột với cùng ý nghĩa tương ứng. Tên cột kết quả lấy từ nhánh đầu, vì vậy hãy đặt bí danh rõ ở nhánh ấy.

Phép loại trùng xét toàn dòng được chọn, không đoán mã bị bỏ khỏi kết quả. Hai học viên trùng tên có thể bị gộp nếu bạn chỉ chọn tên. Ngược lại, cùng một mã kèm hai nhãn lớp khác nhau tạo hai dòng khác nhau và được giữ.

Thử ghép hai dòng cùng chứa NULL để quan sát quy tắc loại trùng:

SELECT NULL AS mentor
UNION
SELECT NULL;

Kết quả chỉ có một dòng NULL. Phép hợp xem hai dòng này là trùng nhau. Điều đó không khiến điều kiện NULL = NULL trở thành đúng.

Cuối cùng, ORDER BY chỉ được đặt sau nhánh cuối để sắp xếp toàn bộ kết quả ghép. Muốn sắp riêng từng nhánh, hãy bọc nhánh trong truy vấn con. Phép tập hợp không hứa giữ thứ tự từng danh sách nguồn. Khi báo cáo cần mã tăng dần, hãy viết điều đó rõ thay vì dựa vào thứ tự tình cờ quan sát.

Lỗi thường gặp

Tóm tắt

• UNION ghép dòng và loại trùng, còn UNION ALL giữ số lần xuất hiện.
• INTERSECT tìm dòng có trong cả hai kết quả.
• EXCEPT giữ dòng chỉ có ở nhánh trái, nên cần kiểm chiều.
• Hai nhánh ghép theo vị trí cột và so sánh toàn dòng đã chọn.
• Chỉ thứ tự được khai báo ở kết quả cuối mới là thứ tự cần dựa vào.

Kiểm tra nhanh

Đọc thêm

• SQLite: các truy vấn ghép và quy tắc loại trùng.
• PostgreSQL: hợp, giao và hiệu của kết quả.
• PostgreSQL: quy tắc ghép kiểu dữ liệu của UNION.