How Correlated Subquery works in Oracle

Let's assume we have following table in our  schema.

Let’s check how correlated sub query works using above table.

Here I’m going to write a query to get 2nd highest salary.

Select *from emp_chk e1 where 2= (Select Count(distinct(sal)) From Emp_Chk e2 Where e2.sal >=e1.sal)

As we know definition of correlated sub query is ,main query would execute first and for that result set ,inner query would execute once per each row (of main query output).

here inner query is dependent on main query.
 

Let’s go through in practical for above query:-
 

As per definition we will get all records from emp_chk table but here we need to concentrate on sal column because that column used in join condition.
So 67760,32000,25000,34345 are values for sal columns.

1st step: Pass 67760 in inner query.
Select *from emp_chk e1 where 2= (Select Count(distinct(sal)) From Emp_Chk e2 Where e2.sal >= 67760)

Here I have replaced e1.sal with 67760 because that is 1st value which would come and check that query condition. So if you see there, we would get count only one as we have only 1 record which is >=67760.

It means where condition doesn’t satisfy (i.e. where 2=1).so we don’t get any result.

2nd step: Pass 32000 in inner query

Select *from emp_chk e1 where 2= (Select Count(distinct(sal)) From Emp_Chk e2 Where e2.sal >= 32000)

Here we ill get count 3 as we have 3 records which are greater than or equal to 32000 so this one also doesn’t satisfy condition ( i.e. where 2=3). So we don’t get any result.

3rd step: Pass 25000 in inner query
Select *from emp_chk e1 where 2= (Select Count(distinct(sal)) From Emp_Chk e2 Where e2.sal >= 25000)

Here we ill get count 4 as we have 4 records which are greater than or equal to 25000 so this one also doesn’t satisfy condition ( i.e. where 2=4). So we don’t get any result.

4th step: Pass 34345 in inner query
Select *from emp_chk e1 where 2= (Select Count(distinct(sal)) From Emp_Chk e2 Where e2.sal >= 34345)

Here we ill get count 2 as we have 2 records which are greater than or equal to 34345 so finally our where condition got satisfied (i.e. where 2=2) for sal value 34345.So oracle would return this record as output.

In this way we can find out nth highest salary using correlated subquery.