Correlated Subquery in SELECT (Row Comparison)
Correlated Subquery in SELECT (Row Comparison) is a SQL statement in the Subqueries category. A correlated subquery in the SELECT clause computes a value for each row using data from the outer row. The syntax is SELECT col1, (SELECT MAX(col2) FROM table2 WHERE table2.fk = table1.pk) AS max_col2 FROM table1;. It returns row-comparison result. A typical example: SELECT e.name, e.salary, (SELECT ROUND(AVG(e2.salary), 0) FROM employees e2 WHERE e2.dept_id = e.dept_id) AS dept_avg, e.salary - (SELECT ROUND(AVG(e2.salary), 0) FROM employees e2 WHERE e2.dept_id = e.dept_id) AS vs_avg FROM employees e; -- Shows each employee's salary compared to their department average -- Can be optimized with window functions or CTEs 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.