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.
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ạng | Vị trí | Trả về |
|---|---|---|
| Scalar subquery | SELECT/WHERE như một giá trị | Đúng 1 dòng, 1 cột (nhiều hơn → lỗi) |
| Subquery với IN / ANY / ALL | WHERE | 1 cột, nhiều dòng |
| Derived table | FROM (bắt buộc có alias) | Một bảng tạm |
| Correlated subquery | Tham chiếu cột truy vấn ngoài | Chạ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; 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; 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; 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; 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;ERROR: more than one row returned by a subquery used as an expressionLỗ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.
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'); 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; 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); 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); 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); dung_2
--------
55
(1 row)- 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 NULLconstraint) — nếu không, tránh.- Trong PostgreSQL,
IN (subquery),EXISTSvà semi-join thường cùng kế hoạch; đừng "tối ưu" theo truyền thuyết — hãy đo bằngEXPLAIN(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; 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ĩa | Ghi chú |
|---|---|---|
UNION ALL | Nối hai tập, giữ trùng | Rẻ nhất — mặc định nên dùng |
UNION | Nối rồi loại trùng | Thêm bước sort/hash khử trùng; vô tình xoá dữ liệu hợp lệ trùng nhau |
INTERSECT | Phần chung | Khử trùng |
EXCEPT | Có ở A, không có ở B | Khử 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; 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; 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)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; 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)); so_khach
----------
10
(1 row)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
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; 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.
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'); 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.
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); 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
-
PostgreSQL — Subquery ExpressionsEXISTS, IN, ANY, ALL — ngữ nghĩa chính xácDocs
-
Cybertec — NOT IN vs NOT EXISTSSubquery và hiệu năng trong PostgreSQLBài viết
✅ 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