FatimaAlam1234In SQL, a correlated subquery is a subquery that uses values from the outer query in its WHERE clause. The correlated subquery is evaluated once for each row processed by the outer query. It exists because it depends on the outer query and it cannot execute independently of the outer query because the subquery is correlated with the outer query as it uses its column in its WHERE clause.
SELECT column_name [, column_name...]
FROM table1 [, table2...]
WHERE column_name OPERATOR
(SELECT column_name [, column_name...]
FROM table_name
WHERE condition [table1.column_name = table2.column_name...]);
SELECT e1.employee_name, e1.salary
FROM employee e1
WHERE salary >
(SELECT AVG(salary)
FROM employee e2
WHERE e1.department = e2.department);
