SQL từ cơ bản đến nâng cao
Trang chủ / Module 6
🧩 Module 6 / 16 · 5 giờ

Subquery, EXISTS và phép toán tập hợp

Truy vấn lồng nhau (scalar, IN, correlated, derived table), EXISTS/NOT EXISTS và UNION/INTERSECT/EXCEPT — cùng cách chọn dạng viết đúng, nhanh và an toàn với NULL.

SubqueryEXISTSUNIONINTERSECT
📌 Vì sao module này quan trọng

Nhiều câu hỏi nghiệp vụ tự nhiên có dạng lồng: "sản phẩm đắt hơn trung bình của danh mục", "khách có đơn nhưng chưa thanh toán". Subquery và EXISTS là cách diễn đạt tự nhiên nhất, nhưng chứa vài bẫy nguy hiểm (NOT IN + NULL, subquery tương quan chạy từng dòng). Biết chọn đúng dạng vừa cho kết quả đúng vừa cho hiệu năng tốt.

🎯 Mục tiêu học tập

  • Phân biệt scalar subquery, subquery trong IN/ANY/ALL, derived table và correlated subquery
  • Chọn giữa IN, EXISTS, JOIN cho bài toán 'có/không có' và giải thích khác biệt khi gặp NULL
  • Dùng UNION / UNION ALL / INTERSECT / EXCEPT đúng cách
  • Giải bài toán 'chia' (khách mua TẤT CẢ ...) và so sánh với giá trị tổng hợp của nhóm

Bốn dạng subquery

DạngVị tríTrả về
Scalar subquerySELECT/WHERE như một giá trịĐúng 1 dòng, 1 cột (nhiều hơn → lỗi)
Subquery với IN / ANY / ALLWHERE1 cột, nhiều dòng
Derived tableFROM (bắt buộc có alias)Một bảng tạm
Correlated subqueryTham chiếu cột truy vấn ngoàiChạy (logic) lại cho mỗi dòng ngoài
-- Scalar: so với một giá trị tổng hợp
SELECT name, price
FROM products
WHERE price > (SELECT avg(price) FROM products)
ORDER BY price DESC;
Kết quả
          name          |    price
------------------------+-------------
 Laptop Zenbook 16 Pro  | 34900000.00
 Laptop Zenbook 14      | 22500000.00
 Điện thoại Nova Pro    | 18990000.00
 Điện thoại Aurora X1   | 12990000.00
 Laptop Student 15      | 11900000.00
 Robot hút bụi          |  7490000.00
 Điện thoại Aurora Lite |  6490000.00
(7 rows)
-- IN: khách đã có đơn bị huỷ
SELECT id, full_name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'cancelled')
ORDER BY id LIMIT 5;
Kết quả
 id |   full_name
----+----------------
  1 | Trần Quốc Dũng
  3 | Phạm Đức Phúc
  5 | Vũ Gia Huy
  7 | Bùi Thị Bình
  9 | Trần Hải Nam
(5 rows)
-- Derived table: bảng tạm trong FROM — tính trung bình số đơn mỗi khách có đơn
SELECT round(avg(so_don), 2) AS don_tb_moi_khach
FROM (SELECT customer_id, count(*) AS so_don FROM orders GROUP BY customer_id) t;
Kết quả
 don_tb_moi_khach
------------------
             7.41
(1 row)
-- Correlated: sản phẩm đắt hơn mức trung bình CỦA CHÍNH DANH MỤC của nó
SELECT p.name, p.category_id, p.price
FROM products p
WHERE p.price > (SELECT avg(p2.price) FROM products p2 WHERE p2.category_id = p.category_id)
ORDER BY p.category_id, p.price DESC;
Kết quả
          name           | category_id |    price
-------------------------+-------------+-------------
 Robot hút bụi           |           3 |  7490000.00
 Điện thoại Nova Pro     |           4 | 18990000.00
 Điện thoại Aurora X1    |           4 | 12990000.00
 Laptop Zenbook 16 Pro   |           5 | 34900000.00
 Tai nghe không dây Echo |           6 |  1890000.00
 Áo khoác gió            |           7 |   690000.00
 Giày da nam             |           8 |  1590000.00
 Nồi chiên không dầu 5L  |           9 |  2290000.00
