Kết nối bảng (2) — OUTER JOIN, CROSS JOIN, tự kết nối

OUTER JOIN — giữ lại cả những dòng chỉ tồn tại ở một bên

INNER JOIN lần trước chỉ trả về những dòng khớp ở cả hai bảng, nên nhân viên không có phòng ban và phòng ban không có nhân viên đều biến mất khỏi kết quả. Khi yêu cầu là "tôi muốn tất cả nhân viên; người không có phòng ban thì để tên phòng ban trống cũng được", bạn dùng OUTER JOIN vốn luôn giữ lại các dòng ở một bên.

OUTER JOIN có 3 loại tùy vào bên bạn giữ: LEFT JOIN giữ mọi dòng của bảng bên trái, RIGHT JOIN giữ mọi dòng của bảng bên phải, và FULL OUTER JOIN giữ mọi dòng của cả hai. Các cột ở bên không có khớp sẽ được lấp bằng NULL. Console của khóa học này dùng SQLite, và bạn có thể chạy cả LEFT / RIGHT / FULL y như vậy. Dữ liệu giống lần trước: department (6 phòng ban) và employee (30 nhân viên).

LEFT JOIN — luôn giữ bảng bên trái

Khi bạn viết SELECT cols FROM left LEFT JOIN right ON condition, mọi dòng của bảng bên trái đều được giữ, và nếu không có dòng khớp ở bảng bên phải thì các cột bên phải trở thành NULL. LEFT JOIN là viết tắt của LEFT OUTER JOIN, và cả hai hoạt động giống nhau. Bạn dùng nó khi muốn liệt kê bên chính mà không bỏ sót ai, như "hiển thị tất cả nhân viên, và để trống tên phòng ban cho người không có phòng ban".

-- Giữ tất cả nhân viên. Nếu không có phòng ban thì dept_name là NULL
SELECT e.name, e.city, d.dept_name
FROM employee e
LEFT JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY e.emp_id;

Hãy hình dung yêu cầu: "tôi muốn tìm các nhân viên không thuộc phòng ban nào, xếp dept_id ở bên nhân viên và bên phòng ban cạnh nhau, và xác nhận vì sao không tra được tên phòng ban". (Chạy đúng thì phần giải thích sẽ hiện ra.)

① LEFT JOIN employee (bí danh e) ở bên trái và department (bí danh d) ở bên phải, rồi thu hẹp chỉ còn các dòng mà d.dept_id là NULL.

② Ngoài e.name và e.city, hãy lấy thêm `e.dept_id` (bên nhân viên) và `d.dept_id` / `d.dept_name` (bên phòng ban). Hãy xác nhận bằng mắt rằng vì bản thân e.dept_id là NULL (chưa gán phòng ban) nên không có đối tác để kết nối, do đó d.dept_id và d.dept_name cũng là NULL. Sắp xếp theo e.name tăng dần.

③ Sau đó đổi cùng phép kết nối đó sang INNER JOIN và xác nhận rằng không có lấy một dòng nào trả về (vì các dòng có khóa kết nối là NULL biến mất khi dùng INNER JOIN).

RIGHT JOIN và FULL OUTER JOIN

RIGHT JOIN là LEFT JOIN đảo trái phải — nó giữ mọi dòng của bảng bên phải. Khi bạn viết employee e RIGHT JOIN department d ON ..., phòng ban Legal không có nhân viên nào vẫn nằm lại trong kết quả, và các cột phía employee trở thành NULL. Nó phù hợp cho "hiển thị tất cả phòng ban, và hiển thị các phòng ban không có nhân viên với các trường để trống".

FULL OUTER JOIN giữ mọi dòng của cả hai bảng. Các dòng khớp được nối cạnh nhau, các dòng chỉ tồn tại ở bên trái (4 người không có phòng ban) thì NULL ở bên phải, và các dòng chỉ tồn tại ở bên phải (phòng ban Legal) thì NULL ở bên trái — tất cả xếp chung trong một kết quả.

-- RIGHT JOIN: giữ tất cả phòng ban. Legal có NULL ở bên nhân viên
SELECT d.dept_name, e.name
FROM employee e
RIGHT JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

-- FULL OUTER JOIN: giữ cả nhân viên không có phòng ban lẫn phòng ban không có nhân viên
SELECT e.name, d.dept_name
FROM employee e
FULL OUTER JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

Hãy hình dung yêu cầu: "tôi muốn xác nhận các phòng ban không có lấy một nhân viên nào được phân, bằng cách xếp các cột bên phòng ban và các cột bên nhân viên cạnh nhau".

① RIGHT JOIN employee (bí danh e) ở bên trái và department (bí danh d) ở bên phải, rồi thu hẹp chỉ còn các dòng mà e.emp_id là NULL.

② Lấy d.dept_id / d.dept_name / d.location (bên phòng ban) và `e.emp_id` / `e.name` (bên nhân viên). Hãy xác nhận rằng d.dept_id có giá trị (số phòng ban) trong khi e.emp_id và e.name là NULL (trống) — nghĩa là phòng ban thực sự tồn tại nhưng có 0 nhân viên được phân. Sắp xếp theo d.dept_name tăng dần.

