Self join trong SQLite: truy vấn một bảng với chính nó

Self join chỉ là một phép join có alias

Self join không có gì đặc biệt. Đó là một JOIN bình thường mà hai vế tình cờ là cùng một bảng. Mấu chốt là SQLite cần cách phân biệt hai bản sao đó, nên bạn đặt cho mỗi bản sao một alias.

Bạn sẽ cần đến self join mỗi khi một hàng trong bảng tham chiếu đến một hàng khác trong cùng bảng. Trường hợp kinh điển: bảng employees, trong đó mỗi hàng có cột manager_id trỏ đến một nhân viên khác:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT * FROM employees;

Ada không có quản lý. Boris và Cleo báo cáo cho Ada. Diego và Esme báo cáo cho Boris. Toàn bộ quan hệ nằm gọn trong một bảng, và đó chính là chỗ self join phát huy tác dụng.

Dạng cơ bản

Để ghép mỗi nhân viên với tên quản lý của họ, hãy join employees với chính nó. Một bản sao đóng vai "nhân viên", bản còn lại đóng vai "quản lý":

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.id;

Hãy đọc truy vấn này như hai bảng tình cờ dùng chung nơi lưu trữ. e là hàng nhân viên; m là hàng quản lý. Điều kiện join e.manager_id = m.id xếp chúng thẳng hàng: với mỗi nhân viên, tìm hàng trong m có id trùng với manager_id của nhân viên đó.

Để ý rằng Ada không xuất hiện. manager_id của Ada là NULL, và INNER JOIN bỏ đi các hàng không khớp.

Giữ lại các hàng không khớp: LEFT JOIN

Nếu bạn muốn mọi người đều có trong kết quả, kể cả những người không có quản lý, hãy đổi sang LEFT JOIN:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id
ORDER BY e.id;

Giờ Ada xuất hiện với NULL ở cột quản lý. Cơ chế self join vẫn vậy, chỉ là kiểu join làm đúng việc LEFT JOIN luôn làm: giữ mọi hàng ở vế trái, để trống ở chỗ không có hàng khớp.

Đây là dạng bạn thường cần khi hiển thị danh sách người. "Không có quản lý" cũng là thông tin; bỏ mất hàng đó thì không.

Alias là bắt buộc

Thử join mà không có alias, SQLite sẽ không hiểu bạn muốn gì:

SELECT name, manager_id FROM employees JOIN employees ON manager_id = id;
-- Error: ambiguous column name: name

Mỗi cột xuất hiện hai lần, mỗi lần từ một bản sao của bảng, và SQLite không thể chọn. Alias giải quyết chuyện này bằng cách đặt cho mỗi bản sao một tên riêng. Hãy chọn alias mô tả vai trò của hàng, không phải tên bảng:

• e và m cho nhân viên/quản lý.
• parent và child cho cấu trúc phân cấp.
• a và b khi bạn so sánh các cặp bất kỳ.

Alias chính là lý do khiến một self join dễ đọc.

Tìm các cặp trong cùng một bảng

Self join không chỉ dành cho cấu trúc phân cấp. Bất cứ khi nào bạn muốn so sánh các hàng trong cùng một bảng, mẫu này đều dùng được. Dưới đây là danh sách sản phẩm, và ta muốn tìm mọi cặp có cùng giá:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Mug',     9.99),
    ('Notebook', 9.99),
    ('Pen',     2.50),
    ('Pencil',  2.50),
    ('Lamp',   29.00);

SELECT a.name AS product_a, b.name AS product_b, a.price
FROM products AS a
INNER JOIN products AS b
    ON a.price = b.price
   AND a.id < b.id;

Có hai điều cần chú ý. Thứ nhất, a.price = b.price là điều kiện ghép thật sự. Thứ hai, a.id < b.id ngăn truy vấn trả về mỗi cặp hai lần (một lần là (Mug, Notebook), lần nữa là (Notebook, Mug)) và ngăn ghép mỗi hàng với chính nó. Mẹo dùng < này rất đáng nhớ: nó xuất hiện bất cứ khi nào bạn liệt kê các cặp.

Đi lên hai cấp

Một self join xử lý được một bước trong cấu trúc phân cấp. Muốn biết quản lý của quản lý mỗi nhân viên? Hãy join ba lần:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name      AS employee,
    m.name      AS manager,
    gm.name     AS grand_manager
FROM employees AS e
LEFT JOIN employees AS m  ON e.manager_id = m.id
LEFT JOIN employees AS gm ON m.manager_id = gm.id
ORDER BY e.id;

Mỗi alias mới đại diện cho một cấp cao hơn trên cây. Cách này ổn với hai hoặc ba bước, nhưng nhanh chóng bế tắc: bạn phải biết độ sâu của cấu trúc phân cấp ngay khi viết truy vấn, và thêm một phép join cho mỗi cấp. Đó chính là bức tường mà CTE đệ quy được tạo ra để phá vỡ.

Khi nào không nên dùng self join

Self join là công cụ đúng khi bạn cần cột từ cả hai phía của quan hệ trong kết quả. Nếu bạn chỉ cần lọc, chẳng hạn tìm mọi nhân viên có quản lý là Ada, thì subquery thường dễ đọc hơn:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT name
FROM employees
WHERE manager_id = (SELECT id FROM employees WHERE name = 'Ada');

Không cần xoay xở với alias, và ý định rõ ràng không thể nhầm. Quy tắc ngón tay cái: bạn muốn dữ liệu từ cả hai hàng trong đầu ra? Dùng self join. Bạn chỉ cần một giá trị để so sánh? Dùng subquery.

Với cấu trúc phân cấp có độ sâu bất kỳ (sơ đồ tổ chức, cây thư mục, bình luận theo luồng), cả hai mẫu đều không mở rộng được. Đó là lãnh địa của CTE đệ quy.

Tiếp theo: subquery

Self join và subquery giải quyết những bài toán chồng lấn nhau, và biết cái nào phù hợp sẽ giúp bạn đỡ phải nheo mắt nhìn SQL về sau. Trang tiếp theo đi sâu vào subquery: các dạng vô hướng, tương quan và IN, cùng chỗ mạnh của từng dạng.