Double-Nested Subquery
Double-Nested Subquery is a SQL statement in the Subqueries category. Nested subqueries can be deeply nested. Each level can filter/aggregate before passing results to the outer level. The syntax is SELECT ... FROM (SELECT ... FROM (SELECT ... FROM table) AS inner) AS outer;. It returns multi-level nested result. A typical example: SELECT department, avg_salary FROM ( SELECT department, AVG(salary) AS avg_salary, COUNT(*) AS emp_count FROM ( SELECT * FROM employees WHERE hire_date > '2020-01-01' ) AS recent_hires GROUP BY department ) AS dept_stats WHERE emp_count >= 5; -- Departments with at least 5 recent hires and their avg salary -- Inner: filter recent hires -- Middle: group by department -- Outer: filter departments with >=5 employees A close relative is Scalar Subquery, which a subquery that returns a single value (one row, one column). Can be used in SELECT, WHERE, HAVING clauses. A close relative is Correlated Subquery, which a subquery that references columns from the outer query. Evaluated for each row of the outer query.