Hãy hình dung yêu cầu: "tôi muốn đối chiếu nhân viên và phòng ban vào một bảng, xếp dept_id của cả hai bên cạnh nhau, và xác nhận các dòng chỉ một bên trở thành NULL như thế nào".

① FULL OUTER JOIN employee (bí danh e) và department (bí danh d). Điều kiện kết nối là khớp dept_id.

② Lấy e.emp_id / e.name / `e.dept_id` (bên nhân viên) và `d.dept_id` / `d.dept_name` (bên phòng ban). Các dòng khớp có giá trị ở cả hai bên, nhưng nhân viên chưa được gán phòng ban thì cả `e.dept_id` lẫn `d.dept_id` đều NULL, và phòng ban không có nhân viên (Legal) thì `d.dept_id` có giá trị còn bên `e` là NULL. Hãy xác nhận bằng mắt mỗi "dòng chỉ một bên" thuộc kiểu nào.

③ Sắp xếp theo d.dept_id tăng dần (các dòng mà d.dept_id là NULL gom về phía đầu).

CROSS JOIN — tạo mọi tổ hợp

CROSS JOIN không có điều kiện kết nối và tạo mọi tổ hợp của từng dòng bảng bên trái với từng dòng bảng bên phải. Số dòng kết quả là số dòng bên trái × số dòng bên phải (tích Descartes). Với employee (30 dòng) và department (6 dòng), đó là 30 × 6 = 180 dòng.

Trong thực tế bạn dùng nó để liệt kê mọi tổ hợp có thể như "tất cả nhân viên × tất cả các tháng" hoặc "tất cả cửa hàng × tất cả sản phẩm", rồi kết nối số liệu thực tế vào đó và lấp 0 cho các ô không có số liệu thực — làm nền cho một phép tổng hợp. Vì là kết nối không có điều kiện nên bạn không thêm ON.

-- Đếm số tổ hợp tất-cả-nhân-viên x tất-cả-phòng-ban
SELECT count(*) AS combo_count
FROM employee e
CROSS JOIN department d;

-- Xem một phần các tổ hợp (chỉ các cặp với phòng ban Sales)
SELECT e.name, d.dept_name
FROM employee e
CROSS JOIN department d
WHERE d.dept_name = 'Sales'
ORDER BY e.emp_id;

Hãy hình dung yêu cầu: "tôi muốn đếm xem giữa tất cả nhân viên và tất cả phòng ban có bao nhiêu tổ hợp có thể".

① CROSS JOIN employee (bí danh e) và department (bí danh d) (đừng viết điều kiện kết nối).

② Đếm tổng số tổ hợp bằng count(*), và đặt bí danh cho cột kết quả là combo_count.

Tự kết nối — dùng cùng một bảng hai lần để tra tên quản lý

Bảng employee có cột manager_id (số nhân viên của quản lý), và quản lý cũng là một nhân viên trong cùng bảng employee. Khi bạn muốn "hiển thị tên mỗi nhân viên kèm tên quản lý của họ", bạn JOIN cùng một bảng hai lần. Cái này gọi là tự kết nối.

Bạn viết là FROM employee e JOIN employee m ON e.manager_id = m.emp_id. Bạn làm cho cùng một bảng xuất hiện hai lần với các bí danh tách vai trò — e là "bên nhân viên" và m là "bên quản lý". Bí danh là bắt buộc trong tự kết nối; không có chúng bạn không phân biệt được employee nào là cái nào. Với INNER JOIN, nhân viên không có quản lý biến mất, nên hãy dùng LEFT JOIN nếu muốn liệt kê tất cả nhân viên.

-- Hiển thị tên quản lý kèm theo mỗi nhân viên (chỉ nhân viên có quản lý: INNER)
SELECT e.name AS employee, m.name AS manager
FROM employee e
JOIN employee m
  ON e.manager_id = m.emp_id
ORDER BY e.emp_id;

Hãy hình dung yêu cầu: "tôi muốn liệt kê tất cả nhân viên kèm tên quản lý của họ bên cạnh. Nhân viên không có quản lý thì để trống tên quản lý cũng được".

① LEFT JOIN employee với bí danh e (bên nhân viên) và employee lần nữa với bí danh m (bên quản lý). Điều kiện kết nối là e.manager_id = m.emp_id.

② Lấy 2 cột, đặt bí danh e.name là employee và m.name là manager.

③ Sắp xếp theo e.emp_id tăng dần.

NULL của OUTER JOIN đổi nghĩa tùy chỗ bạn đặt WHERE

Nếu sau một LEFT JOIN bạn viết điều kiện trên một cột bên phải như WHERE right_table.column = value, NULL luôn được đánh giá là sai trong phép so sánh, nên các dòng không có đối tác bị rớt và bạn thực chất được kết quả giống như INNER JOIN. Khi muốn thu hẹp mà vẫn giữ "các dòng không có đối tác", hãy viết điều kiện đó ở phía ON, hoặc dùng WHERE right_table.key IS NULL để nhặt rõ ràng "các dòng không có đối tác". Để kiểm tra NULL, luôn dùng IS NULL / IS NOT NULL, không bao giờ dùng = NULL.