Phép toán tập hợp — UNION / INTERSECT / EXCEPT

Dữ liệu dùng trong bài viết này — employee và contractor

Phép toán tập hợp (các phép toán kết hợp nhiều kết quả SELECT thành một như hợp, giao, hoặc hiệu của các tập) là cú pháp để xếp chồng dọc các kết quả được lấy riêng rẽ, hoặc lấy ra các hàng chung của cả hai hoặc các hàng chỉ xuất hiện ở một phía.

Bạn sẽ làm việc với bốn cái: UNION (hợp), UNION ALL (hợp giữ trùng lặp), INTERSECT (giao), và EXCEPT (hiệu).

Dữ liệu là bảng nhân viên employee (30 hàng) và bảng nhà thầu contractor (6 hàng).

Bạn sẽ thử bốn phép toán tập hợp lần lượt từng cái — nối hai kết quả với các điều kiện khác nhau bên trong employee, hoặc kết hợp các kết quả lấy từ các bảng khác nhau như employee và contractor thành một.

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

① Chạy PRAGMA table_info(employee); và PRAGMA table_info(contractor); để kiểm tra định nghĩa cột của cả hai bảng.

② Chạy SELECT * FROM employee LIMIT 5; và SELECT * FROM contractor LIMIT 5; để xem trước 5 hàng dữ liệu đầu tiên.

Tiền đề của phép toán tập hợp — tính tương thích UNION

Cả bốn phép toán tập hợp nối hai hoặc nhiều kết quả SELECT theo dạng SELECT ... operator SELECT ....

Tiền đề để nối là SELECT trên và dưới phải tương thích UNION (số cột bằng nhau, và các kiểu cột tương ứng tương thích).

SELECT name FROM employee và SELECT name FROM contractor đều là một cột chuỗi đơn, nên chúng tương thích.

Mặt khác, SELECT name FROM employee và SELECT name, city FROM contractor có số cột khác nhau, nên bạn không thể nối chúng.

-- tương thích UNION: cả hai phía đều 2 cột (name, city)
SELECT name, city FROM employee WHERE city = 'Tokyo'
UNION
SELECT name, city FROM contractor
ORDER BY name;

-- ORDER BY chỉ đặt một lần ở cuối cùng
-- bạn không thể viết ORDER BY riêng cho mỗi SELECT ở giữa

UNION và UNION ALL — loại bỏ trùng lặp hoặc giữ chúng

UNION xếp chồng dọc hai kết quả và gộp các hàng hoàn toàn giống nhau thành một (loại bỏ trùng lặp).

UNION ALL không loại bỏ trùng lặp và giữ mọi hàng từ cả hai phía nguyên trạng.

Khi bạn không cần loại bỏ trùng lặp, hoặc khi bạn muốn giữ "bao nhiêu hàng xuất hiện ở cả hai," hãy dùng UNION ALL.

UNION chạy một phép sắp xếp nội bộ để phát hiện trùng lặp, nên UNION ALL là phép toán nhẹ hơn.

Ví dụ dưới đây nối kết quả "sống ở Kyoto" và kết quả "lương từ 7 triệu trở lên" bên trong employee.

Một nhân viên thỏa cả hai điều kiện trở thành 1 hàng với UNION, và 2 hàng với UNION ALL.

-- UNION: các hàng trùng lặp được gộp thành một
SELECT name FROM employee WHERE city = 'Osaka'
UNION
SELECT name FROM employee WHERE salary >= 5000000
ORDER BY name;

-- UNION ALL: trùng lặp được giữ (các hàng khớp cả hai xuất hiện hai lần)
SELECT name FROM employee WHERE city = 'Osaka'
UNION ALL
SELECT name FROM employee WHERE salary >= 5000000
ORDER BY name;

Hãy hình dung yêu cầu: "tôi muốn kết hợp những nhân viên sống ở Kyoto và những nhân viên có lương cao thành một danh sách. Ai thỏa cả hai chỉ nên xuất hiện một lần." (Chạy đúng thì phần giải thích sẽ hiện ra.)

① Viết một SELECT lấy name từ employee nơi city là Kyoto.

② Nối nó với một SELECT lấy name của các hàng có salary từ 7.000.000 trở lên, dùng một toán tử tập hợp loại bỏ trùng lặp.

③ Sắp xếp kết quả theo name tăng dần (để đảm bảo thứ tự hàng).

Hãy hình dung yêu cầu: "tôi muốn xếp chồng kết quả của cùng hai điều kiện như Thực hành 1 mà không loại bỏ trùng lặp — tất cả chúng — để tôi có thể nhìn thấy bằng mắt những tên nào xuất hiện ở cả hai."

