Difference between correlated subqueries and uncorrelated subqueries

Uncorrelated Subquery

If the SQL above looks scary to you, don’t worry – it’s still easy to understand for our purposes here. The subquery portion of the SQL above begins after the “NOT IN” statement. The reason that the query above is an uncorrelated subquery is that the subquery can be run independently of the outer query. Basically, the subquery has no relationship with the outer query.

Example:

Select  * from salesperson where salesperson.ID NOT IN (Select o.id from orders o,customer c where o.id=c.id)

Correlated Subquery

Now, a correlated subquery has the opposite property – the subquery can not be run independently of the outer query. You can take a look at this example of a correlated subquery below and easily see the difference yourself

Example:

Select * from  Employee e where (1)=(select count (distinct(e.salary)) from employee e1 where e1.salary>e.salary)

What you will notice in the correlated subquery above is that the inner subquery uses e.Salary, but the alias e is created in the outer query. This is why it is called a correlated subquery, because the subquery references a value in it’s WHERE clause (in this case, it uses a column belonging to e) that is used in the outer query.

Example

create table ##Emptemp(id int identity(1,1),Name Varchar(50),Dept varchar(50)) 

insert into ##Emptemp (name,dept) values ('Sendhil','ECE')

insert into ##Emptemp (name,dept) values ('Kumar','ECE')

insert into ##Emptemp (name,dept) values ('ARUN','EEE')

create table ##Deptemp(id int identity(1,1),DEP_Name Varchar(50)) 

insert into ##Deptemp (DEP_Name) values ('ECE')

insert into ##Deptemp (DEP_Name) values ('EEE')

select *  from ##Emptemp

select *  from ##Deptemp

Correlated Query:

select DEP_Name,(select COUNT(*) from ##Emptemp e where e.Dept=d.DEP_Name) TotalCount from ##Deptemp d

Uncorrelated Query:

select * from ##Emptemp e where e.Dept in (select d.DEP_Name  from ##Deptemp d )