SQL - 서브쿼리(SELECT문)

-- 서브쿼리
-- 대부분의 서브쿼리에서는 ORDER BY절을 사용할 수 없다.
-- 서브쿼리는 메인쿼리의 비교 대상과 같은 자료형과 같은 개수로 지정해야 한다.

-- SELECT문
SELECT *
FROM EMP
WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'JONES');

SELECT *
FROM EMP
WHERE HIREDATE < (SELECT HIREDATE FROM EMP WHERE ENAME = 'MARTIN');

-- 20번 부서의 사원 중 평균 급여보다 높은 급여를 받는 사원 정보와 소속 부서 정보 조회
SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E,
     DEPT D
WHERE E.DEPTNO = D.DEPTNO
  AND E.DEPTNO = 20
  AND E.SAL > (SELECT AVG(SAL) FROM EMP);

-- 각 부서별 최고 급여를 받는 사원 조회
SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

SELECT *
FROM EMP
WHERE SAL = SOME (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

-- 30번 부서의 최대 급여보다 적은 급여를 받은 사원 조회
SELECT *
FROM EMP
WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30)
ORDER BY SAL, EMPNO;

-- 30번 부서의 최소 급여보다 적은 급여를 받은 사원 조회
SELECT *
FROM EMP
WHERE SAL < ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30);

-- 서브쿼리 결과 값이 존재하는 경우
SELECT *
FROM EMP
WHERE EXISTS(SELECT DNAME FROM DEPT WHERE DEPTNO = 10);

-- 다중열 서브쿼리
SELECT *
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO);

-- 인라인 뷰: FROM절에 사용하는 서브쿼리
SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM (SELECT * FROM EMP WHERE DEPTNO = 10) E10,
     (SELECT * FROM DEPT) D
WHERE E10.DEPTNO = D.DEPTNO;

-- WITH절
WITH E10 AS (SELECT * FROM EMP WHERE DEPTNO = 10),
     D AS (SELECT * FROM DEPT)
SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM E10,
     D
WHERE E10.DEPTNO = D.DEPTNO;

-- 스칼라 서브쿼리: SELECT절에 사용하는 서브쿼리
SELECT EMPNO,
       ENAME,
       JOB,
       SAL,
       (SELECT GRADE FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL) AS SALARADE,
       DEPTNO,
       (SELECT DNAME FROM DEPT WHERE E.DEPTNO = DEPT.DEPTNO)            AS DNAME
FROM EMP E;