Bài 5: Subquery & CTE

Bài 4 tổng hợp dữ liệu bằng GROUP BY. Nhưng nhiều câu hỏi kinh doanh thật cần 1 câu lệnh SQL tham chiếu tới kết quả của 1 câu lệnh SQL khác — "đơn nào có giá trị lớn hơn trung bình?", "khách nào chưa từng mua Electronics?". Đây là lúc cần subquery (truy vấn con) hoặc CTE (Common Table Expression, khai báo bằng WITH). Bài này cũng có 1 trong những cạm bẫy SQL nổi tiếng nhất — NOT IN kết hợp với subquery chứa NULL — âm thầm trả về sai mà không hề báo lỗi.

1. Subquery Vô Hướng (Scalar Subquery): Trả Về Đúng 1 Giá Trị

Subquery vô hướng là 1 câu SELECT đặt lồng bên trong câu lệnh khác, được đảm bảo trả về đúng 1 hàng, 1 cột (nếu trả nhiều hơn, SQLite báo lỗi runtime). Nó có thể xuất hiện ở bất kỳ đâu 1 giá trị đơn được chấp nhận — trong WHERE, hay ngay trong SELECT.

Tình huống 1 — So sánh với trung bình toàn cục: tìm đơn hàng "trên trung bình".

SELECT order_id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders)
ORDER BY total_amount DESC;

Kết quả có 10/28 đơn vượt mức trung bình ~128.27 đã tính ở Bài 4. Đây chính là điểm khác biệt subquery mang lại so với hardcode số 128.27 trực tiếp: nếu dữ liệu thay đổi (thêm đơn mới), ngưỡng so sánh tự động cập nhật theo, không cần sửa câu lệnh.

Tình huống 2 — Đưa subquery vào ngay trong SELECT: tính % đóng góp của từng đơn so với tổng doanh thu.

SELECT order_id, total_amount,
       ROUND(total_amount * 100.0 / (SELECT SUM(total_amount) FROM orders), 2) AS pct_of_total
FROM orders
ORDER BY total_amount DESC
LIMIT 5;

Đơn giá trị cao nhất (349.99, xuất hiện 2 lần trong dataset) chiếm 9.74% tổng doanh thu. Subquery ở đây chạy đúng 1 lần (không phụ thuộc hàng ngoài), rồi giá trị đó được dùng lại cho mọi hàng — khác hẳn với subquery tương quan sẽ học ở mục 3.

Tình huống 3 — Tìm sản phẩm đắt nhất bằng subquery thay vì ORDER BY LIMIT 1:

SELECT product_name, unit_price
FROM products
WHERE unit_price = (SELECT MAX(unit_price) FROM products);

Kết quả: Standing Desk, 349.99. Cách viết này khác ORDER BY unit_price DESC LIMIT 1 ở 1 điểm quan trọng: nếu có 2 sản phẩm đồng giá cao nhất, subquery WHERE unit_price = (...) trả về cả 2, trong khi LIMIT 1 chỉ trả về 1 (bỏ sót sản phẩm còn lại). Chọn cách nào tuỳ ý định: "top 1 hàng" hay "mọi hàng đạt giá trị cao nhất".

2. Subquery Trong WHERE: IN, EXISTS, và Cạm Bẫy NOT IN

IN (subquery) kiểm tra 1 giá trị có nằm trong tập kết quả subquery hay không. EXISTS (subquery) chỉ kiểm tra subquery có trả về hàng nào không (không quan tâm giá trị cụ thể) — thường hiệu quả hơn với subquery tương quan lớn vì engine có thể dừng ngay khi tìm thấy 1 hàng khớp đầu tiên.

Tình huống 1 — Khách đã từng mua Electronics (IN):

SELECT DISTINCT full_name
FROM customers
WHERE customer_id IN (
  SELECT o.customer_id
  FROM orders o
  INNER JOIN order_items oi ON o.order_id = oi.order_id
  INNER JOIN products p ON oi.product_id = p.product_id
  WHERE p.category = 'Electronics'
);

Kết quả: đúng 7 khách hàng — khớp chính xác với con số "7 khách khác nhau mua Electronics" đã tính bằng COUNT(DISTINCT customer_id) + HAVING ở Bài 4. Đây là 2 cách viết khác nhau cho cùng 1 câu hỏi kinh doanh — subquery IN trả về danh sách tên, còn GROUP BY/HAVING trả về con số đếm.

