Hi Programmers,welcome to new article of ozanecare. this article i’ll write the queries for correlated subquery using Microsoft sql server. The 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 and
subquery depends on the outer query for its value.




Directly Test Queries into Editor.
-- Non-Correlated subquery
SELECT * FROM EMP
WHERE SAL IN ( SELECT SAL FROM EMP
WHERE SAL BETWEEN 3000 AND 5000);
--Correlated subquery
SELECT * FROM EMP E1
WHERE SAL > ( SELECT AVG(SAL) FROM EMP E2
WHERE E2.DEPTNO =E1.DEPTNO
GROUP BY E2.DEPTNO);
-- Non-Correlated subquery
SELECT * FROM EMP E1,
( SELECT DEPTNO,AVG(SAL) SAL1 FROM EMP
GROUP BY DEPTNO) E2
WHERE E2.DEPTNO =E1.DEPTNO
AND E1.SAL > E2.SAL1;
Happy Programming…Thanks.