[오라클] Sub Query (서브쿼리 )

Sub Query

 

서브쿼리란 하나의 쿼리 안에 또 다른 하나의 쿼리가 있는 것입니다.

서브 쿼리는 연산자와 같은 비교 또는 조회 대상의 오른쪽에 놓이며 괄호 ( ) 로  묶어서 사용합니다.

FROM 절 안에 괄호()를 사용하여 서브쿼리를 기술하는 것을 인라인 뷰 라고 합니다.

서브 쿼리에 명시된 열은 메인 쿼리의 비교 대상과 동일한 데이터 타입(자료 형)과 동일한 열 개수를 가져야 합니다.

서브 쿼리에 사용할 연산자는 메인 쿼리에 사용된 연산자와 호환 가능해야 합니다.

특별한 경우(Top-n 분석 등)를 제외하고는 서브 쿼리에서 ORDER BY 절을 사용할 수 없습니다.

 

기본 사용 예시

SELECT ENAME,SAL
FROM EMP
WHERE SAL > ( SELECT SAL
			  FROM EMP
              WHERE EMPNO = 7782);

해석 : EMP 테이블에서 SAL(급여)이  테이블의 EMPNO가 7782인 사원 보다 높은 사원의 정보를
ENAME,SAL 컬럼으로만 출력한다는 의미입니다.

WHERE절 조건안에 ()를 사용해주고 한번 더 쿼리가 들어가는것을 서브쿼리라는것을 알 수 있습니다.

 

예제1

다음과 같이 사원 번호가 7782번인 사원보다 늦게 입사한 사원을 찾고,
각 사원의 부서명과 급여 등급을 함께 출력해 보세요.

- 단, 입사일을 기준으로 오름차순 정렬하세요

 

보면 HIREDATE(입사일) 을 기준으로 정렬이 되었고 81/09/08 이후에 입사한 사원들만 출력을 하였습니다.
급여등급도 함께 조회를 하였고 총 참조하는 테이블은 EMP, DEPT, SALGRADE 테이블입니다.
WHERE절 조건에 어떤 서브쿼리를 사용해야하고 JOIN을 어떻게 사용해야할지 알아보겠습니다.

SELECT DISTINCT E.ENAME, E.HIREDATE, D.DNAME, S.GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE HIREDATE > ( SELECT HIREDATE
            FROM EMP 
            WHERE EMPNO = 7782)
            AND E.SAL BETWEEN S.LOSAL AND S.HISAL
            AND E.DEPTNO = D.DEPTNO
            ORDER BY HIREDATE;

첫 행부터 설명하겠습니다.

SELECT : 총 조회할 컬럼들 ( 테이블 별칭 필수)

FROM : 참조할 테이블들 ( 테이블 별칭 생성 )

WHERE : HIREDATE > ( EMP테이블의 사번이 7782인 사원 ) 
=> EMP 테이블의 사번이 7782인 사원보다 입사날짜가 큰(늦게 들어온) 사원정보

AND 부분부터는 JOIN 문법입니다. 비등가조인, 등가조인 두개를 사용하였고 HIREDATE를 오름차순 정력하여 최종적으로 끝나게 됩니다.

동작 순서는 FROM => WHERE,AND,AND.... => SELECT => ORDER BY 순으로 실행이 됩니다. 
( FROM 안에 서브쿼리있으면 서브쿼리 내에서도 똑같은 순으로 진행.)

 

다중행 서브쿼리

실행 결과가 두 개 이상의 행으로 나오는 서브 쿼리 입니다.

서브 쿼리에서 출력되는 결과가 하나 이상이므로 메인 쿼리와 서브 쿼리의 결과는 별도의 연산자를 사용하여 비교합니다.

다중행 연산자

IN	메인 쿼리의 데이터가 서브 쿼리의 결과 중 하나라도 일치할 때 출력.
ANY	메인 쿼리의 조건식을 만족하는 서브 쿼리의 결과가 하나 이상일 때 출력.
ALL	메인 쿼리의 조건식을 서브 쿼리의 결과가 모두 만족할 때 출력.
EXISTS	서브 쿼리의 결과가 존재하면 출력.

 

IN

SELECT DEPTNO,EMPNO,ENAME,JOB,SAL
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
                FROM EMP
                GROUP BY DEPTNO)
ORDER BY DEPTNO;

서브쿼리 부분이 실행이되면 각 그룹별 (부서번호 10,20,30) 최대 급여의 정보가 나오게 됩니다. ( 2850,3000,5000 )
그리고  SAL이 저 3개의 값중에 하나라도 맞다면 그 행을 출력하게 됩니다.

 

ANY ( OR )

SELECT DEPTNO,EMPNO,ENAME,JOB,SAL
FROM EMP
WHERE SAL > ANY(SELECT SAL
                FROM EMP
                WHERE DEPTNO = 10)
ORDER BY DEPTNO DESC;

 

위의 코드블럭의 서브쿼리를 실행하게 되면 위와같은 결과가 나오게 됩니다 ( 부서번호 10인 사원들의 급여 정보)

전체 쿼리를 실행하게되면 위와 같은 결과가 나오게 됩니다. 
ANY 조건은 조건중에 하나라도 맞으면 결과가 조건이 참이 됩니다. 그래서 급여가 1300이상이거나,5000이상이거나
2450이상이거나 이 셋중에 하나라도 맞으면 출력되게됩니다 ( 1300 이상이면 맞는다고 생각하면 됩니다. )

ALL ( AND )

SELECT DEPTNO,EMPNO,ENAME,JOB,SAL
FROM EMP
WHERE SAL > ALL(SELECT SAL
                FROM EMP
                WHERE DEPTNO = 30)
ORDER BY DEPTNO DESC;

 

부서번호 30인 사번들의 SAL

 

전체쿼리

ALL 은 전체 조건이 다 맞을경우 참이 됩니다 ( AND 연산자라고 사용하면 펀함 )

그래서 SAL이 1600,1250,250,1500,950 보다 큰 모든 조건이 만족했을경우에만 출력합니다.
결국 2850 이상인 값만 출력되는것과 같습니다.

EXISTS

SELECT DEPTNO,EMPNO,ENAME,JOB,SAL
FROM EMP
WHERE EXISTS(SELECT SAL
                FROM EMP
                WHERE DEPTNO = 10)
ORDER BY DEPTNO DESC;

EXISTS는 값이 있을경우 다 출력하게 됩니다.

 

스칼라서브쿼리

SELECT 절에 () 안에 서브쿼리를 사용하고 컬럼마냥 사용하는것입니다.

앞서 했던 서브쿼리는 WHERE 조건절 안에 조건값으로 사용하려고 했다면 

스칼라 서브쿼리는 하나의 컬럼값처럼 사용하기위해 사용합니다.

 

사용 예시

SELECT E.DEPTNO,E.EMPNO,E.ENAME,E.JOB,E.SAL,E.MGR,E.COMM,
    (SELECT GRADE
    FROM SALGRADE 
    WHERE E.SAL BETWEEN LOSAL AND HISAL)AS SALGRADE,
    
    (SELECT D.DNAME
    FROM DEPT D
    WHERE E.DEPTNO = D.DEPTNO) AS DNAME
    
FROM EMP E
ORDER BY EMPNO;

위의 코드를 보면 SELECT 안에 ()에 서브쿼리가 들어가고 SALGRADE 테이블의 조건에 맞는 GRADE  컬럼자체를 하나의 테이블인것 처럼 조회를 할 수 있게 됩니다. ( 두번째 서브쿼리도 동일한 개념 )

빨간색 블록으로 처리한 부분의 두가지의 컬럼이 서브쿼리로 사용한 부분입니다.

 

 

WITH 절을 사용한 서브쿼리 ( 인라인 뷰 )

 

 

--FROM절 안에 서브쿼리 ( 인라인 뷰 )
SELECT D.DNAME, E20.EMPNO,E20.ENAME,E20.DEPTNO,D.LOC
FROM(SELECT * FROM EMP WHERE DEPTNO = 20)E20,
(SELECT * FROM DEPT) D
WHERE E20.DEPTNO = D.DEPTNO;

--WITH 
WITH
E20 AS (SELECT * FROM EMP WHERE DEPTNO = 20),
D AS (SELECT * FROM DEPT)
SELECT D.DNAME,E20.EMPNO,E20.ENAME,E20.DEPTNO,D.LOC
FROM E20,D
WHERE E20.DEPTNO = D.DEPTNO;
SELECT * FROM SALGRADE;

 

첫번째

FROM 절안에 서브쿼리를 사용하게 되면 스칼라 서브쿼리와 비슷하지만 이것은 테이블의 속성을 갖게 됩니다.
간단하게 해석을 해보겠습니다. (인라인뷰 첫번째 행부터)

SELECT : 조회할 컬럼들 ( 테이블 별칭 필수 )

FROM : ()안에 참조할 테이블과 조건(DEPTNO=20)을 지정해준 값들을 담은 테이블을 참조하고 테이블 별칭을 지정(여러 테이블사용 가능)

WHERE : JOIN 조건 사용

두번째

WITH를 사용하면 FROM절안에 사용한 서브쿼리를 최 상단에 ( SELECT 위에 ) 미리 선언을 다 해주고 별칭까지 지정해 준뒤 SELECT 부분부터는 JOIN을 사용하는것처럼 하면 됩니다. 
가독성의 문제로 각자 취향에 맞는방식을 사용하면 될것같습니다.