Về cơ bản Subquery là 1 câu query nằm trong 1 câu query khác. Nó luôn luôn được nằm trong ngoặc () và thường run trước câu query bao chứa nó.

Vì nó cũng là 1 câu query bình thường nên nó có kết quả là 1 trong 3 trường hợp sau đây:

• 1 column, 1 row (1 giá trị)
• 1 column, nhiều rows (nhiều giá trị cùng loại)
• nhiều columns, nhiều rows (nhiều giá trị khác loại)

I. 1 column, 1 row Subquery

• 1 column, 1 row nó tương đương với 1 giá trị duy nhất, do đó, nó có thể dùng để so sánh trực tiếp.

Ví dụ: Tìm thông tin của customer mới nhất (customer có customer_id cao nhất)

SELECT customer_id, first_name, last_name
FROM customer
WHERE customer_id = (SELECT MAX(customer_id) FROM customer);

Trong đó. SELECT MAX(customer_id) FROM customer có kết quả là

II. 1 column, multi rows Subquery

1 column và nhiều rows Subquery sẽ trả lại 1 list các giá trị cùng loại –> ko thể dùng để so sánh trực tiếp được.

1. Dùng trong IN và NOT IN

Ví dụ: Tìm tất cả các thành phố mà thuộc đất nước Canada hoặc Mexico (IN)

SELECT city_id, city
FROM city
WHERE country_id IN
	(SELECT country_id
	FROM country
	WHERE country IN ('Canada','Mexico')
    );

hoặc Tìm tất cả các thành phố mà không thuộc đất nước Canada hoặc Mexico (NOT IN)

SELECT city_id, city
FROM city
WHERE country_id NOT IN
	(SELECT country_id
	FROM country
	WHERE country IN ('Canada','Mexico')
    );

SELECT customer_id, first_name, last_name
FROM customer
WHERE customer_id = (SELECT MAX(customer_id) FROM customer);

2. Dùng trong All

Dùng để so sánh với từng value ở trong list được return từ Subquery, sử dụng kèm với các operator (=, <>, <, >)

SELECT customer_id, count(*)
FROM rental
GROUP BY customer_id
HAVING count(*) > ALL
      ( SELECT count(*)
 		FROM rental r
 		INNER JOIN customer c
 			ON r.customer_id = c.customer_id
 		INNER JOIN address a
 			ON c.address_id = a.address_id
 		INNER JOIN city ct-> 
         	ON a.city_id = ct.city_id
		INNER JOIN country co
 			ON ct.country_id = co.country_id
 		WHERE co.country IN ('United States','Mexico','Canada')
		GROUP BY r.customer_id
 	);

Ví dụ:

• Subquery trả ra tổng số những bộ phim được thuê từ các khách hàng đến từ Bắc Mỹ (Mỹ, Canada và Mexico).

• Sau đó, sẽ tìm ra khách hàng nào + số bộ phim thuê mà có số phim thuê nhiều hơn tổng số được trả ra từ Subquery.

3. Dùng trong any

Cũng là so sánh với toàn bộ kết quả trả ra từ Subquery như All nhưng thay vì tất cả phải thỏa mãn, chỉ cần 1 phần tử thỏa mãn là đủ.

SELECT customer_id, sum(amount)
FROM payment
GROUP BY customer_id
HAVING sum(amount) > ANY
      ( SELECT sum(p.amount)
 		FROM payment p
 		INNER JOIN customer c
 			ON r.customer_id = c.customer_id
 		INNER JOIN address a
 			ON c.address_id = a.address_id
 		INNER JOIN city ct-> 
         	ON a.city_id = ct.city_id
		INNER JOIN country co
 			ON ct.country_id = co.country_id
 		WHERE co.country IN ('Bolivia','Paraguay','Chile')
		GROUP BY co.country
 	);

• Subquery trả ra 1 list tổng số tiền thuê phim của tất cả các khách hàng tại Bolivia, Paraguay và Chile, group theo quốc gia.
• Sau đó lấy ra những khách hàng nào có số tiền thuê lớn hơn bất cứ 1 số nào từ list trên.

