Difference between Correlated and Non-correlated Sub queries in SQL Server

Correlated Sub queries

A correlated sub query is a sub query that references columns from the outer (main) query. This means that the sub query executes once for each row processed by the outer query. The results of the sub query depend on the row currently being evaluated in the outer query.

See the example

We have creating two table and inserting some records into both table for this demo

create table depttbl ( deptid int primary key, deptname varchar(50) ) insert into depttbl(deptid,deptname) values (1,'IT'),(2,'HR'),(3,'Finance'),(4,'Admin')   create table emptble (empid int identity(1,1) primary key, ename varchar(50), salary decimal(18,10), deptid int) insert into emptble(ename,salary,deptid) values ('Bagesh',50000,1),('Rahul',80000,1), ('Sunil',30000,2),('Anil',20000,2), ('Ramesh',60000,3),('Suresh',8000,3), ('Ganesh',2000,4),('Mahesh',70000,4)

If we want to find employees whose salary is above the average salary of their department, we could use a correlated sub query as below

SELECT ename, Salary,deptid FROM emptble AS e WHERE Salary > (     SELECT AVG(Salary)     FROM emptble AS e2     WHERE e2.deptid = e.deptid );

See the result below

Non-Correlated Sub queries

A non-correlated sub query is independent of the outer query. It does not reference any columns from the outer query, and it can execute on its own without depending on the data from the outer query. A non-correlated sub query executes only once, and its results are used by the outer query.

For example, we want to find all employees who earn more than the overall average salary across all employees.

SELECT ename, Salary,deptid FROM emptble WHERE Salary > (SELECT AVG(Salary) FROM emptble);

See the output.