Self-join: khi bạn cần so một dòng với dòng khác cùng bảng

“Nhân viên nào lương cao hơn sếp?” Một bảng, một câu hỏi, và không có bảng thứ hai nào để JOIN.

Bài toán

Bảng nhan_vien có cột quan_ly_id trỏ ngược về id của chính bảng đó. Câu hỏi: ai đang lương cao hơn sếp mình?

Không có bảng thứ hai. Vậy hãy coi cùng một bảng là hai bảng, bằng cách đặt hai bí danh khác nhau:

SELECT
  nv.ten   AS nhan_vien,
  nv.luong,
  ql.ten   AS quan_ly,
  ql.luong AS luong_ql
FROM nhan_vien nv
JOIN nhan_vien ql ON ql.id = nv.quan_ly_id
WHERE nv.luong > ql.luong;

Lỗi thường gặp

1. Quên bí danh

-- Lỗi: không biết id nào thuộc bảng nào
FROM nhan_vien JOIN nhan_vien ON id = quan_ly_id

Self-join bắt buộc có bí danh cho cả hai vế.

2. Dùng INNER JOIN khi cần LEFT JOIN

Ở ví dụ trên, An không có quản lý (quan_ly_id IS NULL) nên bị loại khỏi kết quả. Nếu báo cáo cần liệt kê mọi nhân viên, dùng LEFT JOIN:

FROM nhan_vien nv
LEFT JOIN nhan_vien ql ON ql.id = nv.quan_ly_id

3. Đếm cặp hai lần

Bài toán “tìm mọi cặp nhân viên cùng phòng ban” nếu viết ON a.phong_ban = b.phong_ban AND a.id <> b.id sẽ trả về cả (An, Bình) lẫn (Bình, An). Đổi thành a.id < b.id để mỗi cặp chỉ xuất hiện một lần.

Dạng hay gặp thứ hai: so với ngày liền trước

SELECT h.id
FROM thoi_tiet h
JOIN thoi_tiet t ON t.ngay = h.ngay - INTERVAL '1 day'
WHERE h.nhiet_do > t.nhiet_do;

Đây chính là LeetCode 197. Trên hệ có hàm window, LAG nhanh hơn và dễ đọc hơn — nhưng self-join vẫn là cách duy nhất trên MySQL 5.7.

Luyện tập

• LeetCode 181 — Employees Earning More Than Their Managers
• LeetCode 197 — Rising Temperature
• LeetCode 1747 — Leetflex Banned Accounts
• LeetCode 1607 — Sellers With No Sales
• HackerRank — Placements