Truy vấn con (Subquery)

← GROUPING SETS, CUBE, ROLLUP | → CTE và truy vấn đệ quy

Bạn sẽ học được

Lồng một truy vấn bên trong truy vấn khác; phân biệt subquery độc lập và subquery tương quan; dùng IN, ANY, ALL, EXISTS đúng chỗ.

1. Subquery là gì?

Subquery (truy vấn con) là một câu SELECT nằm bên trong một câu lệnh SQL khác. PostgreSQL luôn chạy subquery trước, rồi dùng kết quả của nó cho truy vấn ngoài.

1.1. Bài toán mở đầu

Tìm các phim có thời lượng dài hơn trung bình. Không thể viết:

-- ❌ LỖI: không dùng hàm tổng hợp trực tiếp trong WHERE
SELECT title, length FROM film WHERE length > avg(length);
-- ERROR: aggregate functions are not allowed in WHERE

Phải tách làm hai bước:

-- Bước 1
SELECT avg(length) FROM film;   -- 115.272
 
-- Bước 2
SELECT title, length FROM film WHERE length > 115.272;

Subquery gộp hai bước thành một:

SELECT title, length
FROM film
WHERE length > (SELECT avg(length) FROM film)
ORDER BY length DESC, film_id
LIMIT 5;

2. Phân loại theo số dòng/cột trả về

2.1. Scalar subquery — 1 dòng, 1 cột

Dùng được ở bất cứ đâu chấp nhận một giá trị đơn:

-- Trong WHERE
SELECT title FROM film
WHERE length > (SELECT avg(length) FROM film);
 
-- Trong SELECT
SELECT title,
       length,
       (SELECT avg(length) FROM film) AS tb_chung,
       length - (SELECT avg(length) FROM film) AS chenh_lech
FROM film
LIMIT 5;
 
-- Trong ORDER BY, HAVING, thậm chí trong VALUES

Scalar subquery phải trả về tối đa 1 dòng
Nếu trả về nhiều dòng:
ERROR: more than one row returned by a subquery used as an expression
Nếu trả về 0 dòng, kết quả là NULL (không lỗi) — điều này có thể gây bất ngờ.

2.2. Row subquery — 1 dòng, nhiều cột

SELECT * FROM film
WHERE (rating, rental_duration) = (
   SELECT rating, rental_duration FROM film WHERE film_id = 1
);

2.3. Table subquery — nhiều dòng

Dùng với IN, ANY, ALL, EXISTS, hoặc trong FROM:

SELECT first_name, last_name
FROM customer
WHERE customer_id IN (
   SELECT customer_id FROM rental
   WHERE return_date IS NULL
);

2.4. Subquery trong `FROM` — bảng dẫn xuất

SELECT the_loai, so_phim
FROM (
   SELECT c.name AS the_loai, count(*) AS so_phim
   FROM category c
   JOIN film_category fc ON fc.category_id = c.category_id
   GROUP BY c.name
) AS thong_ke              -- ← bí danh BẮT BUỘC
WHERE so_phim > 60
ORDER BY so_phim DESC;

Subquery trong FROM bắt buộc phải có bí danh
Quên bí danh sẽ báo lỗi:
ERROR: subquery in FROM must have an alias
Với truy vấn nhiều tầng, CTE dễ đọc hơn nhiều. → CTE và truy vấn đệ quy

3. Subquery độc lập vs tương quan

Đây là phân biệt quan trọng nhất về mặt hiệu năng.

3.1. Subquery độc lập (uncorrelated)

Không tham chiếu tới truy vấn ngoài → chạy một lần duy nhất, kết quả được tái sử dụng.

SELECT title, length
FROM film
WHERE length > (SELECT avg(length) FROM film);
--                                   ↑ không nhắc tới bảng ngoài

3.2. Subquery tương quan (correlated)

Tham chiếu cột của truy vấn ngoài → về mặt logic chạy lại cho từng dòng của truy vấn ngoài.

Tìm các phim dài hơn trung bình của thể loại chính nó:

SELECT f.title, f.length, c.name AS the_loai
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c       ON c.category_id = fc.category_id
WHERE f.length > (
   SELECT avg(f2.length)
   FROM film f2
   JOIN film_category fc2 ON fc2.film_id = f2.film_id
   WHERE fc2.category_id = fc.category_id     -- ← tham chiếu fc của truy vấn ngoài
)
ORDER BY c.name, f.length DESC;

Trình tối ưu hóa thường "gỡ tương quan"

