SELF JOIN: dữ liệu phân cấp và so sánh nội bảng

Mở đầu — vì sao bài này quan trọng

Trong các bài trước, bạn đã quen với việc JOIN hai bảng khác nhau: bảng orders nối với customers, bảng products nối với categories. Đó là chuyện hiển nhiên — mỗi bảng đại diện cho một loại thực thể, và bạn ghép chúng lại để có bức tranh đầy đủ.

Nhưng có một loại bài toán mà dữ liệu cần ghép lại nằm... ngay trong chính bảng đó. Nhân viên và người quản lý của họ đều là nhân viên, nằm chung trong bảng employees. Danh mục con và danh mục cha đều là danh mục, nằm chung trong bảng categories. Đơn hàng hôm nay cần so với đơn hàng liền trước của cùng khách — cả hai đều là dòng trong bảng orders.

Khi đó, kỹ thuật bạn cần là SELF JOIN — JOIN một bảng với chính nó. Nghe có vẻ kỳ lạ lúc đầu, nhưng đây là một trong những công cụ phân biệt một BA "biết viết SELECT" với một BA "thật sự đọc được dữ liệu". Rất nhiều câu hỏi nghiệp vụ quan trọng — "Ai báo cáo cho ai?", "Sản phẩm này thuộc danh mục cha nào?", "Đơn này có lớn hơn đơn trước của khách không?" — đều giải được gọn gàng bằng SELF JOIN. Và đây cũng là một câu hỏi phỏng vấn kinh điển cho vị trí Data Analyst/BA. Nắm chắc bài này, bạn xử lý được cả một lớp bài toán phân cấp và so sánh nội bảng mà nhiều người loay hoay không biết bắt đầu từ đâu.

Khái niệm cốt lõi

SELF JOIN là gì?

SELF JOIN không phải là một loại JOIN mới với cú pháp riêng. Nó vẫn là INNER JOIN, LEFT JOIN... như bạn đã học — chỉ khác ở chỗ bảng bên trái và bảng bên phải là cùng một bảng vật lý. Vì SQL không cho phép tham chiếu cùng một tên bảng hai lần một cách mơ hồ, bạn bắt buộc phải dùng alias (bí danh) để phân biệt hai "bản sao" của bảng.

Hãy hình dung: bạn photo bảng employees ra thành hai tờ, đặt cạnh nhau, gọi tờ bên trái là e (employee — nhân viên) và tờ bên phải là m (manager — quản lý). Bây giờ bạn có thể nối dòng nhân viên ở tờ e với dòng quản lý của họ ở tờ m, dựa trên điều kiện e.manager_id = m.employee_id.

SELECT
    e.employee_id,
    e.full_name      AS nhan_vien,
    m.full_name      AS quan_ly
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id;

Điểm mấu chốt nằm ở dòng ON: ta đang nói "lấy giá trị manager_id của nhân viên, đi tìm dòng trong bản sao m có employee_id khớp với nó". Kết quả: mỗi dòng cho ta một nhân viên kèm tên quản lý trực tiếp.

Vì sao alias là bắt buộc, không phải tùy chọn

Nếu bạn viết FROM employees JOIN employees ON ..., SQL engine sẽ không biết employee_id trong điều kiện ON thuộc bản sao nào — nó nhập nhằng (ambiguous). Alias giải quyết chuyện này. Hãy đặt alias có ý nghĩa nghiệp vụ (e cho employee, m cho manager) thay vì t1, t2 — vì khi query dài ra, alias rõ nghĩa giúp bạn và đồng nghiệp đọc nhanh hơn rất nhiều.

Hai họ bài toán SELF JOIN thường gặp

SELF JOIN phục vụ chủ yếu hai nhóm tình huống:

1. Dữ liệu phân cấp (hierarchical / parent–child). Khi một bản ghi tham chiếu đến một bản ghi khác trong cùng bảng theo quan hệ "cha–con": nhân viên → quản lý, danh mục con → danh mục cha, phòng ban con → phòng ban mẹ. Mỗi dòng giữ một khóa ngoại (foreign key) trỏ về dòng "cha" của nó ngay trong bảng. SELF JOIN giúp bạn "trải phẳng" một tầng của cây ra thành cột.

> Lưu ý phạm vi: SELF JOIN chỉ nối được một tầng mỗi lần (nhân viên và sếp trực tiếp, danh mục và cha trực tiếp). Nếu cần đi xuống nhiều tầng không biết trước độ sâu — ví dụ toàn bộ chuỗi cấp trên từ nhân viên lên đến CEO — đó là việc của Recursive CTE (Bài 17). Bài này tập trung vào SELF JOIN cổ điển.

