subqueries

Subqueries are queries embedded inside another SQL query. They are evaluated to produce a value or a set of values that the outer query uses for filtering, comparison, or computation. Subqueries can appear in various parts of the outer statement, most commonly in the WHERE or HAVING clauses, or as part of a SELECT list or as a derived table in the FROM clause.

Two broad categories are correlated and non-correlated subqueries. Non-correlated subqueries are independent of the outer query and can be executed once. Correlated subqueries reference columns from the outer query and are evaluated once per outer row, which can affect performance. Subqueries can also be scalar (returning a single value), multi-row, or existential (using EXISTS) to test for the existence of rows.

Examples:

- Non-correlated scalar subquery: SELECT name FROM employees WHERE salary = (SELECT AVG(salary) FROM employees);

- Correlated scalar subquery: SELECT name FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

- Subquery in the FROM clause (derived table): SELECT d.name, t.emp_count FROM departments d JOIN (SELECT department_id, COUNT(*) AS emp_count FROM employees GROUP BY department_id) t ON d.id = t.department_id;

Performance and alternatives: In some cases, subqueries can be rewritten as joins or using EXISTS for better efficiency, especially for correlated subqueries. Proper indexing and query planning are important for ensuring good performance. Subqueries remain a common and expressive tool across SQL dialects.