PostgreSQL rất giỏi việc biến subquery tương quan thành join hoặc hash aggregate. Đừng tránh chúng vì lo hiệu năng — hãy đo bằng EXPLAIN ANALYZE trước. → Công thức thực dụng - EXPLAIN, xóa trùng, so sánh bảng

Tuy nhiên, subquery tương quan chứa ORDER BY ... LIMIT thường không gỡ được. Trong trường hợp đó, LATERAL hoặc DISTINCT ON sẽ nhanh hơn.

4. IN với subquery

SELECT film_id, title
FROM film
WHERE film_id IN (
   SELECT film_id FROM inventory WHERE store_id = 1
)
ORDER BY title
LIMIT 5;

Xem thêm về cạm bẫy NOT IN với NULL tại Lọc dữ liệu - WHERE, LIMIT, IN, BETWEEN, LIKE, IS NULL.

5. ANY / SOME

bieu_thuc toan_tu ANY (subquery)

Trả về true nếu có ít nhất một giá trị trong subquery thỏa mãn. SOME là từ đồng nghĩa hoàn toàn.

-- Phim dài hơn ít nhất một phim thuộc thể loại Action
SELECT title, length
FROM film
WHERE length > ANY (
   SELECT f.length
   FROM film f
   JOIN film_category fc ON fc.film_id = f.film_id
   JOIN category c       ON c.category_id = fc.category_id
   WHERE c.name = 'Action'
)
ORDER BY length
LIMIT 5;

> ANY (...) tương đương > min(...) — chỉ cần dài hơn phim ngắn nhất là đủ.

5.1. Quan hệ giữa `= ANY` và `IN`

x = ANY (subquery)   ≡   x IN (subquery)
x <> ALL (subquery)  ≡   x NOT IN (subquery)

5.2. `ANY` với mảng — dùng nhiều trong ứng dụng

SELECT * FROM film
WHERE film_id = ANY (ARRAY[1, 2, 3, 5, 8]);
 
-- Hoặc ép từ chuỗi
SELECT * FROM film
WHERE film_id = ANY (string_to_array('1,2,3', ',')::int[]);

Đây là cách chuẩn để truyền một danh sách tham số từ ứng dụng — an toàn hơn hẳn việc nối chuỗi để tạo IN (...), vốn là con đường dẫn tới SQL injection.

6. ALL

bieu_thuc toan_tu ALL (subquery)

Trả về true chỉ khi mọi giá trị trong subquery đều thỏa mãn.

-- Phim dài hơn TẤT CẢ phim thể loại Action
SELECT title, length
FROM film
WHERE length > ALL (
   SELECT f.length
   FROM film f
   JOIN film_category fc ON fc.film_id = f.film_id
   JOIN category c       ON c.category_id = fc.category_id
   WHERE c.name = 'Action'
)
ORDER BY length DESC;

> ALL (...) tương đương > max(...).

6.1. Bảng tương đương

Viết với `ANY`/`ALL`	Tương đương
`x > ANY (...)`	`x > min(...)`
`x < ANY (...)`	`x < max(...)`
`x > ALL (...)`	`x > max(...)`
`x < ALL (...)`	`x < min(...)`
`x = ANY (...)`	`x IN (...)`
`x <> ALL (...)`	`x NOT IN (...)`

Dạng dùng min/max thường dễ đọc và dễ tối ưu hơn.

6.2. Cạm bẫy: subquery rỗng

Trường hợp	`ANY`	`ALL`
Subquery trả về 0 dòng	`false`	`true` ⚠️
Subquery chứa `NULL`, không có giá trị nào khớp	`NULL`	`NULL`

x > ALL (tập rỗng) cho true vì “mọi phần tử của tập rỗng đều thỏa mãn” — đúng về logic nhưng thường không phải điều bạn muốn.

7. EXISTS

EXISTS (subquery)

Trả về true nếu subquery trả về ít nhất một dòng, bất kể nội dung dòng đó là gì.

SELECT c.first_name, c.last_name
FROM customer c
WHERE EXISTS (
   SELECT 1
   FROM payment p
   WHERE p.customer_id = c.customer_id
     AND p.amount > 11
)
ORDER BY c.first_name;

7.1. Vì sao viết `SELECT 1`?

PostgreSQL không đánh giá danh sách cột của subquery trong EXISTS — nó chỉ kiểm tra có dòng nào không. Bạn có thể viết SELECT 1, SELECT *, hay SELECT 1/0 — kết quả và tốc độ như nhau:

SELECT EXISTS (SELECT 1/0 FROM film WHERE film_id = 1);
-- true  — không hề báo lỗi chia cho 0!

Quy ước dùng SELECT 1 để thể hiện rõ với người đọc rằng nội dung không quan trọng.

7.2. `EXISTS` dừng ngay khi tìm thấy

Điểm mạnh về hiệu năng: PostgreSQL dừng subquery ngay khi có dòng đầu tiên. Với bảng lớn, điều này nhanh hơn nhiều so với IN (vốn có thể phải vật chất hóa toàn bộ danh sách).

7.3. NOT EXISTS — anti-join an toàn

-- Tìm khách hàng chưa từng thanh toán
SELECT c.customer_id, c.first_name, c.last_name
FROM customer c
WHERE NOT EXISTS (
   SELECT 1 FROM payment p WHERE p.customer_id = c.customer_id
);

NOT EXISTS an toàn với NULL, NOT IN thì không
-- ⚠️ Trả về 0 dòng nếu subquery có bất kỳ NULL nào
WHERE x NOT IN (SELECT y FROM t)
 
-- ✅ Luôn đúng
WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.y = x)
Đây là lý do NOT EXISTS được coi là cách viết anti-join chuẩn mực.

7.4. Ba cách viết anti-join

Ba câu sau cho cùng kết quả:

-- 1. NOT EXISTS  (khuyến nghị)
SELECT f.* FROM film f
WHERE NOT EXISTS (SELECT 1 FROM inventory i WHERE i.film_id = f.film_id);
 
-- 2. LEFT JOIN + IS NULL
SELECT f.* FROM film f
LEFT JOIN inventory i ON i.film_id = f.film_id
WHERE i.film_id IS NULL;
 
-- 3. EXCEPT  (chỉ tiện khi cần một cột)
SELECT film_id FROM film
EXCEPT
SELECT film_id FROM inventory;

Cách	Ưu điểm	Nhược điểm
`NOT EXISTS`	An toàn `NULL`, rõ ý định, thường nhanh nhất	Dài hơn chút
`LEFT JOIN ... IS NULL`	Quen thuộc	Dễ chọn nhầm cột `IS NULL`
`EXCEPT`	Ngắn gọn	Khó lấy thêm cột từ bảng gốc

NOT IN — tránh dùng với subquery.

8. Subquery trong các mệnh đề khác

8.1. Trong `SELECT`

SELECT
   c.name AS the_loai,
   (SELECT count(*) FROM film_category fc
    WHERE fc.category_id = c.category_id) AS so_phim
FROM category c
ORDER BY so_phim DESC;

Cách này dễ đọc nhưng thường chậm hơn JOIN + GROUP BY khi bảng lớn.

8.2. Trong `HAVING`

SELECT customer_id, sum(amount) AS tong_chi
FROM payment
GROUP BY customer_id
HAVING sum(amount) > (SELECT avg(amount) * 30 FROM payment)
ORDER BY tong_chi DESC;

8.3. Trong `UPDATE` / `DELETE`

UPDATE film
SET rental_rate = rental_rate * 1.1
WHERE film_id IN (
   SELECT film_id FROM inventory GROUP BY film_id HAVING count(*) > 5
);
 
DELETE FROM rental
WHERE customer_id IN (
   SELECT customer_id FROM customer WHERE activebool = false
);

→ Sửa đổi dữ liệu - INSERT, UPDATE, DELETE, UPSERT

8.4. `LATERAL` — subquery nhìn thấy bảng bên trái

Subquery trong FROM bình thường không tham chiếu được bảng đứng trước nó. LATERAL gỡ bỏ hạn chế đó:

SELECT c.first_name, c.last_name, gan_day.rental_date
FROM customer c
CROSS JOIN LATERAL (
   SELECT r.rental_date
   FROM rental r
   WHERE r.customer_id = c.customer_id     -- ← tham chiếu c, chỉ được nhờ LATERAL
   ORDER BY r.rental_date DESC
   LIMIT 1
) AS gan_day
ORDER BY gan_day.rental_date DESC
LIMIT 10;

Dùng LEFT JOIN LATERAL ... ON true nếu muốn giữ cả những khách không có lượt thuê nào.

→ Kết hợp nhiều bảng - JOIN

9. Bảng chọn công cụ

