Kết hợp bảng (1) — INNER JOIN và alias

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

Cho đến giờ bạn đã làm việc với một bảng tại một thời điểm, nhưng dữ liệu thực tế được chia ra trên nhiều bảng. Tên và lương của nhân viên nằm trong bảng employee, tên và vị trí của phòng ban nằm trong bảng department — thiết kế thông thường chia dữ liệu theo vai trò và liên kết chúng bằng employee.dept_id trỏ tới department (cái "cột trỏ tới một dòng của bảng khác" này được gọi là khóa ngoại).

Trong bài này bạn sẽ học INNER JOIN, dạng cơ bản nhất của JOIN kết hợp hai bảng riêng biệt thành một kết quả. Tài liệu là bảng department (6 phòng ban) và bảng employee (30 nhân viên). Thông qua một bài tập xây dựng danh sách hiển thị tên phòng ban của từng nhân viên, bạn sẽ lần lượt thử alias bảng, ON và USING.

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

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

② Chạy SELECT * FROM department LIMIT 5; và SELECT * FROM employee LIMIT 5; để xem trước 5 dòng đầu. Hãy lưu ý rằng một số dòng trong employee có NULL ở cột dept_id.

INNER JOIN — nối hai bảng bằng điều kiện kết hợp

Bảng employee chỉ chứa dept_id (số phòng ban). Khi bạn muốn danh sách nhân viên hiển thị thêm tên phòng ban, bạn lấy các dòng mà employee.dept_id và department.dept_id khớp nhau, xếp chúng cạnh nhau và gộp thành một dòng. Đây là JOIN, và cái cơ bản nhất là INNER JOIN (INNER = kết hợp trong).

Dạng viết là SELECT cột FROM bảng_trái JOIN bảng_phải ON điều_kiện_kết_hợp. JOIN là viết tắt của INNER JOIN — cả hai hành xử giống hệt nhau. Trong ON bạn viết điều kiện kết hợp, nói rằng "nếu cột nào khớp với cột nào thì coi các dòng đó là cùng một dòng". INNER JOIN chỉ giữ lại những dòng đã khớp ở cả hai bảng; các dòng không có đối tác ở một bên bị loại khỏi kết quả.

-- Hiển thị dept_name cạnh mỗi nhân viên (INNER JOIN)
SELECT employee.name, employee.salary, department.dept_name
FROM employee
JOIN department
  ON employee.dept_id = department.dept_id;

Hãy hình dung một yêu cầu: "xây dựng một bảng cho danh sách nhân viên với số nhân viên, tên và tên phòng ban họ thuộc về". (Chạy đúng thì phần giải thích sẽ hiện ra.)

① INNER JOIN bảng employee và bảng department. Điều kiện kết hợp là dept_id khớp nhau ở cả hai bảng.

② Lấy ba cột: emp_id và name từ employee, và dept_name từ department.

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

Alias bảng — viết ngắn các tên bảng dài

Viết tên bảng mỗi lần, như trong employee.name hay department.dept_name, làm câu truy vấn dài ra. Nếu bạn đặt một alias sau tên bảng, như trong FROM employee e, từ đó về sau bạn có thể tham chiếu tới nó bằng tên ngắn như e.name. Viết với AS, như FROM employee AS e, mang cùng ý nghĩa.

Alias cũng trở nên cần thiết cho một self-join (dùng cùng một bảng hai lần, được trình bày ở bài kế tiếp) và để đặt tên cho một kết quả trong subquery. Trong bài này chúng ta sẽ viết employee là e và department là d.

-- Đặt alias employee là e và department là d
SELECT e.name, e.city, d.dept_name, d.location
FROM employee e
JOIN department d
  ON e.dept_id = d.dept_id;

Hãy hình dung một yêu cầu: "chỉ hiển thị tên các nhân viên thuộc phòng Engineering, cùng với vị trí của phòng đó".

① INNER JOIN employee với alias e và department với alias d. Điều kiện kết hợp là dept_id khớp nhau.

② Thu hẹp về các dòng mà dept_name là Engineering.

③ Lấy hai cột — name từ e và location từ d — sắp xếp theo name tăng dần.

USING — cách viết tắt khi tên cột kết hợp trùng nhau

employee và department được kết hợp trên cùng một tên cột, `dept_id`, ở cả hai bảng. Khi tên của cột dùng để kết hợp hoàn toàn giống nhau ở cả hai bảng như thế này, bạn có thể viết USING (dept_id) thay cho ON e.dept_id = d.dept_id.

USING (tên_cột) mang cùng ý nghĩa với ON trái.tên_cột = phải.tên_cột, và ngắn hơn vì bạn chỉ viết cột kết hợp một lần. Hơn nữa, một cột được kết hợp bằng USING chỉ xuất hiện một lần trong kết quả, và bạn có thể tham chiếu trực tiếp bằng SELECT dept_id (không có tên bảng). Khi tên cột khác nhau (ví dụ e.dept_id và d.id), bạn không thể dùng USING — bạn dùng ON.

-- Viết cùng ý nghĩa với ON e.dept_id = d.dept_id bằng USING
SELECT e.name, dept_id, d.dept_name
FROM employee e
JOIN department d
  USING (dept_id);

Hãy hình dung một yêu cầu: "trong tất cả nhân viên, lấy top 5 theo lương và liệt kê tên, tên phòng ban và lương của họ".

① Kết hợp employee (alias e) và department (alias d) bằng mệnh đề `USING`. Cột kết hợp là dept_id, chung cho cả hai bảng.

② Lấy ba cột: name từ e, dept_name từ d và salary từ e.

③ Sắp xếp theo salary giảm dần và thu hẹp về 5 dòng đầu.

INNER JOIN loại bỏ các dòng chỉ tồn tại ở một bên

INNER JOIN trả về chỉ những dòng mà điều kiện kết hợp khớp ở cả hai bảng. 4 người có dept_id là NULL (Ivan, Quinn, Xander, Brian) và phòng ban Legal không có nhân viên nào không có đối tác kết hợp, nên họ không xuất hiện trong kết quả. "Tôi muốn tất cả nhân viên nhưng chỉ trả về 26 dòng" trong hầu hết trường hợp là do hành vi này. Khi bạn muốn giữ cả các dòng chỉ tồn tại ở một bên, hãy dùng OUTER JOIN được trình bày ở bài kế tiếp.