JOIN, Subquery, CTE & Window Functions

1. JOIN bắt nguồn từ Cartesian Product

Mỗi loại JOIN chỉ là tích Đề-các + filter. Hiểu được Cartesian, mọi JOIN khác trở nên hiển nhiên.

Hai bảng users và orders sẽ là ví dụ chạy xuyên cả chương.

2. INNER JOIN — chỉ giữ phần giao

-- Cú pháp đầy đủ:
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- INNER là MẶC ĐỊNH, có thể bỏ:
SELECT u.name, o.total
FROM users u JOIN orders o ON u.id = o.user_id;

-- Cú pháp cũ (1992) — KHÔNG khuyến nghị:
SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id;

INNER JOIN chỉ giữ dòng có đối ứng ở cả 2 bảng. Trong ví dụ trên, chỉ trả 3 dòng (user 3 không có order, order 4 không có user — bị loại).

2.1. JOIN với điều kiện phức tạp

-- Nhiều điều kiện ON:
SELECT u.name, o.total
FROM users u
JOIN orders o
  ON u.id = o.user_id
 AND o.status = 'paid'
 AND o.created_at > NOW() - INTERVAL '30 days';

-- Khác gì với WHERE? Với INNER JOIN: KHÔNG khác.
-- Nhưng với OUTER JOIN: khác lớn (xem phần sau).

2.2. JOIN trên nhiều cột

-- Khi PK composite hoặc match đa điều kiện:
SELECT *
FROM order_items oi
JOIN inventory i
  ON i.product_id = oi.product_id
 AND i.warehouse_id = oi.warehouse_id;

-- USING (cú pháp ngắn nếu cột cùng tên):
SELECT * FROM order_items JOIN inventory USING (product_id, warehouse_id);

-- NATURAL JOIN — JOIN trên TẤT CẢ cột cùng tên (CHỚ DÙNG, dễ sai):
SELECT * FROM order_items NATURAL JOIN inventory;

NATURAL JOIN nguy hiểm vì nếu thêm cột chung mới, JOIN tự động đổi nghĩa. Tránh.

3. OUTER JOIN — giữ cả phần lệch

3.1. LEFT JOIN

Giữ mọi dòng bên trái, ngay cả khi không có match bên phải. Dòng mất khớp → cột phải = NULL.

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Output:
-- Anh    | 100
-- Anh    | 50
-- Bình   | 200
-- Cường  | NULL    ← user 3 không có order, vẫn xuất hiện

3.2. RIGHT JOIN

Đối xứng với LEFT, giữ mọi dòng bên phải. Trong thực tế ít dùng — chỉ cần đảo thứ tự bảng và dùng LEFT.

-- Hai cái sau tương đương:
SELECT * FROM A LEFT  JOIN B ON ...;
SELECT * FROM B RIGHT JOIN A ON ...;

3.3. FULL OUTER JOIN

Giữ mọi dòng cả hai bên. Dòng nào không match → cột phía kia = NULL.

SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- Output:
-- Anh    | 100
-- Anh    | 50
-- Bình   | 200
-- Cường  | NULL   ← user không order
-- NULL   | 30     ← order mồ côi (user_id = 9)

FULL JOIN hữu ích khi bạn cần phát hiện cả 2 phía mất đồng bộ (data audit, reconciliation).

3.4. ON vs WHERE trong OUTER JOIN — khác biệt LỚN

-- A. Điều kiện ở ON:
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid';
-- Mọi user đều xuất hiện. Order chưa paid → o.total = NULL

-- B. Điều kiện ở WHERE:
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid';
-- WHERE filter SAU JOIN → loại user không có order paid
-- Hệ quả: trở thành INNER JOIN!

3.5. Anti-join — "phía bên trái KHÔNG có match"

-- Lấy user CHƯA bao giờ đặt order:
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- Cú pháp tương đương dùng NOT EXISTS:
SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- HOẶC NOT IN (cẩn thận với NULL!):
SELECT u.* FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

4. CROSS JOIN — Cartesian Product thuần