2. So sánh các dòng trong cùng bảng (intra-table comparison). Khi bạn muốn so một dòng với một dòng khác cùng bảng theo một tiêu chí: đơn hàng này với đơn hàng liền trước của cùng khách, doanh thu tháng này với tháng trước, tìm các cặp khách hàng ở cùng một thành phố, hay phát hiện các bản ghi trùng lặp. Ở đây điều kiện JOIN thường không phải khóa ngoại, mà là một biểu thức so sánh như a.customer_id = b.customer_id AND a.order_date > b.order_date.

Tình huống thực tế

Ví dụ 1 — Sơ đồ tổ chức tại Tiki: ai báo cáo cho ai?

Giả sử bạn là BA tại Tiki, phụ trách báo cáo nhân sự cho khối Vận hành. Bảng employees có cấu trúc đơn giản:

Sếp tổng (An) có manager_id là NULL vì không ai quản lý ông. Trưởng phòng báo cáo cho An, nhân viên báo cáo cho trưởng phòng tương ứng.

Câu hỏi của quản lý: "Cho tôi danh sách từng nhân viên kèm tên sếp trực tiếp của họ." Bạn viết:

SELECT
    e.full_name              AS nhan_vien,
    e.title                  AS chuc_danh,
    COALESCE(m.full_name, '(Không có cấp trên)') AS quan_ly_truc_tiep
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

Vì sao dùng LEFT JOIN chứ không phải INNER JOIN? Vì sếp tổng An có manager_id = NULL. Nếu dùng INNER JOIN, dòng của An sẽ không khớp với bất kỳ ai trong bản sao m, và An biến mất khỏi kết quả — báo cáo thiếu mất Giám đốc Vận hành. LEFT JOIN giữ lại mọi nhân viên, và với An thì cột quan_ly_truc_tiep rỗng, ta dùng COALESCE để hiển thị "(Không có cấp trên)".

Bài học rút ra: Trong dữ liệu phân cấp, dòng gốc (root) luôn có khóa cha là NULL. Gần như mọi lần SELF JOIN cây tổ chức, bạn nên dùng LEFT JOIN để không đánh rơi đỉnh của cây.

Ví dụ 2 — Be (gọi xe): so sánh giá cuốc xe này với cuốc liền trước của cùng tài xế

Bạn là BA tại một hãng gọi xe như Be, đang điều tra hiện tượng giá cuốc nhảy bất thường. Bảng trips:

Bạn muốn: với mỗi cuốc, tìm cuốc liền trước của cùng tài xế và tính chênh lệch giá, để lọc ra những cú nhảy giá đáng ngờ. Một cách kinh điển bằng SELF JOIN:

SELECT
    cur.trip_id,
    cur.fare                      AS gia_hien_tai,
    prev.fare                     AS gia_cuoc_truoc,
    cur.fare - prev.fare          AS chenh_lech
FROM trips AS cur
JOIN trips AS prev
    ON cur.driver_id = prev.driver_id
   AND prev.trip_time = (
        SELECT MAX(t.trip_time)
        FROM trips AS t
        WHERE t.driver_id = cur.driver_id
          AND t.trip_time < cur.trip_time
   );

Ý tưởng: nối cuốc hiện tại (cur) với cuốc trước đó (prev) của cùng tài xế, trong đó prev là cuốc có thời gian lớn nhất nhưng vẫn nhỏ hơn thời gian cuốc hiện tại — tức là "cuốc ngay liền trước". Chạy xong, cuốc 103 lộ ra với chenh_lech = +158000 so với cuốc trước — đúng là điểm bất thường cần điều tra.

Bài học rút ra: SELF JOIN dùng được cho so sánh "dòng này với dòng kề" rất tốt, nhưng khi cần điều kiện "liền trước" theo thứ tự, cú pháp dễ cồng kềnh. Trong thực tế ngày nay, bài toán "so với dòng liền trước" thường được giải gọn hơn bằng window function LAG() (Bài 19). Tuy vậy, bạn vẫn nên hiểu cách làm bằng SELF JOIN: nó là nền tảng tư duy, và trong nhiều môi trường SQL cũ hoặc khi phỏng vấn, người ta vẫn hỏi cách này.

Ví dụ 3 — Shopee: tìm các cặp sản phẩm trùng tên trong cùng một shop

Đội Quản lý chất lượng của một sàn như Shopee nhờ bạn rà soát: có những shop đăng trùng sản phẩm (cùng tên, cùng shop) để spam kết quả tìm kiếm. Bảng products:

Bạn cần tìm các cặp sản phẩm khác product_id nhưng cùng shop_id và cùng product_name:

SELECT
    a.shop_id,
    a.product_name,
    a.product_id  AS san_pham_1,
    b.product_id  AS san_pham_2,
    a.price        AS gia_1,
    b.price        AS gia_2
FROM products AS a
JOIN products AS b
    ON a.shop_id      = b.shop_id
   AND a.product_name = b.product_name
   AND a.product_id   < b.product_id;

Điều kiện cuối — a.product_id < b.product_id — là mẹo quan trọng nhất ở đây. Nó làm ba việc cùng lúc: (1) loại bỏ trường hợp một dòng tự ghép với chính nó (a.product_id = b.product_id); (2) tránh đếm trùng mỗi cặp hai lần (cặp 5001–5002 và 5002–5001 chỉ giữ lại một); (3) cho ra kết quả gọn gàng. Kết quả trả về đúng một cặp: 5001 và 5002 ở shop 88. Sản phẩm 5004 không bị bắt vì nó ở shop khác (90), đúng yêu cầu "cùng shop".

Bài học rút ra: Khi SELF JOIN để tìm cặp/tìm trùng, hãy luôn thêm điều kiện bất đẳng thức trên khóa chính (a.id < b.id) để vừa loại self-pairing vừa khử cặp lặp. Đây là pattern bạn sẽ dùng đi dùng lại trong các bài toán data quality (Bài 28).

Hướng dẫn từng bước

Khi gặp một bài toán mà bạn nghi ngờ cần SELF JOIN, hãy đi theo trình tự này:

Bước 1 — Xác nhận hai "vai trò" cùng nằm trong một bảng. Hỏi: "Cái tôi muốn nối có phải cùng loại thực thể với cái đang có không?" Nhân viên nối với quản lý — cả hai đều là nhân viên → đúng là SELF JOIN. Đơn hàng nối với khách hàng — khác loại → đó là JOIN thường, không phải SELF JOIN.

Bước 2 — Đặt hai alias có nghĩa. Gán cho mỗi vai trò một bí danh rõ ràng: e/m, cur/prev, child/parent, a/b. Đây không phải hình thức — alias rõ giúp bạn viết điều kiện ON không nhầm lẫn.

Bước 3 — Viết điều kiện JOIN trong ON. Đây là trái tim của SELF JOIN. Với phân cấp: con.parent_id = cha.id. Với so sánh: a.nhom = b.nhom AND a.<thứ_tự> < b.<thứ_tự>. Luôn tự hỏi: "Dòng bên trái khớp với dòng bên phải theo logic nào?"

Bước 4 — Chọn loại JOIN. Hỏi: "Nếu một dòng không tìm được dòng đối ứng, tôi có muốn giữ nó lại không?" Có (ví dụ giữ sếp tổng không có cấp trên) → LEFT JOIN. Không, chỉ cần các dòng có cặp → INNER JOIN.

Bước 5 — Chọn cột và đặt alias kết quả. Lấy cột từ cả e lẫn m, đặt tên cột kết quả bằng tiếng Việt/nghiệp vụ (AS quan_ly) để báo cáo dễ đọc.

Bước 6 — Kiểm tra với dữ liệu nhỏ. Chạy thử trên vài dòng đã biết kết quả bằng tay. Đặc biệt kiểm tra các ca biên: dòng gốc (parent NULL), dòng không có dòng liền trước, dòng duy nhất trong nhóm.

Lỗi thường gặp & mẹo

Quên alias → lỗi "ambiguous column". Nếu engine báo cột nhập nhằng, gần như chắc chắn bạn quên đặt alias hoặc quên thêm tiền tố alias vào tên cột. Luôn viết e.full_name, không viết trống full_name trong SELF JOIN.

Dùng INNER JOIN làm rớt dòng gốc. Như đã thấy ở ví dụ Tiki, sếp tổng có manager_id = NULL sẽ biến mất nếu dùng INNER JOIN. Với cây phân cấp, mặc định hãy nghĩ đến LEFT JOIN trước.

Quên loại bỏ self-pairing khi tìm cặp. Nếu thiếu điều kiện a.id <> b.id (hoặc tốt hơn là a.id < b.id), mỗi dòng sẽ tự ghép với chính nó, tạo ra các "cặp giả". Luôn nhớ chặn trường hợp này.

