Correlated subquery
In a SQL database query, a correlated subquery is a subquery that uses values from the outer query. This can have major impact on performance because the correlated subquery might get recomputed every time for each row of the outer query is processed. A correlated subquery can contain another correlated subquery.
Examples
Correlated subqueries in the WHERE clause
Here is an example for a typical correlated subquery. In this example, the objective is to find all employees whose salary is above average for their department.SELECT employee_number, name
FROM employees emp
WHERE salary > ;
In the above query the outer query is
SELECT employee_number, name
FROM employees emp
WHERE salary >...
and the inner query is
SELECT AVG
FROM employees
WHERE department = emp.department
In the above nested query the inner query has to be re-executed for each employee.
Correlated subqueries in the SELECT clause
Correlated subqueries may appear elsewhere besides the WHERE clause; for example, this query uses a correlated subquery in the SELECT clause to print the entire list of employees alongside the average salary for each employee's department. Again, because the subquery is correlated with a column of the outer query, it must be re-executed for each row of the result.SELECT employee_number,
name,
AS department_average
FROM employees emp
Correlated subqueries in the FROM clause
It is generally meaningless to have a correlated subquery in the FROM clause because the table in the FROM clause is needed to evaluate the outer query, but the correlated subquery in the FROM clause can't be evaluated before the outer query is evaluated, causing a chicken-and-egg problem. Specifically, MariaDB lists this as a limitation in its documentation.However, in some database systems, it is allowed to use correlated subqueries while joining in the FROM clause, referencing the tables listed before the join using a specified keyword, producing a number of rows in the correlated subquery and joining it to the table on the left. For example, in PostgreSQL, adding the keyword LATERAL before the right-hand subquery, or in Microsoft SQL Server, using the keyword CROSS APPLY or OUTER APPLY instead of JOIN achieves the effect.