Demonstration of Co-related subquery

UNDERSTANDING CO-RELATED SUBQUERY

A correlated subquery is a subquery that uses values from the outer query, requiring the inner query to execute once for each outer query

Here is a TABLE: emp

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	12/17/1980	800		20
7788	SCOTT	ANALYST	7566	4/19/1987	3000		20
7839	KING	PRESIDENT		11/17/1981	5000		10
7844	TURNER	SALESMAN	7698	9/8/1981	1500	0	30
7902	FORD	ANALYST	7566	12/3/1981	3000		20

I want to find out nth highest salary of an employee from emp table

Now the query is :

select distinct sal from emp e where &no=(select count(distinct sal) from emp where e.sal<=sal) 

Demonstration

As we know co-related subquery uses the value from the outer query requiring the inner query to execute once for each outer query.

Here I am dividing the query into 3 sections

Section 1:

Outer query passess the value to inner query

Section 2:

Inner query uses the value passed from outer query for evaluation

Section 3:

Output of inner query is compared with the value passed in where clause

SECTION-1 Distinct keyword sorted the output 5000 3000 1500 800 ------------------- 5000 --> passed 3000 1500 800 2nd execution 5000 3000 --> Passed 1500 800 Likewise we can compare for 3 ---nth highest salary

SECTION-2(e.sal<=sal) 1st execution 5000<=5000 (true cnt=1) 5000<=3000(false cnt=1) 5000<=1500(flase cnt=1) 5000<=800(false   cnt=1)  ----------------- 1st execution 1st 5000 will be comared and Output will be 1 as above 2nd execution 3000<=5000 (true cnt=1) 3000<=3000(true cnt=2) 3000<1500 (false cnt=2) 3000<800   (false cnt=2)

SECTION-3 &NO=1 Evaluation (1=1 true ) Hence 5000 is the highest sal  ------------------ &NO=2 (second highest sal) Evaluaton (2=1  false ) Hence 5000 not 2nd highest sal Evaluation (2=2 which is true) Hence 3000 will be the 2nd highest