(8 rows)
SELECT id, (SELECT customer_id FROM orders WHERE status = 'paid') AS lỗi FROM customers LIMIT 1;
Lỗi PostgreSQL trả về
ERROR:  more than one row returned by a subquery used as an expression
❌ Lỗi thường gặp — Scalar subquery trả về nhiều hơn một dòng

Lỗi "more than one row returned by a subquery used as an expression" thường xuất hiện sau này khi dữ liệu thay đổi (hôm nay 1 dòng, mai 2 dòng) — một quả bom hẹn giờ. Nếu thật sự cần 1 giá trị hãy làm cho điều đó được đảm bảo: lọc theo khoá duy nhất, hoặc ORDER BY ... LIMIT 1, hoặc hàm tổng hợp.

❌ Lỗi thường gặp — Subquery tương quan trong SELECT cho bảng lớn

SELECT o.*, (SELECT count(*) FROM order_items WHERE order_id = o.id) FROM orders o về mặt logic chạy một subquery cho mỗi đơn. Với index đúng thì chấp nhận được; không có index → N lần quét toàn bảng. Thường nên viết lại thành LEFT JOIN với bảng đã GROUP BY trước, hoặc window function.

EXISTS, IN và NOT IN — và cái bẫy NULL

EXISTS (subquery) chỉ hỏi "có ít nhất một dòng không?" — dừng ngay khi tìm thấy dòng đầu tiên, nội dung SELECT bên trong không quan trọng (thường viết SELECT 1), và không bị ảnh hưởng bởi NULL.

-- Khách có ít nhất một đơn đã giao
SELECT count(*) AS so_khach
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'delivered');
Kết quả
 so_khach
----------
       54
(1 row)
-- Khách CHƯA từng đặt đơn
SELECT id, full_name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
ORDER BY id;
Kết quả
 id |     full_name
----+-------------------
 55 | Bùi Gia Huy
 56 | Nguyễn Khánh Linh
 57 | Trần Thị Bình
 58 | Lê Thu Hà
 59 | Phạm Hải Nam
 60 | Hoàng Văn An
(6 rows)

Giờ xem cái bẫy NOT IN trên dữ liệu thật: referred_by có NULL (đa số khách không do ai giới thiệu). "Khách nào chưa từng giới thiệu ai?"

-- SAI âm thầm: subquery chứa NULL ⇒ NOT IN không bao giờ TRUE ⇒ 0 dòng
SELECT count(*) AS ket_qua_nghi_ngo
FROM customers
WHERE id NOT IN (SELECT referred_by FROM customers);
Kết quả
 ket_qua_nghi_ngo
------------------
                0
(1 row)
-- ĐÚNG, cách 1: NOT EXISTS
SELECT count(*) AS dung_1
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM customers r WHERE r.referred_by = c.id);
Kết quả
 dung_1
--------
     55
(1 row)
-- ĐÚNG, cách 2: lọc NULL trong subquery
SELECT count(*) AS dung_2
FROM customers
WHERE id NOT IN (SELECT referred_by FROM customers WHERE referred_by IS NOT NULL);
Kết quả
 dung_2
--------
     55
(1 row)
🧠 Cách chuyên gia — Quy tắc chọn
  • Muốn kiểm tra có/không có bản ghi liên quan → EXISTS / NOT EXISTS (an toàn NULL, thường là kế hoạch tốt nhất).
  • Muốn lấy cột từ bảng liên quan → JOIN.
  • IN (danh sách cố định) là ổn. NOT IN (subquery) chỉ khi chắc chắn cột không NULL (có NOT NULL constraint) — nếu không, tránh.
  • Trong PostgreSQL, IN (subquery), EXISTS và semi-join thường cùng kế hoạch; đừng "tối ưu" theo truyền thuyết — hãy đo bằng EXPLAIN (module 11).
