Advanced Joins: Cross Join, Self Join & Lateral Join

Ngoài INNER/LEFT/RIGHT JOIN quen thuộc, SQL còn có các loại JOIN đặc biệt giúp giải quyết những bài toán phức tạp. Bài này sẽ cover 3 loại ít được biết đến nhưng cực kỳ mạnh.

---

1️⃣ Self Join: Bảng tự JOIN với chính nó

Concept

Self Join là khi bạn JOIN một bảng với chính nó, sử dụng alias khác nhau. Thường dùng khi dữ liệu có quan hệ tự tham chiếu (self-referential relationship).

SELECT a.column, b.column
FROM table_name a
JOIN table_name b ON a.some_column = b.other_column;

Use Case 1: Tìm nhân viên lương cao hơn quản lý

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(15,2),
    manager_id INT REFERENCES employees(id)
);

INSERT INTO employees (id, name, salary, manager_id) VALUES
    (1, 'CEO Nguyễn', 100000000, NULL),
    (2, 'Manager Trần', 50000000, 1),
    (3, 'Manager Lê', 45000000, 1),
    (4, 'Dev Phạm', 60000000, 2),      -- Lương > Manager!
    (5, 'Dev Hoàng', 40000000, 2),
    (6, 'Dev An', 48000000, 3);         -- Lương > Manager!

-- Tìm nhân viên có lương CAO HƠN quản lý trực tiếp
SELECT 
    e.name AS employee_name,
    e.salary AS employee_salary,
    m.name AS manager_name,
    m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id  -- Self Join
WHERE e.salary > m.salary;

-- Kết quả:
-- employee_name | employee_salary | manager_name  | manager_salary
-- --------------+-----------------+---------------+----------------
-- Dev Phạm      |      60000000   | Manager Trần  |     50000000
-- Dev An        |      48000000   | Manager Lê    |     45000000

Use Case 2: Tìm cặp sản phẩm cùng category

-- Tìm các cặp sản phẩm thuộc cùng category (để recommend)
SELECT 
    p1.name AS product_1,
    p2.name AS product_2,
    p1.category_id
FROM products p1
JOIN products p2 ON p1.category_id = p2.category_id
WHERE p1.id < p2.id;  -- Tránh duplicate pairs và self-pair

Use Case 3: So sánh thay đổi giá theo thời gian

-- Tìm sản phẩm có giá TĂNG so với record trước
SELECT 
    curr.product_id,
    curr.price AS current_price,
    prev.price AS previous_price,
    curr.created_at
FROM price_history curr
JOIN price_history prev ON curr.product_id = prev.product_id
WHERE curr.created_at > prev.created_at
  AND curr.price > prev.price
  AND NOT EXISTS (
      -- Đảm bảo prev là record ngay trước curr
      SELECT 1 FROM price_history mid
      WHERE mid.product_id = curr.product_id
        AND mid.created_at > prev.created_at
        AND mid.created_at < curr.created_at
  );

---

2️⃣ Cross Join: Tích Descartes (Cartesian Product)

Concept

Cross Join kết hợp MỌI dòng của bảng A với MỌI dòng của bảng B. Nếu A có 10 dòng, B có 5 dòng -> Kết quả có 50 dòng.

SELECT * FROM table_a CROSS JOIN table_b;
-- Hoặc cú pháp cũ
SELECT * FROM table_a, table_b;

⚠️ Cẩn thận với số lượng dòng!

Cross Join dễ gây bùng nổ dữ liệu:

• 1,000 x 1,000 = 1,000,000 dòng!
• Chỉ dùng với bảng nhỏ hoặc có mục đích rõ ràng.

Use Case 1: Tạo Matrix (Bảng tổ hợp)

-- Tạo bảng size x color cho sản phẩm
CREATE TABLE sizes (name VARCHAR(10));
CREATE TABLE colors (name VARCHAR(20));

INSERT INTO sizes VALUES ('S'), ('M'), ('L'), ('XL');
INSERT INTO colors VALUES ('Red'), ('Blue'), ('Black');

SELECT 
    s.name AS size,
    c.name AS color,
    CONCAT(s.name, '-', c.name) AS sku_suffix
FROM sizes s
CROSS JOIN colors c
ORDER BY s.name, c.name;

-- Kết quả: 4 x 3 = 12 dòng
-- size | color  | sku_suffix
-- -----+--------+------------
-- L    | Black  | L-Black
-- L    | Blue   | L-Blue
-- L    | Red    | L-Red
-- M    | Black  | M-Black
-- ...

Use Case 2: Tạo Time Series (Lấp đầy missing dates)

-- Tạo bảng doanh thu theo ngày, kể cả ngày không có đơn
WITH date_series AS (
    SELECT generate_series(
        '2024-01-01'::date,
        '2024-01-31'::date,
        '1 day'::interval
    )::date AS date
),
products AS (
    SELECT DISTINCT product_id FROM orders
)
SELECT 
    d.date,
    p.product_id,
    COALESCE(SUM(o.amount), 0) AS daily_revenue
FROM date_series d
CROSS JOIN products p
LEFT JOIN orders o ON o.order_date = d.date AND o.product_id = p.product_id
GROUP BY d.date, p.product_id
ORDER BY d.date, p.product_id;

Use Case 3: Game Logic - Matchmaking

-- Tạo tất cả các cặp đấu có thể trong giải đấu
SELECT 
    p1.name AS player_1,
    p2.name AS player_2
FROM players p1
CROSS JOIN players p2
WHERE p1.id < p2.id;  -- Tránh đấu với chính mình và duplicate

