Self-JOIN: một bảng có chính nó

Đôi khi mối quan hệ bạn muốn vượt qua lại không phải giữa hai người các bảng khác nhau nhưng giữa các hàng của cùng một bảng. Ví dụ điển hình: một Hệ thống phân cấp "danh mục → danh mục con", trong đó cả cha và con đều là các hàng cùng một bảng categories, được liên kết bởi parent_id.

Để đọc cùng nhau một hàng (con) và hàng khác "của nó" (cha), bạn sử dụng một tự tham gia: cùng một bảng xuất hiện hai lần trong FROM, với hai bí danh khác nhau.

Cú pháp

SELECT colonne
FROM   tabella AS alias_a
JOIN   tabella AS alias_b ON alias_a.foreign_key = alias_b.id;

Bí danh là bắt buộc: nếu chúng ta bỏ qua AS a / AS b thì PostgreSQL không có cách để biết chúng tôi muốn nói đến bản sao nào trong hai bản sao khi chúng tôi viết name hoặc id.

-- Ogni sotto-categoria con il nome della categoria padre:
SELECT child.name  AS subcategory,
       parent.name AS category
FROM   categories AS child
JOIN   categories AS parent ON child.parent_id = parent.id;

Đọc bằng tiếng Anh đơn giản: "lấy các hàng categories có tên child và với mỗi người, hãy tìm hàng categories có tên cha mẹ có id là parent_id của đứa trẻ".

Kết quả loại trừ các danh mục gốc (những danh mục có parent_id = NULL) vì INNER JOIN bỏ qua các hàng không khớp. Nếu bạn muốn bao gồm chúng, hãy sử dụng LEFT JOIN.

Tự mình tham gia cùng LEFT để “giữ gốc”

-- Tutte le categorie, con nome del padre o NULL se sono radice:
SELECT child.name AS category,
       parent.name AS parent
FROM   categories AS child
LEFT JOIN categories AS parent ON child.parent_id = parent.id;

Bây giờ bạn nhận được 9 hàng (mỗi danh mục trong tập dữ liệu), với parent = NULL cho 3 rễ.

Tổng hợp với tính năng tự tham gia

Nó kết hợp rất tốt với GROUP BY để hỏi "mỗi người có bao nhiêu đứa trẻ cha mẹ có":

SELECT p.name AS parent_category,
       COUNT(c.id) AS children
FROM   categories AS p
LEFT JOIN categories AS c ON c.parent_id = p.id
WHERE  p.parent_id IS NULL          -- solo le radici
GROUP BY p.name
ORDER BY p.name;

Lưu ý hai chi tiết quan trọng:

1. WHERE p.parent_id IS NULL lọc trước THAM GIA: chúng tôi chỉ lấy gốc là "bảng bên trái".
2. COUNT(c.id) (không phải COUNT(*)) trả về 0 cho các gốc không có con, trong khi COUNT(*) sẽ trả về 1 vì hàng LEFT JOIN có KHÔNG.

Hãy tự mình thử

Xem lại bài tập