Truy vấn SQL nâng cao: SUBQUERY (truy vấn con) và CTE (Common Table Expression) — hai kỹ thuật mạnh mẽ sử dụng để tổ chức truy vấn logic, sạch và hiệu quả hơn trong các hệ cơ sở dữ liệu quan hệ.

---

✅ 1. SUBQUERY là gì?

Subquery (truy vấn con) là một câu lệnh SELECT được lồng trong:

• SELECT, FROM, WHERE, hoặc HAVING
• Giúp truy vấn theo điều kiện phức tạp, có phụ thuộc vào dữ liệu khác

---

🔸 1.1. Subquery trong WHERE

SELECT name
FROM Students
WHERE id IN (
    SELECT student_id FROM Enrollments WHERE subject = 'Math'
);

Lấy tên sinh viên học môn Toán.

---

🔸 1.2. Subquery trong SELECT

SELECT 
    name,
    (SELECT COUNT(*) FROM Orders WHERE Orders.customer_id = Customers.id) AS total_orders
FROM Customers;

Truy vấn số đơn hàng mỗi khách hàng ngay trong SELECT.

---

🔸 1.3. Subquery trong FROM (inline view)

SELECT avg_amount
FROM (
    SELECT AVG(amount) AS avg_amount FROM Orders
) AS sub;

Tạo bảng tạm thời chứa giá trị trung bình để xử lý tiếp.

---

🔸 1.4. Subquery có trả về 1 giá trị (scalar)

SELECT name
FROM Customers
WHERE id = (SELECT customer_id FROM Orders WHERE amount = (SELECT MAX(amount) FROM Orders));

Lấy tên khách hàng có đơn hàng lớn nhất.

---

✅ 2. CTE – Common Table Expression

Từ MySQL 8.0 trở lên, bạn có thể sử dụng WITH ... AS để tạo CTE – một cách đặt tên cho truy vấn con và tái sử dụng trong câu truy vấn.

---

🔸 2.1. Cú pháp cơ bản

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

---

🔸 2.2. Ví dụ đơn giản

WITH HighValueOrders AS (
    SELECT * FROM Orders WHERE amount > 300
)
SELECT * FROM HighValueOrders;

CTE giúp viết truy vấn sạch hơn, dễ đọc hơn thay vì lồng nhiều cấp.

---

🔸 2.3. CTE có thể JOIN, GROUP BY, RANK, v.v.

WITH OrderTotals AS (
    SELECT customer_id, SUM(amount) AS total
    FROM Orders
    GROUP BY customer_id
)
SELECT Customers.name, OrderTotals.total
FROM Customers
JOIN OrderTotals ON Customers.id = OrderTotals.customer_id;

---

🔸 2.4. CTE đệ quy (recursive CTE)

Dùng trong cây thư mục, tổ chức cấp bậc…

WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT * FROM numbers;

📌 Kết quả: 1 → 5

---

✅ So sánh nhanh SUBQUERY vs CTE

Tiêu chí	Subquery	CTE
Viết trong	SELECT, WHERE, FROM	Định nghĩa riêng biệt với WITH
Tái sử dụng	❌ Không	✅ Có thể dùng nhiều lần
Dễ đọc	👎 Khi lồng nhiều truy vấn	✅ Rất sạch, rõ ràng
Hỗ trợ đệ quy	❌ Không	✅ Có (WITH RECURSIVE)
Yêu cầu phiên bản	✅ Mọi phiên bản	✅ MySQL >= 8.0

---

💡 Khi nào dùng SUBQUERY và khi nào dùng CTE?

• Dùng SUBQUERY khi:
  • Truy vấn đơn giản, nhanh gọn
  • Cần điều kiện lọc phụ thuộc bảng khác
• Dùng CTE khi:
  • Truy vấn phức tạp cần chia thành nhiều bước
  • Muốn tái sử dụng hoặc dễ đọc
  • Cần xử lý đệ quy (cây phân cấp)

---

📘 Kết Luận

• SUBQUERY và CTE là hai công cụ không thể thiếu trong xử lý truy vấn nâng cao.
• CTE được xem là “phiên bản nâng cấp” của subquery nhờ cấu trúc rõ ràng, dễ tái sử dụng.
• Hãy luôn chọn cách viết giúp truy vấn dễ hiểu, bảo trì tốt, đặc biệt trong hệ thống lớn.