-- ANY / ALL: so sánh với cả tập
SELECT name, price FROM products
WHERE price > ALL (SELECT price FROM products WHERE category_id = 6)  -- đắt hơn MỌI phụ kiện
ORDER BY price LIMIT 3;
Kết quả
          name          |   price
------------------------+------------
 Nồi chiên không dầu 5L | 2290000.00
 Điện thoại Aurora Lite | 6490000.00
 Robot hút bụi          | 7490000.00
(3 rows)

UNION, INTERSECT, EXCEPT

Phép toán tập hợp xếp chồng kết quả hai truy vấn (cùng số cột, kiểu tương thích; tên cột lấy từ truy vấn đầu).

PhépÝ nghĩaGhi chú
UNION ALLNối hai tập, giữ trùngRẻ nhất — mặc định nên dùng
UNIONNối rồi loại trùngThêm bước sort/hash khử trùng; vô tình xoá dữ liệu hợp lệ trùng nhau
INTERSECTPhần chungKhử trùng
EXCEPTCó ở A, không có ở BKhử trùng; thứ tự hai vế quan trọng
-- Khách mua điện thoại VÀ mua laptop
SELECT o.customer_id FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id WHERE p.category_id = 4
INTERSECT
SELECT o.customer_id FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id WHERE p.category_id = 5
ORDER BY 1 LIMIT 5;
Kết quả
 customer_id
-------------
           1
           2
           3
           4
           5
(5 rows)
-- Gộp hai nguồn có cùng cấu trúc thành một báo cáo; thêm cột nhãn nguồn
SELECT 'Khách mới' AS loai, id, created_at FROM customers WHERE created_at >= '2024-06-01'
UNION ALL
SELECT 'Đơn cuối'  AS loai, id, ordered_at FROM orders WHERE ordered_at >= '2024-11-01'
ORDER BY created_at LIMIT 5;
Kết quả
   loai    | id |       created_at
-----------+----+------------------------
 Khách mới | 51 | 2024-06-02 09:00:00+07
 Khách mới | 52 | 2024-06-05 09:00:00+07
 Khách mới | 53 | 2024-06-08 09:00:00+07
 Khách mới | 54 | 2024-06-11 09:00:00+07
 Khách mới | 55 | 2024-06-14 09:00:00+07
(5 rows)
❌ Lỗi thường gặp — UNION thay vì UNION ALL

Hai kỳ doanh thu có cùng số tiền → UNION coi là trùng và xoá một dòng: tổng sai mà không báo lỗi. Chỉ dùng UNION khi thật sự cần khử trùng; còn lại luôn UNION ALL.

Bài toán 'chia': tất cả, mọi, không bao giờ

"Khách đã nhận hàng (quý 1/2024) từ mọi danh mục gốc" là dạng phép chia quan hệ. Hai cách chuẩn, cùng cho kết quả 13 trên 16 khách có đơn giao trong quý:

-- Cách 1: đếm số nhóm khác nhau (gọn, dễ hiểu)
SELECT count(*) AS so_khach_du_3_danh_muc FROM (
  SELECT o.customer_id
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p     ON p.id = oi.product_id
  JOIN categories c   ON c.id = p.category_id
  WHERE o.status = 'delivered' AND o.ordered_at < '2024-04-01'      -- đơn đã giao trong quý 1
  GROUP BY o.customer_id
  HAVING count(DISTINCT COALESCE(c.parent_id, c.id)) = (SELECT count(*) FROM categories WHERE parent_id IS NULL)
) t;
Kết quả
 so_khach_du_3_danh_muc
------------------------
                     10
