Advanced Subqueries

Advanced Subqueries — Deep dive into advanced subquery techniques — correlated, EXISTS, LATERAL, derived tables, and more. The guide walks through Correlated Subqueries, EXISTS & NOT EXISTS, LATERAL Subqueries, Derived Tables (Subqueries in FROM), Subqueries in SELECT, Subquery Performance Tips. A correlated subquery references columns from the outer query and executes once per outer row. Example: SELECT name, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id). Correlated subqueries can be slower than JOINs for large datasets. Use them when the calculation depends on each outer row's context, like per-group comparisons. EXISTS returns true if the subquery returns any rows. It is typically faster than IN because it short-circuits on the first match. NOT EXISTS is NULL-safe and performs better than NOT IN when NULLs are possible. Example: SELECT * FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id). Use SELECT 1 or SELECT * —…