Đếm cặp gấp đôi. Dùng a.id <> b.id sẽ loại self-pairing nhưng vẫn trả mỗi cặp hai lần (A–B và B–A). Đổi sang a.id < b.id để mỗi cặp chỉ xuất hiện một lần.

Nhầm chiều của điều kiện cha–con. Rất dễ viết ngược thành con.id = cha.parent_id. Hãy đọc to: "manager_id của nhân viên trỏ tới employee_id của quản lý" → e.manager_id = m.employee_id. Đọc thành lời giúp bạn không lộn chiều.

Mẹo về NULL trong điều kiện JOIN. Các phép so sánh với NULL luôn cho kết quả "không khớp" (không phải đúng, cũng không phải sai). Vì thế dòng có manager_id = NULL sẽ không bao giờ khớp trong ON — đó chính là lý do LEFT JOIN cần thiết để giữ nó lại. Bài 7 (NULL handling) sẽ giúp bạn nắm chắc hơn cơ chế này.

Mẹo hiệu năng. SELF JOIN trên bảng lớn có thể nặng vì engine phải ghép bảng với chính nó. Hãy đảm bảo cột dùng trong ON (như manager_id, customer_id) có index. Bạn chưa cần làm việc này thủ công, nhưng nên biết để hỏi đúng đội Data Engineer (Bài 24 về index sẽ nói kỹ).

Bài tập thực hành

Dùng các bảng giả định sau (bạn có thể tự tạo trong MySQL/PostgreSQL đã cài ở Bài 5):

Bảng employees(employee_id, full_name, title, department, manager_id) và bảng orders(order_id, customer_id, order_date, amount).

• Cơ bản — phân cấp. Viết query liệt kê mỗi nhân viên kèm tên quản lý trực tiếp. Đảm bảo nhân viên không có quản lý (sếp tổng) vẫn xuất hiện, với cột quản lý hiển thị "(Cấp cao nhất)".

• Lọc theo cấp. Mở rộng câu 1: chỉ lấy những nhân viên mà quản lý của họ thuộc phòng "Vận hành". (Gợi ý: thêm điều kiện trên m.department.)

• Đếm cấp dưới. Với mỗi quản lý, đếm xem họ có bao nhiêu nhân viên báo cáo trực tiếp. (Gợi ý: SELF JOIN rồi GROUP BY theo quản lý — kết hợp kiến thức Bài 3/14.)

• So sánh nội bảng. Tìm tất cả các cặp nhân viên cùng phòng ban nhưng khác người, mỗi cặp chỉ hiện một lần. (Gợi ý: điều kiện a.department = b.department AND a.employee_id < b.employee_id.)

• Nâng cao — đơn liền trước. Với mỗi đơn hàng trong bảng orders, tìm đơn liền trước của cùng khách hàng và tính chênh lệch amount. Lọc ra các đơn có giá trị lớn hơn gấp đôi đơn liền trước. Sau khi làm xong bằng SELF JOIN, hãy ghi chú lại để sau này so sánh với cách dùng LAG() ở Bài 19.

Hãy tự kiểm tra kết quả trên 4–5 dòng dữ liệu mẫu mà bạn biết đáp án bằng tay trước khi tin vào output.

Tóm tắt

SELF JOIN là kỹ thuật JOIN một bảng với chính nó, dùng alias để phân biệt hai "bản sao". Nó không có cú pháp mới — vẫn là INNER/LEFT JOIN quen thuộc — nhưng mở khóa cả một lớp bài toán quan trọng cho BA.

Hai họ ứng dụng cốt lõi cần nhớ: dữ liệu phân cấp (nhân viên–quản lý, danh mục con–cha — nối một tầng, thường dùng LEFT JOIN để giữ dòng gốc có khóa cha NULL) và so sánh nội bảng (so dòng với dòng kề, tìm cặp/tìm trùng — luôn thêm a.id < b.id để khử self-pairing và cặp lặp).

Quy trình sáu bước — xác nhận cùng bảng, đặt alias rõ nghĩa, viết điều kiện ON, chọn loại JOIN, chọn cột, kiểm tra ca biên — sẽ giúp bạn không bao giờ lúng túng khi gặp bài toán SELF JOIN. Và hãy nhớ ranh giới: SELF JOIN nối một tầng; muốn đi sâu nhiều tầng không biết trước độ sâu thì cần Recursive CTE (Bài 17), còn so với dòng liền trước theo thứ tự thì window function LAG/LEAD (Bài 19) thường gọn hơn. Nắm chắc SELF JOIN, bạn đã có một công cụ mà rất nhiều BA bỏ lỡ — và cũng là một điểm cộng chắc chắn trong phỏng vấn.