Subquery trong SQL là gì? Khi nào dùng subquery vs JOIN?

Uncorrelated subquery chạy một lần, kết quả dùng cho outer query: SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products).

• Correlated subquery tham chiếu outer query và chạy lại cho MỖI ROW của outer query — O(n²) complexity, rất chậm với bảng lớn: SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept).
• EXISTS vs IN: EXISTS (SELECT 1 FROM orders WHERE user_id = u.id) dừng ngay khi tìm thấy match đầu tiên (short-circuit), tốt cho large subsets; IN (SELECT user_id FROM orders) load toàn bộ list vào memory — NOT IN với NULL trong subquery trả về empty result set (NULL pitfall).
• Scalar subquery trong SELECT: SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count FROM users u — tương đương LEFT JOIN nhưng thường chậm hơn vì chạy N lần.
• Lateral join (PostgreSQL): FROM users u, LATERAL (SELECT * FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3) o — như correlated subquery nhưng có thể reference multiple columns và return multiple rows, optimizer xử lý tốt hơn.
• Khi subquery tốt hơn JOIN: EXISTS checks, khi JOIN tạo fan-out (duplicate rows do one-to-many), scalar aggregates.