Mọi tổ hợp giữa 2 bảng, không cần ON.

-- Tạo lịch: mọi (date, user):
SELECT u.id, d.date
FROM users u
CROSS JOIN generate_series('2026-01-01'::date, '2026-12-31'::date, '1 day'::interval) AS d(date);

-- Hữu ích cho:
-- - Báo cáo "0 row" (lấp đầy ngày không có order)
-- - Combinatorial testing
-- - Tạo lookup matrix

-- Cú pháp ngầm (cẩn thận):
SELECT * FROM A, B;  -- = CROSS JOIN nếu không có WHERE

5. Self-join — JOIN bảng với chính nó

Bảng có quan hệ "tự tham chiếu" — cây phân cấp, network.

CREATE TABLE employees (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100),
  manager_id BIGINT REFERENCES employees(id)
);

-- Lấy mỗi nhân viên + tên manager:
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Bậc quản lý 2 cấp:
SELECT e.name AS emp, m.name AS mgr, mm.name AS mgr_mgr
FROM employees e
LEFT JOIN employees m  ON e.manager_id = m.id
LEFT JOIN employees mm ON m.manager_id = mm.id;

Cây sâu > 2-3 cấp → dùng Recursive CTE (mục 9).

6. JOIN nhiều bảng

-- 4-table query: order detail đầy đủ
SELECT
  u.name              AS customer,
  o.id                AS order_id,
  oi.qty,
  p.name              AS product,
  c.name              AS category,
  oi.qty * p.price    AS line_total
FROM users u
JOIN orders o       ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p     ON oi.product_id = p.id
JOIN categories c   ON p.category_id = c.id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC;

JOIN có thứ tự (associativity) ảnh hưởng cách planner sẽ chọn thứ tự thực thi nhưng không ảnh hưởng kết quả với INNER. Với LEFT/RIGHT thì thứ tự CÓ ảnh hưởng — luôn JOIN từ "core table" sang "phụ".

7. Subquery — câu lệnh SELECT lồng nhau

7.1. Scalar subquery (trả 1 giá trị)

SELECT
  email,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

7.2. Subquery trong WHERE — IN, NOT IN

-- User đã từng đặt order:
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

-- User chưa bao giờ đặt:
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
-- ⚠ NOT IN với NULL trong subquery → trả 0 dòng. Luôn lọc NULL.

7.3. EXISTS — bán kết hợp (semi-join)

-- "User có ÍT NHẤT 1 order paid":
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.status = 'paid'
);

-- EXISTS chỉ check tồn tại — KHÔNG cần SELECT cột nào.
-- DB có thể short-circuit khi tìm thấy 1 dòng.

EXISTS vs IN: Trong Postgres hiện đại, planner đủ thông minh để tối ưu cả hai. Theo cảm tính: dùng EXISTS với NULL để tránh bug, dùng IN với danh sách hằng để rõ.

7.4. Correlated subquery — phụ thuộc dòng ngoài

-- Lấy mỗi user và đơn hàng MỚI NHẤT của họ:
SELECT u.email, (
  SELECT MAX(created_at)
  FROM orders o
  WHERE o.user_id = u.id
) AS last_order_at
FROM users u;

Subquery chạy lại cho từng dòng bảng ngoài → có thể chậm trên bảng lớn. Window function (mục 10) thường nhanh hơn.

7.5. Subquery trong FROM (derived table)

SELECT category, AVG(top10_price) AS avg_top10
FROM (
  SELECT category, price
  FROM products
  ORDER BY price DESC
  LIMIT 10
) top10
GROUP BY category;

-- CTE thường rõ hơn (xem mục 8).

7.6. ANY / ALL

-- Lương cao hơn ÍT NHẤT 1 manager:
SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE role = 'manager');

-- Lương cao hơn TẤT CẢ junior:
SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE role = 'junior');

8. CTE — Common Table Expression (WITH)

CTE là "biến tạm" cho subquery. Đặt tên cho 1 truy vấn rồi dùng nó như bảng. Dễ đọc hơn nhiều subquery lồng.

-- Trước (subquery khó đọc):
SELECT *
FROM (
  SELECT user_id, COUNT(*) AS oc
  FROM orders
  WHERE status = 'paid'
  GROUP BY user_id
) AS uc
WHERE oc > 5;

-- Sau (CTE rõ ràng):
WITH paid_counts AS (
  SELECT user_id, COUNT(*) AS oc
  FROM orders
  WHERE status = 'paid'
  GROUP BY user_id
)
SELECT *
FROM paid_counts
WHERE oc > 5;

8.1. Nhiều CTE cùng câu

WITH
  active_users AS (
    SELECT * FROM users WHERE deleted_at IS NULL
  ),
  recent_orders AS (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30d'
  ),
  user_revenue AS (
    SELECT u.id, u.email, COALESCE(SUM(o.total), 0) AS revenue
    FROM active_users u
    LEFT JOIN recent_orders o ON o.user_id = u.id
    GROUP BY u.id, u.email
  )
SELECT * FROM user_revenue ORDER BY revenue DESC LIMIT 10;

8.2. CTE với DML (Postgres)

-- "Move data" trong 1 transaction:
WITH archived AS (
  DELETE FROM orders WHERE created_at < NOW() - INTERVAL '5 years'
  RETURNING *
)
INSERT INTO orders_archive SELECT * FROM archived;

Đây là pattern data lifecycle — di chuyển dữ liệu cũ sang archive table mà vẫn đảm bảo atomic.

9. Recursive CTE — duyệt cây/đồ thị

Cú pháp: WITH RECURSIVE name AS (anchor UNION ALL recursive_step) SELECT ...

-- Bảng employees với manager_id (cây phân cấp)
-- Lấy toàn bộ "team" của manager có id = 5

WITH RECURSIVE team AS (
  -- Anchor: manager bắt đầu
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE id = 5

  UNION ALL

  -- Recursive: lấy nhân viên có manager_id thuộc team
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN team t ON e.manager_id = t.id
)
SELECT * FROM team ORDER BY depth, name;

9.1. Phát sinh dãy

-- Postgres: dùng generate_series
SELECT generate_series(1, 10);

-- Recursive CTE generic (chuẩn SQL):
WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

9.2. Ngăn vô hạn — UNION (DISTINCT)

Nếu đồ thị có chu trình, UNION ALL có thể loop vô hạn. Dùng UNION (DISTINCT) hoặc thêm điều kiện dừng (depth < N).

WITH RECURSIVE traversal AS (
  SELECT id, name, ARRAY[id] AS path FROM nodes WHERE id = 1
  UNION ALL
  SELECT n.id, n.name, t.path || n.id
  FROM nodes n
  JOIN edges e ON e.from_id = n.id
  JOIN traversal t ON t.id = e.to_id
  WHERE NOT n.id = ANY(t.path)   -- ngăn revisit
    AND array_length(t.path, 1) < 20  -- giới hạn depth
)
SELECT * FROM traversal;

10. Window Functions — superpower của SQL

Window function tính giá trị aggregate trên một "cửa sổ" của các dòng quanh dòng hiện tại, mà KHÔNG gom nhóm dòng lại. Đây là điểm khác biệt lớn so với GROUP BY.

10.1. Cú pháp tổng quát

function() OVER (
  [PARTITION BY cols]   -- chia thành các cửa sổ
  [ORDER BY cols]       -- thứ tự trong cửa sổ
  [ROWS|RANGE BETWEEN ... AND ...]  -- frame
)

10.2. Ranking functions

SELECT
  category, name, price,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn,
  RANK()       OVER (PARTITION BY category ORDER BY price DESC) AS rk,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS drk
FROM products;

-- ROW_NUMBER: 1, 2, 3, 4, 5 (luôn unique)
-- RANK:       1, 2, 2, 4, 5 (skip sau ngang bằng)
-- DENSE_RANK: 1, 2, 2, 3, 4 (không skip)

