28 OCT

Correlated Subquery in SQL Server

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;