How to write subqueries in SQL

Short answer

A subquery is a query nested inside another query. It can return a scalar, a list, or a table for use in the outer query.

Steps

1. Scalar subquery in SELECT: SELECT name, (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) FROM customers c;
2. Subquery in WHERE with IN: SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = 1);
3. Correlated subquery: SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);
4. Subquery in FROM: SELECT * FROM (SELECT department, AVG(salary) AS avg_sal FROM employees GROUP BY department) AS dept_avgs WHERE avg_sal > 50000;
5. Use EXISTS for semi-joins: SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Tips

• Correlated subqueries execute once per outer row; consider joins for large datasets.
• Modern optimizers often rewrite subqueries to joins automatically.

Common issues

• Subqueries returning multiple rows in a scalar context cause runtime errors.
• Deeply nested subqueries become hard to read and debug.r