SQL - Sub Query Vs Co-related Query

Sub Query : Independent from outer query
First sub query executed and result passes to outer query as input.

Ex.

create table employee (

ID int primary key identity(1,1),

FullName Varchar(20),

Salary money)

insert into employee values ('Megha', 22000)

insert into employee values ('Mukesh', 25000)

insert into employee values ('Renuka', 21000)

insert into employee values ('Ghanshyam', 132000)

Second highest salary using Sub Query

select top 1 o.Salary from

(select top 2 c.Salary from employee c order by c.Salary desc) o order by o.Salary asc

Co-related query : Dependent on outer query
Values from outer query becomes input for inner query then final output gets generated

All records of Sub query compared with each record of outer query

Second highest salary using Co-related Query

select o.Salary from employee o

where 2 = (select count(c.id) from employee c where c.Salary >= o.Salary)