상관관계 서브쿼리

상관관계 서브쿼리
1. 외부 쿼리가 먼저 실행
2. 외부쿼리에서 사용된 테이블의 한 행이 내부 쿼리에서 사용되어 내부 쿼리가 실행됨
3. 2번 작업이 한 행씩 처리되어 마지막 행까지 처리된 후 다시 외부 쿼리가 최종 완료됨
--메인쿼리의 컬럼 중 하나가 서브쿼리의 조건에 이용되며, 서브쿼리의 결과는 메인쿼리로 반환되어 실행된다.

SELECT ENAME 직원, (SELECT ENAME FROM EMP
                             WHERE E.MGR=EMP.EMPNO) 상사
FROM EMP E;

--상관관계 서브쿼리는 메인 쿼리가 먼저 돈 다음 서브쿼리가 돈다
--메인쿼리에 있는 처음 한 행이 서브쿼리로 넘어가고 서브쿼리에 있는 WHERE절을 실행 그렇게 한 행이 실행되고
--최종적으로 완료됨
--서브쿼리에 메인쿼리를 이용한 조건절이 필요하다

--자신이 근무하는 부서의 평균 급여
SELECT EMPNO,ENAME,DEPTNO,(SELECT AVG(SAL) FROM EMP
                            WHERE E.DEPTNO=EMP.DEPTNO) 부서평균
FROM EMP E
ORDER BY DEPTNO;

--JOIN, 서브쿼리만 사용해서 검색
SELECT EMP.DEPTNO,EMP.ENAME,E2.SAL
FROM EMP
JOIN (SELECT DEPTNO,AVG(SAL) SAL FROM EMP GROUP BY DEPTNO) E2
ON EMP.DEPTNO=E2.DEPTNO
ORDER BY DEPTNO;

--자신이 근무하는 부서의 평균 급여보다 많이 받는 사원(상관관계)
SELECT * FROM EMP E
WHERE SAL>(SELECT AVG(SAL)
          FROM EMP WHERE E.DEPTNO=EMP.DEPTNO);

 

--JOIN,서브쿼리만 사용
SELECT * 
FROM EMP E
JOIN (SELECT DEPTNO,AVG(SAL) SAL2 FROM EMP GROUP BY DEPTNO) E2
ON E.DEPTNO=E2.DEPTNO
WHERE E.SAL>E2.SAL2;

--사원이 존재하는 부서정보(상관관계)
SELECT * FROM DEPT D
WHERE EXISTS(SELECT * FROM EMP
             WHERE D.DEPTNO=EMP.DEPTNO);

 

--JOIN,서브쿼리만 사용
SELECT * 
FROM DEPT D
JOIN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO) E
ON D.DEPTNO=E.DEPTNO
WHERE D.DEPTNO IN(E.DEPTNO);
---------------------------
SELECT * FROM DEPT D
WHERE DEPTNO IN(SELECT DISTINCT DEPTNO FROM EMP);