---

3️⃣ Lateral Join: Vòng lặp for-each trong SQL

Concept

Lateral Join cho phép subquery bên phải THAM CHIẾU đến các cột từ bảng bên trái. Nó giống như chạy subquery MỖI DÒNG của bảng trái.

SELECT *
FROM table_a a
JOIN LATERAL (
    -- Subquery này có thể dùng a.column!
    SELECT * FROM table_b WHERE table_b.fk = a.id LIMIT 3
) sub ON true;

Khác biệt với Regular Subquery:

Regular Subquery	Lateral Subquery
Chạy 1 lần, độc lập	Chạy N lần, mỗi lần cho 1 dòng của bảng trái
Không thể tham chiếu bảng trái	Có thể tham chiếu bảng trái
Dùng cho cùng kết quả	Dùng cho kết quả khác nhau mỗi dòng

Use Case 1: Top N per Group (Lấy N item mỗi nhóm)

Bài toán: Với mỗi category, lấy 3 sản phẩm bán chạy nhất.

-- KHÔNG CÓ LATERAL: Phải dùng Window Function phức tạp
SELECT * FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn
    FROM products
) sub
WHERE rn <= 3;

-- VỚI LATERAL: Đọc dễ hơn, giống for-each loop
SELECT 
    c.name AS category_name,
    p.name AS product_name,
    p.sales
FROM categories c
JOIN LATERAL (
    SELECT name, sales
    FROM products
    WHERE products.category_id = c.id  -- Tham chiếu c.id từ bảng trái!
    ORDER BY sales DESC
    LIMIT 3
) p ON true;

Use Case 2: Unnest JSON Array

-- Bảng orders có cột items là JSON array
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer VARCHAR(100),
    items JSONB  -- Ví dụ: [{"sku": "A", "qty": 2}, {"sku": "B", "qty": 1}]
);

-- Trải phẳng JSON array thành từng dòng
SELECT 
    o.id AS order_id,
    o.customer,
    item->>'sku' AS sku,
    (item->>'qty')::int AS quantity
FROM orders o
JOIN LATERAL jsonb_array_elements(o.items) AS item ON true;

-- Kết quả:
-- order_id | customer | sku | quantity
-- ---------+----------+-----+----------
--        1 | Alice    | A   |        2
--        1 | Alice    | B   |        1

Use Case 3: Tìm sự kiện tiếp theo (Next Event)

-- Với mỗi login, tìm logout tiếp theo
SELECT 
    login.user_id,
    login.timestamp AS login_time,
    logout.timestamp AS logout_time,
    logout.timestamp - login.timestamp AS session_duration
FROM events login
JOIN LATERAL (
    SELECT timestamp
    FROM events
    WHERE events.user_id = login.user_id
      AND events.event_type = 'logout'
      AND events.timestamp > login.timestamp
    ORDER BY timestamp
    LIMIT 1  -- Chỉ lấy logout ĐẦU TIÊN sau login
) logout ON true
WHERE login.event_type = 'login';

Use Case 4: Aggregate với điều kiện động

-- Với mỗi user, tính tổng đơn hàng trong 30 ngày gần nhất
SELECT 
    u.id,
    u.name,
    stats.order_count,
    stats.total_amount
FROM users u
JOIN LATERAL (
    SELECT 
        COUNT(*) AS order_count,
        COALESCE(SUM(total_amount), 0) AS total_amount
    FROM orders
    WHERE orders.user_id = u.id
      AND orders.created_at > NOW() - INTERVAL '30 days'
) stats ON true;

LEFT JOIN LATERAL (Giữ dòng không có kết quả)

-- Dùng LEFT để giữ users không có đơn hàng
SELECT 
    u.id,
    u.name,
    COALESCE(stats.order_count, 0) AS order_count
FROM users u
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS order_count
    FROM orders
    WHERE orders.user_id = u.id
) stats ON true;

---

4️⃣ So sánh tổng hợp

Join Type	Kết quả	Use Case
Self Join	Bảng JOIN với chính nó	So sánh các dòng trong cùng bảng
Cross Join	Tích Descartes (M x N)	Tạo matrix, fill missing data
Lateral Join	Subquery chạy mỗi dòng	Top N per group, unnest, next event

Performance Considerations

-- Self Join: Đảm bảo có index trên cột JOIN
CREATE INDEX idx_employees_manager ON employees(manager_id);

-- Cross Join: TUYỆT ĐỐI tránh với bảng lớn
-- Nếu cần, filter trước khi CROSS JOIN

-- Lateral Join: Tương tự subquery, có thể chậm nếu không có index
-- PostgreSQL thường optimize tốt, nhưng kiểm tra EXPLAIN
EXPLAIN ANALYZE
SELECT * FROM categories c
JOIN LATERAL (SELECT * FROM products WHERE category_id = c.id LIMIT 3) p ON true;

---

Tổng kết

💡 HPN Pro Tips

1. Self Join: Luôn dùng alias rõ ràng (e cho employee, m cho manager) để không bị confuse.
2. Cross Join: Hãy tự hỏi "Mình có THỰC SỰ cần tất cả tổ hợp không?" trước khi dùng.
3. Lateral Join: Khi thấy mình viết correlated subquery trong SELECT, hãy xem xét refactor sang LATERAL để dễ đọc hơn.

-- Pattern nhớ nhanh
-- Self: Cùng bảng, khác alias
-- Cross: Mọi cặp, không điều kiện
-- Lateral: Subquery tham chiếu bảng trái