[SQL] Oracle, 서브쿼리

서브쿼리

: SELECT문의 결과를 WHERE절에서 사용하고 싶을 때 사용

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

-단일행 서브쿼리

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);

• IN
• ANY, SOME: 메인쿼리와 조건식을 사용한 결과가 하나라도 true라면 true로 반환
• ALL: 서브쿼리의 모든 결과가 조건식에 맞아떨어져야만 메인쿼리의 조건식이 true가 된다.
• EXISTS: 서브쿼리에 결과값이 하나 이상 존재하면 조건식이 모두 true가 되는 연산자

 

 

SELECT *
FROM EMP
WHERE SAL < ANY(SELECT SAL 
                FROM EMP
                WHERE DEPTNO = 30)
ORDER BY SAL ,EMPNO;

SELECT *
FROM EMP
WHERE SAL < ALL(SELECT SAL 
                FROM EMP
                WHERE DEPTNO = 30)
ORDER BY SAL ,EMPNO;

-다중열 서브쿼리

SELECT *
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL)
                        FROM EMP
                        GROUP BY DEPTNO);

-FROM절에 사용하는 서브쿼리, WITH절: 인라인 뷰

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
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 SALGRADE
    , DEPTNO
    , (SELECT DNAME FROM DEPT WHERE E.DEPTNO = DEPT.DEPTNO) AS DNAME
FROM EMP E;

 

-종합예제

--1
SELECT E.JOB, E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
    AND E.JOB = (SELECT JOB FROM EMP WHERE ENAME = 'ALLEN');
    
--2
SELECT E.EMPNO, E.ENAME, D.DNAME, E.HIREDATE, D.LOC, E.SAL, S.GRADE 
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO = D.DEPTNO
    AND E.SAL BETWEEN S.LOSAL AND S.HISAL
    AND E.SAL>(SELECT AVG(SAL) FROM EMP)
ORDER BY E.SAL DESC, E.EMPNO;

--3
SELECT E.EMPNO, E.ENAME, E.JOB, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
    AND E.DEPTNO  = 10
    AND E.JOB NOT IN (SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 30);
    
--4
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE 
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
    AND SAL > ALL(SELECT SAL FROM EMP WHERE JOB = 'SALESMAN')
    --MAX함수 사용 가능
ORDER BY E.EMPNO;