Correlated Subquery in Oracle

Correlated Subquery has a different way of execution. It follows Top to down (outer to inner) approach. This is what makes Correlated Subquery different from single and multi-row subqueries. Single Row and multi-row subqueries follow the inner to outer approach, as in first the inner query will be executed and then the outer query is executed.

However, a Correlated query follows a different approach, that is the first outer query is solved according to the output of the inner query. Also here, the subquery takes values from the outer query to execute.

Example

Follow the Stu Table as shown below:

Let's take another table Marks Table with Stu Table as:

Marks Table

You can see that Roll_no is Marks Table stands as a foreign key for Roll_no as a primary key in Stu table.

Now I want to find details for the student that also contain marks in the Marks table using a correlated subquery.

Select * from stu
where exists (Select Roll_No from Marks where Roll_No=Stu.Roll_No);

OUTPUT: