Soạn truy vấn — Kết hợp JOIN, truy vấn con, và UNION

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

JOIN (kết nối nhiều bảng), truy vấn con (một truy vấn bên trong một truy vấn), và UNION (xếp chồng dọc các kết quả) mỗi cái hoạt động riêng, nhưng khi bạn kết hợp chúng với WHERE / ORDER BY / LIMIT bạn có thể viết các truy vấn thực dụng, sát thực tế.

Trong bài viết này bạn sẽ đi qua, lần lượt, cách kết hợp các kỹ thuật này và phát triển chúng thành một truy vấn duy nhất.

Dữ liệu là ba bảng nhân viên — employee (30 nhân viên), department (6 phòng ban), và sales (50 dòng doanh số).

Bạn sẽ bắt đầu với một JOIN đơn giản, rồi xây dựng dần qua truy vấn con và UNION từng bước.

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

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

② Dùng SELECT * FROM table_name LIMIT 5; để xem trước 5 hàng đầu tiên của mỗi bảng.

Nắm chắc thứ tự các mệnh đề được đánh giá — JOIN, WHERE, ORDER BY, LIMIT

Thứ tự bạn viết một truy vấn (SELECT … FROM … WHERE … ORDER BY) khác với thứ tự nó được đánh giá.

Cơ sở dữ liệu trước tiên kết nối các bảng bằng FROM / JOIN, rồi thu hẹp hàng bằng WHERE, tổng hợp bằng GROUP BY / HAVING, dựng cột bằng SELECT, và cuối cùng sắp xếp bằng ORDER BY và giới hạn số hàng bằng LIMIT.

Một khi bạn nắm được thứ tự này, các quy tắc như "để lọc trên một kết quả đã tổng hợp dùng HAVING, không phải WHERE" và "LIMIT có hiệu lực sau khi sắp xếp" bắt đầu trở nên hợp lý.

-- JOIN + WHERE + ORDER BY: nhân viên trong các phòng ban Osaka, sắp theo ngày tuyển
SELECT emp.name, dept.dept_name, emp.hired_on
FROM employee emp
JOIN department dept ON dept.dept_id = emp.dept_id
WHERE dept.location = 'Osaka'
ORDER BY emp.hired_on;

Kết hợp JOIN, WHERE, ORDER BY, và LIMIT trong một truy vấn duy nhất. (Chạy đúng thì phần giải thích sẽ hiện ra.)

① Inner join employee và department trên dept_id và lấy ra tên nhân viên, tên phòng ban, và lương.

② Thu hẹp về chỉ những nhân viên có vị trí phòng ban (department.location) là Tokyo.

③ Sắp xếp theo lương từ cao xuống thấp và chỉ lấy top 5.

Xây dựng một giá trị tham chiếu bằng truy vấn con và thu hẹp kết quả JOIN

Một truy vấn con có thể được dùng để xây dựng "giá trị tham chiếu" bạn cần để thu hẹp thêm một kết quả bạn đã ráp bằng JOIN.

Một giá trị tiêu chí thay đổi theo từng nhân viên, như "lương trung bình của chính phòng ban mình," được tính bằng một truy vấn con tương quan (một truy vấn con được tính lại cho từng nhân viên riêng lẻ) tham chiếu hàng ở ngoài.

Khi bạn gắn tên phòng ban bằng JOIN và thu hẹp hàng dùng một điều kiện truy vấn con tương quan, nhiều mảnh cú pháp ăn khớp với nhau trong một truy vấn.

-- JOIN + truy vấn con tương quan: nhân viên có lương bằng mức tối đa của chính phòng ban họ (người có lương cao nhất trong phòng ban)
SELECT emp.name, dept.dept_name, emp.salary
FROM employee emp
JOIN department dept ON dept.dept_id = emp.dept_id
WHERE emp.salary = (
  SELECT MAX(dept_member.salary)
  FROM employee dept_member
  WHERE dept_member.dept_id = emp.dept_id
);

Lần này bạn sẽ xếp chồng các truy vấn con. Liệt kê, kèm tên phòng ban, những nhân viên trong từng phòng ban có lương "cao hơn lương trung bình của chính phòng ban họ."

① Join employee và department và lấy ra tên nhân viên, tên phòng ban, và lương.

② Dùng một truy vấn con tương quan để tính lương trung bình của chính phòng ban nhân viên đó, và thu hẹp về chỉ những nhân viên có salary lớn hơn nó.

③ Sắp xếp theo dept_id tăng dần, và trong cùng một phòng ban thì theo lương từ cao xuống thấp.

Kết hợp hai góc nhìn bằng UNION và kết thúc với ORDER BY

UNION xếp chồng dọc kết quả của hai SELECT.

SELECT trên và dưới phải tương thích UNION (cùng số cột, với kiểu của các cột tương ứng tương thích).

UNION gộp các hàng hoàn toàn giống nhau thành một (loại bỏ trùng lặp), còn UNION ALL giữ trùng lặp nguyên trạng.

Sắp xếp và giới hạn số hàng áp dụng cho toàn bộ kết quả đã kết hợp, nên hãy viết ORDER BY / LIMIT chỉ một lần, ở cuối cùng.

-- UNION ALL: xếp chồng dọc hai góc nhìn với một cột nhãn (giữ trùng lặp)
SELECT name, 'Kyoto' AS via FROM employee WHERE city = 'Kyoto'
UNION ALL
SELECT emp.name, 'HighSales' AS via
FROM employee emp
JOIN sales sale ON sale.emp_id = emp.emp_id
GROUP BY emp.emp_id
HAVING SUM(sale.amount) >= 1500000
ORDER BY name;

Cuối cùng, kết hợp hai góc nhìn thành một danh sách duy nhất bằng UNION.

① Viết một SELECT lấy ra tên của "những nhân viên sống ở Kyoto."

② Viết một SELECT tìm "những nhân viên có tổng doanh số từ 1,5 triệu đồng trở lên" dùng một phép join của employee và sales cộng tổng hợp (GROUP BY / HAVING).

③ Nối hai cái bằng UNION để gộp trùng lặp thành một hàng duy nhất, và sắp xếp theo tên tăng dần.