① Nối cùng hai SELECT như Thực hành 1 (name cho sống ở Kyoto / lương từ 7.000.000 trở lên) dùng một toán tử tập hợp giữ trùng lặp.

② Sắp xếp kết quả theo name tăng dần. Xác nhận một tên xuất hiện ở cả hai hiện ra dưới dạng hai hàng liên tiếp.

INTERSECT và EXCEPT — hàng chung và hàng khác biệt

• INTERSECT (giao) trả về chỉ các hàng xuất hiện chung trong cả hai kết quả.
• EXCEPT (hiệu) trả về các hàng có trong kết quả trên nhưng không có trong kết quả dưới.

Cả hai tự động loại bỏ trùng lặp (cùng hành vi như UNION).

EXCEPT là một phép toán bất đối xứng mà kết quả thay đổi nếu bạn hoán đổi phía trên và phía dưới — A EXCEPT B và B EXCEPT A là những thứ khác nhau.

Ví dụ dưới đây nối name cho "sống ở Kyoto" và "lương từ 6.5 triệu trở lên" bên trong employee bằng INTERSECT, lấy ra những nhân viên thỏa cả hai (các hàng chung).

-- INTERSECT: nhân viên sống ở Osaka VÀ có lương từ 6000000 trở lên
SELECT name FROM employee WHERE city = 'Osaka'
INTERSECT
SELECT name FROM employee WHERE salary >= 6000000
ORDER BY name;

-- EXCEPT: nhân viên sống ở Osaka nhưng KHÔNG có lương từ 6000000 trở lên
SELECT name FROM employee WHERE city = 'Osaka'
EXCEPT
SELECT name FROM employee WHERE salary >= 6000000
ORDER BY name;

Giải yêu cầu "tôi muốn lấy ra chỉ những nhân viên sống ở Kyoto VÀ có lương từ 6.500.000 trở lên" dùng phép toán tập hợp.

① Viết một SELECT lấy name từ employee nơi city là Kyoto.

② Nối nó với một SELECT lấy name của các hàng có salary từ 6.500.000 trở lên, dùng một toán tử tập hợp chỉ trả về các hàng chung.

③ Sắp xếp kết quả theo name tăng dần.

Hãy hình dung yêu cầu: "Trong số những nhân viên sống ở Kyoto, tôi muốn lấy ra chỉ những người KHÔNG ở mức lương từ 6.500.000 trở lên (những người Kyoto lương thấp hơn)."

① Viết một SELECT lấy name từ employee nơi city là Kyoto.

② Nối nó với một SELECT lấy name của các hàng có salary từ 6.500.000 trở lên, dùng một toán tử tập hợp trả về các hàng ở phía trên nhưng không ở phía dưới (hãy cẩn thận — thứ tự phía trên và phía dưới ảnh hưởng đến kết quả).

③ Sắp xếp kết quả theo name tăng dần.

Phép toán tập hợp giữa các bảng khác nhau — employee và contractor

Phép toán tập hợp hoạt động không chỉ trong một bảng đơn mà còn giữa các kết quả lấy từ các bảng riêng rẽ.

Miễn là chúng tương thích UNION (khớp số cột và kiểu), các bảng có thể khác nhau.

Khi bạn muốn sắp xếp kết quả đã nối, hãy viết một ORDER BY duy nhất sau SELECT cuối cùng (nó áp dụng cho toàn bộ kết quả đã nối).

Nối name của bảng nhân viên employee và bảng nhà thầu contractor bằng INTERSECT cho phép bạn phát hiện những người xuất hiện ở cả hai với cùng tên.

Ví dụ dưới đây chỉ lấy cột name đơn và nối chúng bằng INTERSECT.

Vì cả name của hai bảng đều có Alice và Bob, 2 người được trả về dưới dạng các hàng chung.

-- các tên xuất hiện ở cả employee và contractor
SELECT name FROM employee
INTERSECT
SELECT name FROM contractor
ORDER BY name;

-- các tên trong contractor nhưng không trong employee (chỉ bên ngoài)
SELECT name FROM contractor
EXCEPT
SELECT name FROM employee
ORDER BY name;

Hãy hình dung yêu cầu: "tôi muốn liệt kê những người trong bảng nhà thầu contractor mà cùng tên KHÔNG tồn tại trong bảng nhân viên employee (các tên thuần chỉ bên ngoài)."

① Viết một SELECT lấy name từ contractor.

② Nối nó với một SELECT lấy name từ employee, dùng một toán tử tập hợp trả về các hàng ở phía trên nhưng không ở phía dưới. Đặt SELECT contractor ở trên.

③ Sắp xếp kết quả theo name tăng dần.