10.3. NTILE — chia bucket

-- Chia user thành 4 quartile theo total spent:
SELECT
  user_id, total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM user_revenue;

10.4. LAG / LEAD — dòng trước/sau

-- So sánh order với order TRƯỚC đó của cùng user:
SELECT
  user_id, created_at, total,
  LAG(total)  OVER (PARTITION BY user_id ORDER BY created_at) AS prev_total,
  LEAD(total) OVER (PARTITION BY user_id ORDER BY created_at) AS next_total,
  total - LAG(total) OVER (PARTITION BY user_id ORDER BY created_at) AS delta
FROM orders;

10.5. Aggregate windows — running total / moving avg

-- Running total (tổng tích lũy):
SELECT
  created_at, total,
  SUM(total) OVER (ORDER BY created_at) AS running_total,
  AVG(total) OVER (
    ORDER BY created_at
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_avg
FROM orders;

10.6. FIRST_VALUE / LAST_VALUE / NTH_VALUE

SELECT
  category, name, price,
  FIRST_VALUE(name) OVER (PARTITION BY category ORDER BY price DESC) AS top_in_cat,
  LAST_VALUE(name)  OVER (
    PARTITION BY category ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS cheapest_in_cat
FROM products;

10.7. Named window — tránh lặp

SELECT
  user_id, created_at, total,
  ROW_NUMBER() OVER w AS rn,
  LAG(total)   OVER w AS prev,
  SUM(total)   OVER w AS running
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY created_at);

11. Patterns kinh điển

11.1. Top-N per group

-- "Top 3 sản phẩm đắt nhất theo từng category"
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
)
SELECT * FROM ranked WHERE rn <= 3;

-- Cách khác: LATERAL (Postgres):
SELECT c.name, p.*
FROM categories c,
LATERAL (
  SELECT * FROM products
  WHERE category_id = c.id
  ORDER BY price DESC LIMIT 3
) p;

11.2. Latest record per group

-- Order mới nhất của mỗi user:
SELECT DISTINCT ON (user_id) *      -- Postgres-only, very fast
FROM orders
ORDER BY user_id, created_at DESC;

-- Cú pháp chuẩn (window):
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

11.3. Running total theo tháng

SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(total) AS month_total,
  SUM(SUM(total)) OVER (ORDER BY DATE_TRUNC('month', created_at)) AS cumulative
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

11.4. Gaps & Islands — phân đoạn

-- "Tìm chuỗi ngày liên tiếp user login":
WITH grouped AS (
  SELECT
    user_id, login_date,
    login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp
  FROM logins
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end,
       COUNT(*) AS streak_len
FROM grouped
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

11.5. Pivot — chuyển dòng thành cột

-- Đếm order theo status, mỗi status 1 cột:
SELECT
  user_id,
  COUNT(*) FILTER (WHERE status = 'paid')      AS paid_count,
  COUNT(*) FILTER (WHERE status = 'pending')   AS pending_count,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_count
FROM orders
GROUP BY user_id;

-- Ngắn dùng crosstab() (Postgres extension tablefunc).

12. Bài tập

Schema dùng cho mọi bài:

users(id, email, name, country, created_at)
products(id, name, category_id, price, stock)
categories(id, name)
orders(id, user_id, total, status, created_at)
order_items(order_id, product_id, qty)

1. Top 5 country có nhiều order nhất tháng này.
2. User chi tiêu nhiều nhất từng tháng (top 1 mỗi tháng).
3. Sản phẩm chưa từng được order.
4. Đơn hàng đầu tiên và cuối cùng của mỗi user (LAG/MIN/MAX OVER).
5. Phần trăm order paid theo tháng (so với tổng order tháng đó).
6. Streak ngày liên tiếp có order > 0 (gaps & islands).
7. Cây danh mục: cho categories(id, name, parent_id), lấy tất cả con cháu của category id = 5 (recursive CTE).
8. Pivot: bảng (user_id, paid, pending, cancelled) với count mỗi status.

13. Quiz