Corelated Sub-Queries

A correlated subquery is one that makes use of values from the outer query. Because of this dependency, a correlated subquery cannot be executed as a simple subquery on its own. It is evaluated once for each row that the outer query processes.

In order to understand the below example, consider the dataset provided here.

SELECT deptno, sal FROM emp e1
WHERE sal > (
aaaa SELECT AVG(sal) FROM emp e2
aaaa WHERE e2.deptno = e1.deptno );

Please take note of the preceding SELECT statement. It appears to be a simple sub-query. The values returned by the sub-query are used by the primary query. However, in this example, the sub-query has a reference to the parent query, causing it to loop. This also implies that the sub-query is reliant on the parent query.

Hope you find this article helpful.

One comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s