(1 row)
-- Cách 2: NOT EXISTS lồng nhau — 'không tồn tại danh mục gốc nào mà khách chưa mua'
SELECT count(*) AS so_khach
FROM customers cu
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = cu.id AND o.status = 'delivered' AND o.ordered_at < '2024-04-01')
  AND NOT EXISTS (
  SELECT 1 FROM categories g WHERE g.parent_id IS NULL
  AND NOT EXISTS (
    SELECT 1 FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    JOIN products p     ON p.id = oi.product_id
    JOIN categories c   ON c.id = p.category_id
    WHERE o.customer_id = cu.id AND o.status = 'delivered' AND o.ordered_at < '2024-04-01'
      AND COALESCE(c.parent_id, c.id) = g.id));
Kết quả
 so_khach
----------
       10
(1 row)
💡 Chọn cách nào

Cách 1 (đếm) dễ viết, dễ đọc và thường đủ nhanh. Cách 2 (NOT EXISTS kép) là dạng "kinh điển" trong giáo trình, xử lý đúng cả khi tập yêu cầu thay đổi động, nhưng khó đọc hơn — dùng khi cần ngữ nghĩa chính xác như "không thiếu cái nào".

🏋️ Bài tập thực hành

Sản phẩm đắt nhất mỗi danh mục (không dùng window)Trung bình

Với mỗi category_id, lấy sản phẩm có giá cao nhất bằng correlated subquery hoặc = (SELECT max(...)).

Xem lời giải & giải thích
SELECT p.category_id, p.name, p.price
FROM products p
WHERE p.price = (SELECT max(p2.price) FROM products p2 WHERE p2.category_id = p.category_id)
ORDER BY p.category_id;
Kết quả
 category_id |          name           |    price
-------------+-------------------------+-------------
           3 | Robot hút bụi           |  7490000.00
           4 | Điện thoại Nova Pro     | 18990000.00
           5 | Laptop Zenbook 16 Pro   | 34900000.00
           6 | Tai nghe không dây Echo |  1890000.00
           7 | Áo khoác gió            |   690000.00
           8 | Giày da nam             |  1590000.00
           9 | Nồi chiên không dầu 5L  |  2290000.00
(7 rows)

Nếu hai sản phẩm cùng giá cao nhất, cả hai đều hiện (đúng ngữ nghĩa 'ngang hạng'). Module 8 sẽ làm gọn hơn bằng window function.

Khách có đơn huỷ nhưng không có đơn đang chờTrung bình

Tìm khách đã từng có ít nhất một đơn cancelled nhưng không có đơn nào đang pending, bằng EXISTS và NOT EXISTS.

Xem lời giải & giải thích
SELECT count(*) AS so_khach
FROM customers c
WHERE EXISTS     (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'cancelled')
  AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'pending');
Kết quả
 so_khach
----------
       27
(1 row)

Mỗi điều kiện là một câu hỏi có/không độc lập về cùng khách. Cách này gọn hơn nhiều so với JOIN + GROUP BY + HAVING có điều kiện, và không bị fan-out.

Tìm lỗi NOT INKhó

Truy vấn SELECT name FROM products WHERE id NOT IN (SELECT product_id FROM order_items WHERE qty > 1000) trả về gì? Vì sao đây là cách viết 'an toàn tạm thời' và rủi ro về sau? Viết lại bằng NOT EXISTS.

Xem lời giải & giải thích
SELECT count(*) AS sp_chua_ban_qty_lon
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id AND oi.qty > 1000);
Kết quả
 sp_chua_ban_qty_lon
---------------------
                  20
(1 row)

Cột order_items.product_id là NOT NULL nên NOT IN hôm nay vẫn đúng. Nhưng nếu sau này cột đó (hoặc một subquery khác) cho phép NULL, kết quả sẽ biến mất. NOT EXISTS đúng bất kể ràng buộc nên là thói quen tốt.

📚 Tài nguyên học tập

✅ Tự đánh giá hoàn thành

  • Dùng NOT EXISTS thay NOT IN cho mọi anti-join có thể chứa NULL
  • Biết khi nào subquery phải trả đúng 1 dòng và cách đảm bảo điều đó
  • Mặc định UNION ALL; chỉ UNION khi cần khử trùng
  • Giải được bài 'khách mua tất cả ...' bằng đếm nhóm hoặc NOT EXISTS kép