Subquery, CTE, LATERAL — 3 cách viết cùng một logic

TL;DR: Cùng một logic, ba cách viết với ngữ nghĩa khác nhau. Subquery (scalar / IN / EXISTS) là query inline — dùng khi logic đơn giản, không cần reuse. CTE (WITH name AS (...)) đặt tên cho logic phức tạp và cho phép reference nhiều lần — đọc dễ hơn subquery lồng nhau. LATERAL (chuẩn SQL:1999, đặt trong FROM) cho phép subquery tham chiếu column của bảng đứng trước — pattern tốt nhất cho "top N per group". Cả ba đều là cú pháp ANSI, nhưng mức hỗ trợ và cách optimizer xử lý — đặc biệt việc CTE có materialize hay inline — khác nhau giữa các RDBMS. Chương này nói nguyên lý, ghi chú dialect khi cần.

Cùng một logic truy vấn, bạn có thể viết theo 3 cách khác nhau — subquery inline, CTE có tên, hoặc LATERAL. Cả 3 cho ra kết quả giống nhau. Nhưng chúng khác nhau về readability, optimizer behavior, và reuse ability.

Đây là nơi nhiều developer dừng lại ở "nó chạy được là được". Bài này đẩy bạn thêm một bước: hiểu tại sao mỗi pattern tồn tại, khi nào planner xử lý chúng khác nhau, và làm thế nào để chọn đúng từ đầu thay vì thử mọi cách rồi đoán.

1. Analogy — 3 cách chuẩn bị nguyên liệu khi nấu ăn

Hãy hình dung bạn đang nấu một bữa với nhiều món, và cần lấy nguyên liệu ở các thời điểm khác nhau.

2. Subquery — scalar, correlated, IN, EXISTS

Subquery là SELECT nằm bên trong một câu SQL khác. Có 4 dạng dùng phổ biến:

-- Scalar subquery: tra ve dung 1 column 1 row, dung trong SELECT clause
SELECT
  u.name,
  (SELECT count(*) FROM tasks t WHERE t.assignee_id = u.id) AS task_count
FROM users u;

-- Correlated subquery trong WHERE voi EXISTS
-- EXISTS tra ve TRUE neu subquery tra ve bat ky row nao
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM tasks t
  WHERE t.assignee_id = u.id AND t.status = 'doing'
);

-- IN: filter membership -- project_id phai nam trong tap ket qua
SELECT * FROM tasks WHERE project_id IN (
  SELECT id FROM projects WHERE owner_id = 5
);

-- NOT EXISTS: filter exclusion -- user chua co task nao
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM tasks t WHERE t.assignee_id = u.id
);

Planner thường rewrite thành JOIN: optimizer của RDBMS hiện đại đủ thông minh — IN và EXISTS subquery thường được unnest thành semi-join, correlated subquery thường được rewrite thành hash join hoặc merge join. Xem query plan (mỗi engine có công cụ riêng) thường thấy plan giống nhau dù viết khác nhau. Mức độ rewrite này khác nhau giữa các engine, nhưng nguyên lý "viết IN/EXISTS hay JOIN cho cùng kết quả, planner tự chọn cách thực thi" là phổ quát.

-- Hai cach viet nay thuong co cung plan sau khi planner rewrite:
-- Cach 1: IN subquery
SELECT * FROM tasks WHERE project_id IN (SELECT id FROM projects WHERE owner_id = 5);

-- Cach 2: JOIN
SELECT t.* FROM tasks t JOIN projects p ON t.project_id = p.id WHERE p.owner_id = 5;

-- Xem plan ca hai -> thuong thay Hash Semi Join hoac Merge Semi Join
-- Planner tu quyet dinh; khong can manual rewrite de "giup" planner

3. CTE — WITH name AS (...)

CTE (Common Table Expression) là subquery có tên, định nghĩa trước phần SELECT chính. Sức mạnh cốt lõi: đặt tên cho logic phức tạp và reference cùng tên nhiều lần.

-- CTE co ban: dat ten cho filter logic
WITH active_projects AS (
  SELECT id, name, owner_id
  FROM projects
  WHERE archived_at IS NULL
)
SELECT ap.name, count(t.id) AS task_count
FROM active_projects ap
LEFT JOIN tasks t ON t.project_id = ap.id
GROUP BY ap.id, ap.name;

Reuse cùng CTE nhiều lần — đây là lý do chính để chọn CTE thay subquery inline:

-- Reuse recent_tasks 2 lan trong cung query
WITH recent_tasks AS (
  SELECT * FROM tasks WHERE created_at > now() - interval '7 days'
)
SELECT 'created' AS metric, count(*) FROM recent_tasks
UNION ALL
SELECT 'completed', count(*) FROM recent_tasks WHERE status = 'done';
-- Khong can viet lai filter "created_at > now() - interval '7 days'" 2 lan

CTE chain — CTE sau có thể reference CTE trước:

WITH
active_projects AS (
  SELECT id, name FROM projects WHERE archived_at IS NULL
),
project_task_counts AS (
  SELECT ap.id, ap.name, count(t.id) AS task_count
  FROM active_projects ap
  LEFT JOIN tasks t ON t.project_id = ap.id
  GROUP BY ap.id, ap.name
)
SELECT * FROM project_task_counts WHERE task_count > 10;

4. CTE materialize vs inline — thay đổi quan trọng cần biết

Đây là điểm dễ gây bug hiệu năng ngầm nhất với CTE, và nó khác nhau tuỳ engine. Có hai chiến lược optimizer xử lý một CTE:

Materialize: engine tính toàn bộ CTE xong, lưu kết quả vào buffer tạm, rồi mới dùng. Khi đó CTE trở thành optimization barrier — planner không thể đẩy predicate từ query ngoài vào bên trong CTE.

Inline: engine treat CTE như một subquery — có thể đẩy predicate vào bên trong, chọn index phù hợp, optimize aggressive như subquery thường.

Đây là khác biệt dialect quan trọng:

• Phần lớn RDBMS (vd MySQL 8+, SQL Server, Oracle) inline CTE theo mặc định từ lâu.
• Một số engine từng materialize CTE mặc định (tạo optimization barrier) — code chạy nhanh ở nơi inline có thể chậm bất ngờ ở nơi materialize, vì planner bị chặn không push được predicate.

Một số engine cho phép ép chiến lược bằng hint MATERIALIZED / NOT MATERIALIZED (đây là extension, không phải chuẩn SQL — kiểm tra engine của bạn):

-- Inline (treat nhu subquery): planner co the push WHERE assignee_id=5
-- vao ben trong CTE -> Index Scan tren (assignee_id)
WITH active_tasks AS (
  SELECT * FROM tasks WHERE status != 'archived'
)
SELECT * FROM active_tasks WHERE assignee_id = 5;

-- Ep MATERIALIZED (neu engine ho tro hint): tinh 1 lan, cache, reuse nhieu lan
-- Dung khi: CTE dat + reference nhieu lan + KHONG muon planner re-compute
WITH expensive_calc AS MATERIALIZED (
  SELECT project_id, sum(estimated_hours) AS total_hours
  FROM tasks
  WHERE status != 'archived'
  GROUP BY project_id  -- query nang, nen tinh 1 lan
)
SELECT * FROM expensive_calc WHERE total_hours > 100
UNION ALL
SELECT * FROM expensive_calc WHERE total_hours < 10;
-- MATERIALIZED dam bao tinh 1 lan du reference 2 lan

-- Ep NOT MATERIALIZED: explicit inline -- document intent ro rang
WITH cheap_filter AS NOT MATERIALIZED (
  SELECT * FROM tasks WHERE status != 'archived'
)
SELECT * FROM cheap_filter WHERE assignee_id = 5;
-- Planner inline -> push WHERE assignee_id=5 -> Index Scan

5. LATERAL — "với mỗi outer row, chạy subquery này"

LATERAL (chuẩn SQL:1999) cho phép subquery trong FROM clause reference column từ bảng đứng trước nó trong cùng FROM. Không có LATERAL, subquery trong FROM phải hoàn toàn độc lập. Một số dialect dùng tên khác cho ý tưởng tương tự: SQL Server có CROSS APPLY / OUTER APPLY; MySQL hỗ trợ LATERAL từ 8.0.14; Oracle có cả LATERAL lẫn CROSS APPLY.

-- Top 3 task moi nhat cua moi project
-- LATERAL: "voi moi project p, chay subquery nay (co dung p.id)"
SELECT p.name AS project_name, t.title, t.created_at
FROM projects p,
     LATERAL (
       SELECT title, created_at
       FROM tasks
       WHERE project_id = p.id      -- reference p.id tu outer
       ORDER BY created_at DESC
       LIMIT 3
     ) t;
-- Chay 1 lan per project row, voi LIMIT 3 ap dung rieng cho tung project

LEFT JOIN LATERAL — giữ project dù không có task:

SELECT p.name, t.title
FROM projects p
LEFT JOIN LATERAL (
  SELECT title FROM tasks
  WHERE project_id = p.id
  ORDER BY created_at DESC LIMIT 3
) t ON true;
-- "ON true": khong co dieu kien join them, chi giu row khi subquery empty
-- LEFT JOIN dam bao project khong co task van xuat hien (title = NULL)

LATERAL chạy theo cơ chế nested loop: với mỗi outer row, chạy inner subquery 1 lần — tương tự cách nested loop join hoạt động. Index trên (project_id, created_at DESC) làm inner subquery rất nhanh — O(log N) per project thay vì scan toàn bảng.

6. So sánh 3 cách viết — flowchart

flowchart TD
    START([Logic query can viet]) --> Q1{Can reuse<br/>logic 2+ lan?}
    Q1 -- Yes --> CTE[CTE<br/>WITH name AS ...]
    Q1 -- No --> Q2{Can reference<br/>outer row per-row?}
    Q2 -- Yes --> LATERAL[LATERAL<br/>chay 1 lan per outer row]
    Q2 -- No --> Q3{Logic don gian,<br/>1 gia tri / membership?}
    Q3 -- Yes --> SUB[Subquery inline<br/>scalar / IN / EXISTS]
    Q3 -- No --> CTE

    CTE --> OUT([Dat ten, doc ro, tai dung duoc])
    LATERAL --> OUT2([Top-N per group,<br/>nested loop + index])
    SUB --> OUT3([Planner rewrite<br/>thanh semi-join])

7. Bảng decision — khi nào dùng pattern nào

8. Top N per group — 3 cách viết cùng logic

"Top 3 task mới nhất per project" là bài toán kinh điển. Đây là 3 cách viết phổ biến:

-- Cach 1: Window function (Module 3 cua khoa: join-aggregation-window)
SELECT * FROM (
  SELECT
    t.*,
    ROW_NUMBER() OVER (
      PARTITION BY project_id ORDER BY created_at DESC
    ) AS rn
  FROM tasks t
) sub
WHERE rn <= 3;
-- Planner: compute ROW_NUMBER tren toan bo tasks truoc khi filter
-- Phu hop khi can ca cot rn cho logic sau (e.g. danh so thu tu)

-- Cach 2: LATERAL (ngan nhat, doc tu nhien nhat)
SELECT p.id AS project_id, t.*
FROM projects p,
     LATERAL (
       SELECT * FROM tasks
       WHERE project_id = p.id
       ORDER BY created_at DESC LIMIT 3
     ) t;
-- Planner: Nested Loop, Index Scan tren (project_id, created_at) per project
-- ~1ms cho 1000 project neu co composite index

-- Cach 3: Correlated subquery (verbose, planner co the khong optimize tot)
SELECT t.* FROM tasks t
WHERE t.id IN (
  SELECT id FROM tasks t2
  WHERE t2.project_id = t.project_id
  ORDER BY created_at DESC LIMIT 3
);
-- CANH BAO: correlated subquery voi LIMIT trong IN khong luon work dung
-- tuy SQL dialect, va planner kho optimize -- tranh dung pattern nay

LATERAL thường thắng về readability và planner behavior cho top-N-per-group: ngắn hơn window function, planner chọn Nested Loop với index trên (project_id, created_at DESC) — O(k log N) với k = số project, thay vì scan toàn bộ bảng.

9. Pitfall — scalar nhiều row, correlated N+1, CTE materialize

-- LOI: subquery co the tra ve nhieu row
SELECT (SELECT id FROM tasks WHERE assignee_id = u.id) FROM users u;
-- ERROR: more than one row returned by a subquery used as an expression

-- Fix 1: them LIMIT 1 (neu chi can 1 gia tri bat ky)
SELECT (SELECT id FROM tasks WHERE assignee_id = u.id LIMIT 1) FROM users u;

-- Fix 2: dung LATERAL neu can nhieu column hoac co ORDER BY
SELECT u.name, t.id AS latest_task_id
FROM users u
LEFT JOIN LATERAL (
  SELECT id FROM tasks WHERE assignee_id = u.id
  ORDER BY created_at DESC LIMIT 1
) t ON true;

-- Co the la N+1 neu planner khong unnest:
SELECT u.name,
       (SELECT count(*) FROM tasks WHERE assignee_id = u.id) AS task_count
FROM users u;

-- Tot hon: explicit GROUP BY join -- planner luon chon HashAggregate
SELECT u.name, coalesce(tc.task_count, 0) AS task_count
FROM users u
LEFT JOIN (
  SELECT assignee_id, count(*) AS task_count
  FROM tasks GROUP BY assignee_id
) tc ON tc.assignee_id = u.id;

10. Applied — TaskFlow dashboard: CTE + LATERAL kết hợp

Bài toán thực tế: dashboard hiển thị tất cả active project, mỗi project kèm tổng số task và 3 task gần đây nhất.

-- Dashboard: active projects + task count + 3 task gan nhat moi project
WITH active_projects AS (
  -- CTE: filter 1 lan, reuse 2 lan (trong LATERAL va outer SELECT)
  SELECT id, name, owner_id
  FROM projects
  WHERE archived_at IS NULL
)
SELECT
  ap.name          AS project_name,
  ap.owner_id,
  recent.task_count,
  recent.recent_titles
FROM active_projects ap,
     LATERAL (
       -- "Voi moi active project, tinh count va lay 3 title gan nhat"
       SELECT
         (SELECT count(*) FROM tasks WHERE project_id = ap.id) AS task_count,
         array_agg(t.title ORDER BY t.created_at DESC)         AS recent_titles
       FROM (
         SELECT title, created_at
         FROM tasks
         WHERE project_id = ap.id
         ORDER BY created_at DESC LIMIT 3
       ) t
     ) recent;

Pattern này kết hợp cả hai điểm mạnh:

• CTE active_projects: filter chung viết 1 lần, đọc rõ ý định; với engine inline CTE thì planner gập vào query ngoài → không tốn chi phí thêm.
• LATERAL: "với mỗi active project, pull top 3 task" — thể hiện quan hệ row-by-row rõ ràng hơn subquery correlated.
• Index đề xuất: (project_id, created_at DESC) trên bảng tasks — cho phép LATERAL inner query dùng Index Scan + LIMIT early termination.

11. Deep Dive

12. Tóm tắt

• Subquery (scalar / correlated / IN / EXISTS) là subquery inline — dùng khi logic đơn giản, không cần reuse, không cần reference outer row. Planner thường rewrite IN / EXISTS thành semi-join.
• Scalar subquery trong SELECT phải trả về đúng 1 column, tối đa 1 row — vi phạm gây runtime error "more than one row returned".
• CTE (WITH name AS (...)) đặt tên cho logic phức tạp, cho phép reuse cùng tên nhiều lần trong query. Đọc dễ hơn subquery lồng nhau.
• Materialize vs inline là khác biệt dialect quan trọng: engine inline CTE thì planner push được predicate (optimize aggressive); engine materialize CTE thì CTE thành optimization barrier (tính trước, không push). Cùng một CTE có thể nhanh ở engine này, chậm ở engine kia.
• Một số engine cho ép chiến lược bằng hint AS MATERIALIZED (tính 1 lần, cache — dùng khi CTE expensive và reference nhiều lần) / AS NOT MATERIALIZED (ép inline). Đây là extension, không phải chuẩn SQL — đừng phụ thuộc khi viết SQL portable.
• LATERAL (SQL:1999) cho phép subquery trong FROM reference column từ bảng đứng trước. Chạy 1 lần per outer row (nested loop). Pattern tốt nhất cho "top N per group" — ngắn hơn window function, planner thường chọn Index Scan với LIMIT early termination. SQL Server dùng CROSS/OUTER APPLY cho ý tưởng tương đương; MySQL hỗ trợ LATERAL từ 8.0.14.
• LEFT JOIN LATERAL ... ON true giữ outer row dù inner subquery không trả row nào.
• Bảng decision: reuse 2+ lần → CTE; top-N per group → LATERAL; filter membership → IN/EXISTS; 1 giá trị đơn → scalar subquery; recursive → WITH RECURSIVE (bài 2 module này).

13. Tự kiểm tra

WITH expensive_cte AS MATERIALIZED (
SELECT ... FROM ... -- query nang
)
SELECT ... FROM expensive_cte  -- reference 1
UNION ALL SELECT ... FROM expensive_cte  -- reference 2
UNION ALL SELECT ... FROM expensive_cte; -- reference 3
-- AS MATERIALIZED: tinh 1 lan, cache, dung cho ca 3 reference

SELECT DISTINCT u.id, u.name
FROM users u
WHERE EXISTS (
SELECT 1 FROM tasks t
JOIN projects p ON t.project_id = p.id
WHERE t.assignee_id = u.id
  AND t.status = 'doing'
  AND p.archived_at IS NULL
);

SELECT u.id, u.name
FROM users u
WHERE u.id IN (
SELECT t.assignee_id FROM tasks t
JOIN projects p ON t.project_id = p.id
WHERE t.status = 'doing' AND p.archived_at IS NULL
);

Bài tiếp theo: Recursive CTE — comment thread, project tree, manager chain