Nhu cầu	Nên dùng
So sánh với một giá trị tính được	Scalar subquery
Kiểm tra thuộc danh sách	`IN` hoặc `= ANY`
Kiểm tra không thuộc danh sách	`NOT EXISTS` (không dùng `NOT IN`)
Kiểm tra sự tồn tại	`EXISTS`
So với giá trị lớn/nhỏ nhất	`> ALL` / `> ANY`, hoặc `max()`/`min()`
Top-N cho mỗi nhóm	`LATERAL` hoặc `DISTINCT ON` hoặc window function
Truy vấn nhiều tầng, dễ đọc	CTE → CTE và truy vấn đệ quy
Duyệt cây phân cấp	`WITH RECURSIVE`

10. Bài tập

Tìm các phim có rental_rate cao hơn giá thuê trung bình.
Liệt kê khách hàng đã thuê phim thể loại 'Horror' (dùng EXISTS).
Tìm các thể loại không có phim nào dài trên 180 phút.
Với mỗi khách hàng, hiển thị họ tên và số tiền của lần thanh toán lớn nhất (dùng LATERAL).
Tìm phim dài hơn tất cả phim thể loại 'Documentary'.

Viết lại câu sau bằng JOIN và so sánh EXPLAIN ANALYZE:

SELECT c.name,
       (SELECT count(*) FROM film_category fc
        WHERE fc.category_id = c.category_id) AS so_phim
FROM category c;

Câu sau đây có vấn đề gì?

SELECT title FROM film
WHERE film_id NOT IN (SELECT film_id FROM film_actor);

Đáp án

SELECT title, rental_rate
FROM film
WHERE rental_rate > (SELECT avg(rental_rate) FROM film)
ORDER BY rental_rate DESC, title;

SELECT DISTINCT c.customer_id, c.first_name, c.last_name
FROM customer c
WHERE EXISTS (
   SELECT 1
   FROM rental r
   JOIN inventory i     ON i.inventory_id = r.inventory_id
   JOIN film_category fc ON fc.film_id = i.film_id
   JOIN category cat     ON cat.category_id = fc.category_id
   WHERE r.customer_id = c.customer_id
     AND cat.name = 'Horror'
)
ORDER BY c.last_name;

SELECT c.name
FROM category c
WHERE NOT EXISTS (
   SELECT 1
   FROM film_category fc
   JOIN film f ON f.film_id = fc.film_id
   WHERE fc.category_id = c.category_id
     AND f.length > 180
)
ORDER BY c.name;

SELECT c.first_name, c.last_name, lon_nhat.amount, lon_nhat.payment_date
FROM customer c
LEFT JOIN LATERAL (
   SELECT p.amount, p.payment_date
   FROM payment p
   WHERE p.customer_id = c.customer_id
   ORDER BY p.amount DESC, p.payment_id
   LIMIT 1
) AS lon_nhat ON true
ORDER BY lon_nhat.amount DESC NULLS LAST
LIMIT 10;

Dùng LEFT JOIN LATERAL ... ON true để không mất khách chưa thanh toán.

SELECT title, length
FROM film
WHERE length > ALL (
   SELECT f.length
   FROM film f
   JOIN film_category fc ON fc.film_id = f.film_id
   JOIN category c       ON c.category_id = fc.category_id
   WHERE c.name = 'Documentary'
)
ORDER BY length DESC;

Cách rõ ràng hơn:

WHERE length > (SELECT max(f.length) FROM ...)

SELECT c.name, count(fc.film_id) AS so_phim
FROM category c
LEFT JOIN film_category fc ON fc.category_id = c.category_id
GROUP BY c.category_id, c.name
ORDER BY so_phim DESC;

Với 16 thể loại, chênh lệch không đáng kể. Với hàng triệu dòng, phiên bản JOIN + GROUP BY nhanh hơn rõ rệt vì chỉ quét bảng con một lần thay vì một lần cho mỗi dòng ngoài. Chú ý phải dùng count(fc.film_id) chứ không phải count(*) để thể loại rỗng cho ra 0.

7. Nếu film_actor.film_id cho phép NULL và có dòng nào là NULL, câu này trả về 0 dòng dù có phim không diễn viên. Trong CSDL mẫu, film_id là NOT NULL nên vẫn chạy đúng, nhưng đây là mẫu code mong manh. Viết an toàn:

SELECT f.title FROM film f
WHERE NOT EXISTS (
   SELECT 1 FROM film_actor fa WHERE fa.film_id = f.film_id
);

11. Tóm tắt