III. Multi columns, multi rows Subquery

Giả sử trong 1 câu query mà cần đến 2 Subquery để lấy ra được 2 list condition values thì cũng có thể gộp 2 Subquery đấy thành 1 Subquery mà trả ra 1 result set có nhiều column, nhiều rows.

Ví dụ:

SELECT fa.actor_id, fa.film_id
FROM film_actor fa
WHERE fa.actor_id IN
	(SELECT actor_id FROM actor WHERE last_name = 'MONROE')
AND fa.film_id IN
	(SELECT film_id FROM film WHERE rating = 'PG');

• Subquery 1 trả 1 list actor_id mà có last_name là “MONROE”
• Subquery 2 trả 1 list phim được đánh giá “PG”
• Câu query chính: lấy ra list các actor và film mà actor có last_name = “MONROE” được xuất hiện trong phim được đánh giá “PG”

Câu này có thể viết lại như sau:

SELECT actor_id, film_id
FROM film_actor
WHERE (actor_id, film_id) IN
	( SELECT a.actor_id, f.film_id
	  FROM actor a
	  CROSS JOIN film f
	  WHERE a.last_name = 'MONROE' AND f.rating = 'PG'
    );

• Subquery trả ra 1 list các cặp (actor_id & film_id) sau khi Cross join
• Sau đó, lọc ra các cặp xuất hiện trong table film_actor

IV. Cách phân loại Subquery

Có 2 loại Subquery:

• Noncorrelated Subqueries: Câu subquery không liên quan đến câu query chính, những ví dụ ở trên là thuộc dạng này.
• Correlated Subqueries: Câu subquery có liên quan đến câu query chính

Ví dụ: Trong phần WHERE của subquery lấy table c ở trong câu query chính

SELECT c.first_name, c.last_name
FROM customer c
WHERE 20 = (SELECT count(*) FROM rental r
			WHERE r.customer_id = c.customer_id
           );

• Step 1: Câu query chính cung cấp c.customer_id cho subquery
• Step 2: Run subquery cho mỗi customer_id, đếm số lần thuê phim 1 của khách hàng.
• Step 3: Nếu số lần thuê phim bằng 20 thì lấy first_name và last_name cho vào result set.

Note: Performance của loại Subquery này cũng không tốt lắm nếu câu query chính có 1 table quá lớn, quá nhiều customer chẳng hạn.

Một ví dụ khác:

SELECT c.first_name, c.last_name
FROM customer c
WHERE
	( SELECT sum(p.amount) FROM payment p
	  WHERE p.customer_id = c.customer_id )
BETWEEN 180 AND 240;

Ngoài ra, Correlated Subqueries còn hay sử dụng với operator exists, mục đích là xác định xem có tồn tại mối quan hệ nào đó hay ko?

Ví dụ: Tìm tất cả các khách hàng mà từng thuê ít nhất 1 phim từ trước 25/5/2005 mà không quan tâm đến số lượng phim đã thuê.

SELECT c.first_name, c.last_name
FROM customer c
WHERE EXISTS
	(SELECT 1 FROM rental r
	WHERE r.customer_id = c.customer_id
	AND date(r.rental_date) < '2005-05-25');

Lưu ý: function date này là của Mysql.

• Do số lượng phim thuê ko quan trọng nên ta có thể thay thế data thực tế bằng 1 nếu điều kiện ở WHERE được thỏa mãn.

V. Một số thông tin khác

• Các ví dụ trên chỉ dùng với SELECT, nhưng hoàn toàn có thể dùng subquery với INSERT, UPDATE, DELETE
• Subquery có thể cung cấp data source (nguồn dữ liệu) hoặc condition (điều kiện) cho query chính
• Subquery có thể dùng trong SELECT, FROM, WHERE, HAVING, ORDER BY.

Nếu bạn thích học video thì đây: