Kết nối bảng (3) — Xếp chồng WHERE / ORDER BY / CASE lên JOIN

Dữ liệu dùng trong bài — department, employee và sales

Trong các bài trước bạn đã thấy các biến thể của kết nối — INNER JOIN, OUTER JOIN, và tự kết nối.

Trong bài này bạn sẽ xếp chồng lọc WHERE, ORDER BY, và nhãn band CASE lên trên JOIN và dựng đúng hình dạng của một báo cáo tổng hợp mà bạn sẽ gặp ở công việc thực tế.

Dữ liệu là department (6 phòng ban) và employee (30 nhân viên), cộng với bảng sales mới (50 dòng) gồm các dòng chi tiết bán hàng.

sales.emp_id là khóa ngoại trỏ tới employee.emp_id.

Chúng ta sẽ kết nối ba bảng để dựng một báo cáo in tên phòng ban và tên người phụ trách bên cạnh mỗi giao dịch.

Trước khi vào bài tập, hãy xác nhận định nghĩa cột và các hàng mẫu của ba bảng — department, employee, và sales.

① Chạy PRAGMA table_info(table_name); trên từng bảng để xem các cột.

② Chạy SELECT * FROM table_name LIMIT 5; trên từng bảng để xem trước 5 hàng đầu. Hãy chú ý cách sales.emp_id trỏ tới một hàng trong employee.

Kết nối ba bảng — nối tiếp các JOIN

Để kết nối ba bảng trở lên, bạn chỉ cần nối tiếp các mệnh đề `JOIN ... ON ...` từng cái một.

Viết FROM sales s JOIN employee e ON s.emp_id = e.emp_id JOIN department d ON e.dept_id = d.dept_id — trước tiên nối sales với employee qua emp_id, rồi nối kết quả đó với department qua dept_id.

Cách đọc thứ tự tự nhiên nhất là: "bắt đầu từ các dòng chi tiết (sales), rồi gắn thông tin nhân viên và phòng ban lên trên".

Mọi cái đều là INNER JOIN, nên chỉ những hàng khớp ở cả ba bảng mới sống sót.

Trong tập dữ liệu này cả 12 nhân viên có doanh số đều thuộc một phòng ban nào đó, nên INNER JOIN 3 bảng trả về toàn bộ 50 hàng của sales.

-- Gắn tên nhân viên và dept_name vào mỗi giao dịch (INNER JOIN 3 bảng)
SELECT s.sale_id, e.name, d.dept_name, s.amount, s.sale_date
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY s.sale_id;

Hãy hình dung yêu cầu: "Tôi muốn một báo cáo các dòng chi tiết bán hàng có gắn tên nhân viên phụ trách và tên phòng ban của họ". (Phần giải thích sẽ xuất hiện khi truy vấn của bạn chạy đúng.)

① INNER JOIN sales (bí danh s) với employee (bí danh e) qua emp_id, rồi INNER JOIN với department (bí danh d) qua dept_id.

② Chọn 4 cột s.sale_id, e.name, d.dept_name, và s.amount.

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

Lọc với WHERE, sắp xếp với ORDER BY

Khi đã có JOIN, dùng WHERE để chỉ giữ những hàng bạn cần và ORDER BY để xếp chúng theo thứ tự dễ đọc.

Điều kiện WHERE có thể tham chiếu các cột từ bất kỳ bảng nào đã join.

WHERE d.location = 'Tokyo' chỉ giữ các giao dịch từ phòng ban ở Tokyo, và WHERE s.amount >= 400000 chỉ giữ các giao dịch giá trị cao.

Các mệnh đề SQL được viết theo thứ tự FROM → JOIN → ON → WHERE → ORDER BY.

WHERE lọc các hàng sau khi join hoàn tất, và ORDER BY sắp xếp sau cùng.

Để sắp xếp theo nhiều cột, dùng dấu phẩy: ORDER BY col1 DESC, col2. Các hàng bằng nhau ở col1 sẽ được xếp tiếp theo col2 (khóa phụ để ổn định thứ tự).

-- Chỉ các giao dịch của phòng ban ở Osaka, giá trị cao nhất trước
SELECT e.name, d.dept_name, s.amount, s.sale_date
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
WHERE d.location = 'Osaka'
ORDER BY s.amount DESC, s.sale_id;

Hãy hình dung yêu cầu: "Tôi muốn báo cáo các giao dịch ở phòng ban đặt tại Tokyo có giá trị từ 400,000 trở lên, sắp xếp theo amount giảm dần".

① INNER JOIN sales (s), employee (e), và department (d) qua emp_id và dept_id.

② Lọc về các hàng có d.location là Tokyo và s.amount từ 400000 trở lên.

③ Chọn 3 cột e.name, d.dept_name, và s.amount, sắp xếp theo s.amount giảm dần. Với các hàng có amount bằng nhau, dùng s.sale_id tăng dần làm khóa phụ để ổn định thứ tự.

Thêm nhãn band với CASE — hoàn thiện báo cáo có phân loại

Trên nền JOIN, WHERE, và ORDER BY, một biểu thức CASE có thể thêm một cột với nhãn band cho amount, biến kết quả thành một báo cáo mà con người có thể đọc lướt một cái là hiểu.

CASE đánh giá các điều kiện từ trên xuống và trả về giá trị của nhánh đầu tiên cho kết quả đúng. Bạn có thể đặt nó trực tiếp trong danh sách cột SELECT (dạng tìm kiếm: CASE WHEN điều_kiện THEN giá_trị ... ELSE mặc_định END).

Ví dụ, chia mỗi giao dịch thành ba band theo amount: High khi amount >= 400000, Mid khi amount >= 200000, và Low ngược lại — thể hiện band trong một cột đặt bí danh band.

Giữ các nhãn là chuỗi ASCII cố định ('High' / 'Mid' / 'Low') để kết quả ổn định không phụ thuộc môi trường.

Khi gộp JOIN 3 bảng, WHERE, ORDER BY, và CASE vào một truy vấn duy nhất, bạn có gần như đúng hình dạng của các báo cáo tổng hợp được dựng hằng ngày trong các đội ngũ thực tế.

-- Ví dụ: chia amount thành 2 band (Large >= 300000)
SELECT e.name, d.dept_name, s.amount,
  CASE
    WHEN s.amount >= 300000 THEN 'Large'
    ELSE 'Small'
  END AS size
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Sales'
ORDER BY s.sale_id;

Hãy hình dung yêu cầu: "Tôi muốn một báo cáo bán hàng có tên nhân viên, tên phòng ban, và một nhãn band, sắp xếp theo amount giảm dần".

① INNER JOIN sales (s), employee (e), và department (d) qua ba bảng.

② Thêm một cột bí danh band sử dụng CASE dạng tìm kiếm trả về 'High' khi s.amount từ 400000 trở lên, 'Mid' khi từ 200000 trở lên, và 'Low' ngược lại.

③ Chọn 4 cột e.name, d.dept_name, s.amount, và band, sắp xếp theo s.amount giảm dần với s.sale_id tăng dần làm khóa phụ.

Hãy hình dung yêu cầu: "Tôi muốn một bảng tổng kết tính tổng doanh số của từng nhân viên và phân hạng họ thành Gold / Silver / Bronze theo tổng số".

① INNER JOIN sales (s), employee (e), và department (d).

② Nhóm theo e.name và d.dept_name, và tổng hợp SUM(s.amount) đặt bí danh total.

③ Thêm một cột bí danh tier sử dụng CASE dạng tìm kiếm trả về 'Gold' khi SUM(s.amount) từ 1500000 trở lên, 'Silver' khi từ 1000000 trở lên, và 'Bronze' ngược lại.

④ Chọn 4 cột e.name, d.dept_name, total, và tier, sắp xếp theo total giảm dần với e.name tăng dần làm khóa phụ.

Nhân viên không có doanh số biến mất dưới INNER JOIN

Mọi báo cáo trong bài này đều dùng INNER JOIN, nên 18 nhân viên không có giao dịch nào sẽ không bao giờ xuất hiện trong kết quả.

Nếu bạn cần một báo cáo bao gồm mọi nhân viên — với số 0 cho những người không có doanh số — hãy bắt đầu từ employee làm trục xương sống và LEFT JOIN sales lên trên, rồi bọc SUM trong COALESCE(SUM(s.amount), 0) để chuyển tổng NULL thành 0.

Mỗi khi các con số trong báo cáo không khớp, điều đầu tiên cần nghi ngờ là "loại kết nối sai đang âm thầm loại bỏ các hàng đáng lẽ phải có".