Scalar subquery trả về 1 giá trị, dùng được ở SELECT, WHERE, HAVING, ORDER BY.
Subquery trong FROM bắt buộc có bí danh; nhiều tầng thì dùng CTE cho dễ đọc.
Subquery độc lập chạy một lần; tương quan tham chiếu truy vấn ngoài.
= ANY ≡ IN; <> ALL ≡ NOT IN; > ALL ≡ > max(); > ANY ≡ > min().
ALL với subquery rỗng trả về true — cạm bẫy dễ bỏ sót.
EXISTS chỉ quan tâm có dòng hay không; viết SELECT 1 theo quy ước.
NOT EXISTS là cách viết anti-join chuẩn — an toàn với NULL, khác NOT IN.
LATERAL cho phép subquery trong FROM tham chiếu bảng bên trái — công cụ giải bài toán top-N mỗi nhóm.

← GROUPING SETS, CUBE, ROLLUP | → CTE và truy vấn đệ quy

description	Lồng một truy vấn bên trong truy vấn khác; phân biệt subquery độc lập và subquery tương quan; dùng IN, ANY, ALL, EXISTS đúng chỗ.
tags	postgresql, sql, subquery, exists, any, all, correlated
aliases

TungDD Wiki

Nội dung

Truy vấn con: Subquery, ANY, ALL, EXISTS

Truy vấn con (Subquery)

1. Subquery là gì?

1.1. Bài toán mở đầu

2. Phân loại theo số dòng/cột trả về

2.1. Scalar subquery — 1 dòng, 1 cột

2.2. Row subquery — 1 dòng, nhiều cột

2.3. Table subquery — nhiều dòng

2.4. Subquery trong `FROM` — bảng dẫn xuất

3. Subquery độc lập vs tương quan

3.1. Subquery độc lập (uncorrelated)

3.2. Subquery tương quan (correlated)

4. IN với subquery

5. ANY / SOME

5.1. Quan hệ giữa `= ANY` và `IN`

5.2. `ANY` với mảng — dùng nhiều trong ứng dụng

6. ALL

6.1. Bảng tương đương

6.2. Cạm bẫy: subquery rỗng

7. EXISTS

7.1. Vì sao viết `SELECT 1`?

7.2. `EXISTS` dừng ngay khi tìm thấy

7.3. NOT EXISTS — anti-join an toàn

7.4. Ba cách viết anti-join

8. Subquery trong các mệnh đề khác

8.1. Trong `SELECT`

8.2. Trong `HAVING`

8.3. Trong `UPDATE` / `DELETE`

8.4. `LATERAL` — subquery nhìn thấy bảng bên trái

9. Bảng chọn công cụ

10. Bài tập

11. Tóm tắt

Sơ đồ

Mục lục

Liên kết ngược

TungDD Wiki

Nội dung

Truy vấn con: Subquery, ANY, ALL, EXISTS

Truy vấn con (Subquery)

1. Subquery là gì?

1.1. Bài toán mở đầu

2. Phân loại theo số dòng/cột trả về

2.1. Scalar subquery — 1 dòng, 1 cột

2.2. Row subquery — 1 dòng, nhiều cột

2.3. Table subquery — nhiều dòng

2.4. Subquery trong FROM — bảng dẫn xuất

3. Subquery độc lập vs tương quan

3.1. Subquery độc lập (uncorrelated)

3.2. Subquery tương quan (correlated)

4. IN với subquery

5. ANY / SOME

5.1. Quan hệ giữa = ANY và IN

5.2. ANY với mảng — dùng nhiều trong ứng dụng

6. ALL

6.1. Bảng tương đương

6.2. Cạm bẫy: subquery rỗng

7. EXISTS

7.1. Vì sao viết SELECT 1?

7.2. EXISTS dừng ngay khi tìm thấy

7.3. NOT EXISTS — anti-join an toàn

7.4. Ba cách viết anti-join

8. Subquery trong các mệnh đề khác

8.1. Trong SELECT

8.2. Trong HAVING

8.3. Trong UPDATE / DELETE

8.4. LATERAL — subquery nhìn thấy bảng bên trái

9. Bảng chọn công cụ

10. Bài tập

11. Tóm tắt

Sơ đồ

Mục lục

Liên kết ngược

2.4. Subquery trong `FROM` — bảng dẫn xuất

5.1. Quan hệ giữa `= ANY` và `IN`

5.2. `ANY` với mảng — dùng nhiều trong ứng dụng

7.1. Vì sao viết `SELECT 1`?

7.2. `EXISTS` dừng ngay khi tìm thấy

8.1. Trong `SELECT`

8.2. Trong `HAVING`

8.3. Trong `UPDATE` / `DELETE`

8.4. `LATERAL` — subquery nhìn thấy bảng bên trái