Tình huống 2 — Sản phẩm chưa từng được bán (NOT EXISTS):

SELECT product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id
);

Với dataset TechMart hiện tại, kết quả là 0 hàng — đúng như đã xác nhận ở Bài 3, mọi sản phẩm đều đã bán được ít nhất 1 lần. Cấu trúc truy vấn vẫn hoàn toàn đúng và cần thiết cho dữ liệu thật (sẽ thay đổi khi có sản phẩm mới).

-- Ý định: tìm khách CHƯA từng đặt đơn nào
SELECT full_name FROM customers c
WHERE c.customer_id NOT IN (SELECT customer_id FROM orders);

3. Subquery Tương Quan (Correlated Subquery): Chạy Lại Cho Từng Hàng Ngoài

Khác với subquery vô hướng ở mục 1 (chạy đúng 1 lần), subquery tương quan tham chiếu tới cột của bảng ngoài — engine phải chạy lại nó cho mỗi hàng của bảng ngoài, giống 1 vòng lặp lồng nhau.

Tình huống 1 — Mỗi khách hàng kèm ngày đặt đơn gần nhất:

SELECT c.full_name,
       (SELECT MAX(o.order_date) FROM orders o WHERE o.customer_id = c.customer_id) AS last_order_date
FROM customers c
ORDER BY last_order_date DESC;

Subquery WHERE o.customer_id = c.customer_id tham chiếu c.customer_id từ bảng ngoài — với 12 khách hàng, subquery chạy 12 lần, mỗi lần lọc theo 1 khách khác nhau. Kết quả: David Kim có đơn gần nhất (2026-03-28), Phạm Quốc Việt có đơn gần nhất xa nhất trong quá khứ (2025-12-12 — khách này chỉ có 1 đơn duy nhất, và đã cancelled, đúng với is_active = 0 đã thấy ở Bài 4).

Tình huống 2 — % doanh thu mỗi sản phẩm đóng góp trong chính danh mục của nó: kết hợp subquery tương quan với subquery trong FROM (mục 4).

SELECT p.product_name, p.category, rev.product_revenue,
       ROUND(rev.product_revenue * 100.0 / (
         SELECT SUM(oi2.quantity * oi2.unit_price)
         FROM order_items oi2
         INNER JOIN products p2 ON oi2.product_id = p2.product_id
         WHERE p2.category = p.category  -- tương quan: tham chiếu p.category ở bảng ngoài
       ), 2) AS pct_of_category
FROM products p
INNER JOIN (
  SELECT product_id, SUM(quantity * unit_price) AS product_revenue
  FROM order_items
  GROUP BY product_id
) rev ON p.product_id = rev.product_id
ORDER BY p.category, pct_of_category DESC;

Kết quả cho thấy Standing Desk chiếm 62.61% doanh thu danh mục Office — 1 sản phẩm gánh phần lớn doanh thu cả danh mục, thông tin cực kỳ hữu ích cho quyết định nhập hàng/marketing mà số liệu doanh thu tổng (Bài 4) không thể hiện được.

4. Subquery Trong FROM (Derived Table)

Query con ở mục 3 vừa dùng 1 derived table — subquery đặt ngay trong FROM, được đối xử như 1 bảng tạm thời chỉ tồn tại trong câu lệnh đó (tương tự kỹ thuật (SELECT DISTINCT category FROM products) đã dùng cho CROSS JOIN ở Bài 3). Đây là công cụ hữu ích khi cần tổng hợp trước rồi mới nối/lọc tiếp — tính product_revenue theo từng sản phẩm trước (bên trong), rồi mới JOIN với products để lấy tên và tính phần trăm (bên ngoài).

5. CTE (WITH): Đặt Tên Cho Subquery, Đọc Dễ Hơn

WITH tên_cte AS (subquery) khai báo 1 hoặc nhiều "bảng tạm có tên" trước câu lệnh chính — về mặt kết quả, hoàn toàn tương đương derived table ở mục 4, nhưng dễ đọc hơn nhiều khi subquery phức tạp hoặc được dùng lại nhiều lần. Viết lại đúng ví dụ ở mục 3 bằng CTE:

WITH product_revenue AS (
  SELECT product_id, SUM(quantity * unit_price) AS revenue
  FROM order_items
  GROUP BY product_id
),
category_revenue AS (
  SELECT p.category, SUM(pr.revenue) AS total_revenue
  FROM product_revenue pr
  INNER JOIN products p ON pr.product_id = p.product_id
  GROUP BY p.category
)
SELECT p.product_name, p.category, pr.revenue,
       ROUND(pr.revenue * 100.0 / cr.total_revenue, 2) AS pct_of_category
