Correlated Subquery is a sub-query that uses values from the outer query. In this case the inner query has to be executed for every row of outer query.
The inner query depends on outer query for its execution.
Example: To find the Nth Max Mark a student obtained
FROM Student S1
WHERE N-1 = (SELECT COUNT(*)
FROM Student S2
WHERE S1.Mark < S2.Mark)
This query will check for count of students who got less mark from the outer query statement and it will match with N -1.
IF the below condition satisfy the student is in 10th position
If we need the 10th position mark that is N = 10
9 = ( IF count S1.Mark < S2.Mark then 9 END)