FROM product_revenue pr
INNER JOIN products p ON pr.product_id = p.product_id
INNER JOIN category_revenue cr ON p.category = cr.category
ORDER BY p.category, pct_of_category DESC;

Kết quả giống hệt truy vấn ở mục 3, nhưng cấu trúc rõ ràng hơn hẳn: product_revenue tính doanh thu từng sản phẩm, category_revenue tính tổng theo danh mục (tái sử dụng product_revenue thay vì viết lại subquery tương quan lồng nhau), câu lệnh chính chỉ còn việc JOIN và tính phần trăm — không còn subquery tương quan nào cả. Đây chính là điểm CTE mạnh hơn subquery tương quan: tách từng bước tính toán thành 1 khối có tên riêng, đọc theo thứ tự tự nhiên từ trên xuống thay vì phải "đọc từ trong ra ngoài" như subquery lồng nhau.

6. CTE Đệ Quy: Duyệt Cây Phân Cấp

TechMart hiện dùng danh mục phẳng (Electronics, Accessories...), không có cấu trúc cây. Để minh hoạ CTE đệ quy — công cụ SQL duy nhất xử lý được dữ liệu phân cấp có độ sâu bất kỳ — bài học tạo thêm 1 bảng category_tree ngay trong phiên làm việc (chỉ tồn tại cho bài học này, không phải 1 phần cố định của dataset TechMart):

CREATE TABLE category_tree (
  category_id INTEGER PRIMARY KEY,
  category_name TEXT NOT NULL,
  parent_id INTEGER
);
INSERT INTO category_tree VALUES
  (1, 'Electronics', NULL), (2, 'Computers', 1), (3, 'Laptops', 2),
  (4, 'Desktops', 2), (5, 'Audio', 1), (6, 'Headphones', 5),
  (7, 'Office', NULL), (8, 'Furniture', 7), (9, 'Chairs', 8);

parent_id NULL đánh dấu danh mục gốc (Electronics, Office). Mỗi danh mục con trỏ tới category_id của danh mục cha — đúng mô hình adjacency list kinh điển cho dữ liệu phân cấp.

WITH RECURSIVE category_path(category_id, category_name, depth, path) AS (
  -- Anchor: điểm khởi đầu — mọi danh mục GỐC (không có cha)
  SELECT category_id, category_name, 0, category_name
  FROM category_tree
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive: nối tiếp con của những gì category_path ĐÃ có
  SELECT ct.category_id, ct.category_name, cp.depth + 1, cp.path || ' > ' || ct.category_name
  FROM category_tree ct
  INNER JOIN category_path cp ON ct.parent_id = cp.category_id
)
SELECT category_id, category_name, depth, path
FROM category_path
ORDER BY path;

Kết quả liệt kê đủ 9 danh mục kèm đường dẫn đầy đủ, ví dụ Electronics > Audio > Headphones (depth 2). Cơ chế hoạt động:

• Anchor member (phần trước UNION ALL) chạy đúng 1 lần, tạo tập khởi điểm — ở đây là 2 danh mục gốc, depth = 0.
• Recursive member (phần sau UNION ALL) chạy lặp lại, mỗi lần JOIN bảng gốc với kết quả của lần chạy ngay trước đó (không phải toàn bộ category_path tích luỹ, mà chỉ các hàng mới sinh ra ở vòng lặp liền trước) — tìm con của những danh mục vừa được thêm vào.
• Vòng lặp dừng khi 1 lượt chạy recursive member không sinh ra hàng mới nào.

Bảng So Sánh: Subquery vs CTE

Sân chơi tương tác: SQL Workbench Trên Dataset TechMart

Thử ngay ví dụ NOT IN/NOT EXISTS ở mục 2 — chèn thêm 1 khách hàng test rồi chạy lại cả 2 câu để tự mắt thấy cạm bẫy. Ví dụ CTE đệ quy cũng có sẵn trong danh sách tình huống bên dưới (bao gồm cả bước tạo bảng category_tree).

Đang tải...

⬇ Tải file schema + dữ liệu mẫu TechMart (.sql) — chạy được trực tiếp bằng sqlite3 CLI hoặc DB Browser for SQLite trên máy bạn.