오라클 서브쿼리

95qwer·2022년 5월 29일

참고)
Do it! 오라클로 배우는 데이터베이스 입문
https://mozi.tistory.com/233

서브쿼리란 ?

SQL문을 실행하는 데 필요한 데이터를 추가로 조회하기 위해 SQL문 내부에서 사용하는 SELECT문을 의미합니다.

서브쿼리 특징

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

특수한 몇몇 경우를 제외한 대부분의 서브쿼리에서는 ORDER BY절을 사용할 수 없습니다.

서브쿼리의 SELECT절에 명시한 열은 메인쿼리의 비교 대상과 같은 자료형과 같은 개수로 지정해야 합니다. 즉, 메인쿼리의 비교 대상 데이터가 하나라면 서브쿼리의 SELECT절 역시 같은 자료형인 열을 하나 지정해야 합니다.

서브쿼리에 있는 SELECT문의 결과 행 수는 함께 사용하는 메인쿼리의 연산자 종류와 호환 가능해야 합니다. 예를 들어 메인쿼리에 사용한 연산자가 단 하나의 데이터로만 연산이 가능한 연산자라면 서브쿼리의 결과 행 수는 반드시 하나여야 합니다.

사용 가능한 곳

SELECT 절

FROM 절

HAVING 절

ORDER BY 절

INSERT 문의 VALUES 절

UPDATE 문의 SET 절

기본 형식

SELECT 조회할 열
FROM 조회할 테이블
WHERE 조건 (
                   SELECT 조회할 열
                   FROM 조회할 테이블
                   WHERE 조건식
                  )

예시) JONES 사원보다 급여가 많은 사람을 찾아봅시다.

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

실행 결과가 하나인 단일행 서브쿼리

앞의 예시에서 사용한 서브쿼리는 대소 비교 연산자를 사용하였으므로 단일행 서브쿼리라 볼 수 있습니다. 다만, 위에서 근로자 이름 = 'JONES'로 조건을 걸어 단일행 서브쿼리를 사용하는 것은 문제가 될 수 있습니다. 동명이인이 있을 수 있습니다.
만약, JONES라는 이름을 가진 근로자가 여러 명 있었다면 대소 비교 연산자를 사용한 서브쿼리는 오류가 발생하고 실행되지 못합니다. 이처럼 서브쿼리의 결과로 여러 행을 반환할 때에는 다중생 서브쿼리를 사용해야 합니다.

단일행 서브쿼리와 함수

서브쿼리에서 특정 함수를 사용한 결과 값이 하나일 때, 단일행 서브쿼리로서 사용 가능합니다.
단일 행 비교 연산자(=, <, <=, >, >=, <>)와 함께 사용할 때는 서브쿼리의 결과 건 수가 반드시 1건 이하여야 합니다.

예시) 1번 부서에 속한 사원 중, 전체 사원의 평균 급여보다 낮은 급여를 받는 사원 정보를 조회하는 경우.

SELECT E.ENAME, E.SAL, D.DEPTNO
FROM EMP E, DEPT D
WHERE E.DETPNO = 20
AND E.DEPTNO = D.DEPTNO
AND E.SAL > (
                    SELECT AVG(SAL)
                    FROM EMP
                    )
;

실행 결과가 여러 개인 다중행 서브쿼리

다중행 서브쿼리는 실행 결과 행이 여러 개로 나오는 서브쿼리를 가리킵니다. 서브쿼리 결과가 여러 개이므로 단일행 연산자는 사용할 수 없고 다중행 연산자를 사용해야 메인쿼리와 비교할 수 있습니다.

다중행 연산자	설명
IN	메인쿼리의 데이터가 서브쿼리의 결과 중 하나라도 일치한 데이터가 있다면 TRUE
ANY, SOME	메인쿼리의 조건식을 만족하는 서브쿼리의 결과가 하나 이상이면 TRUE
ALL	메인쿼리의 조건식을 서브쿼리의 결과가 모두 만족하면 TRUE
EXISTS	서브쿼리의 결과가 존재하면, 즉 행이 1개 이상일 경우 TRUE

IN 연산자

예시) 각 부서별 최고 급여와 동일한 급여를 받는 사원 정보 출력하기
SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO
)
;

우선 서브쿼리 내용만 살펴보면,

	MAX(SAL)
>	2850
	3000
	5000

의 데이터가 뽑혔다고 가정해봅시다.
즉, 메인쿼리는 아래처럼 치환될 수 있습니다.
SELECT*
FROM EMP
WHERE SAL IN (2850, 3000, 5000)
;
세 값 중 일치하는 값을 가진 행만 출력합니다.

ANY, SOME 연산자

서브쿼리가 반환한 여러 결과 값 중, 메인쿼리와 조건식을 사용한 결과가 하나라도 TRUE라면 메인쿼리 조건식을 TRUE로 반환해주는 연산자입니다. --> OR 연산

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

위처럼 등가 비교 연산자(=)와 ANY / SOME 연산자를 함께 사용하면 IN 연산자와 정확히 같은 기능을 수행합니다. 다만, IN과 같은 효과를 내어야 할 때, ANY / SOME을 사용하는 경우는 거의 없습니다.
IN 연산자가 가독성이 훨씬 좋기 때문에 대부분 IN 연산자를 사용합니다.

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

ANY / SOME 연산자를 대소 비교 연산자와 함께 사용하는 경우는 생각해봐야 합니다.

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

서브 쿼리의 결과부터 살펴봅시다.

	SAL
>	1600
	1250
	1250
	2850
	1500
	900

여기서 ANY 연산자를 활용했으므로 위 쿼리된 값 중, 가장 큰 값인 2850보다 작은 SAL을 갖는 행은 모두 가져오게 됩니다.(단 하나만 TRUE가 되면, TRUE입니다.)
즉, 이렇게 생각할 수 있습니다.

SELECT SAL, EMPNO
FROM EMP
WHERE SAL < 2850
ORDER BY SAL, EMPNO
;

즉, < ANY 연산자는 서브쿼리 결과 중 급여의 최댓값보다 작은 값은 모두 출력 대상이 되는 것입니다. 따라서, < ANY 연산자의 조합은 서브쿼리에 MAX 함수를 적용한 값을 ANY 연산자 없이 비교 연산자(<)만 사용한 결과와 같은 효과를 낼 수 있습니다.

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

반대로 > ANY는 서브쿼리 결과 값 950, 1250, 1500, 1600, 2850 중 어떤 하나의 값보다 메인쿼리 데이터가 큰 값을 가지면 TRUE가 되어 결과 값이 출력됩니다.
즉, 950보다 높은 급여를 받는 사원은 모두 출력하게 됩니다.

ALL 연산자

ALL 연산자는 서브쿼리의 모든 결과가 조건식을 만족시켜야만 메인쿼리의 조건식이 TRUE가 되는 연산자입니다. 서브쿼리 결과 값 중 하나라도 조건식에 만족하지 않으면 FALSE입니다. (AND)

예시) 부서 번호가 30번인 사원들의 최소 급여보다 더 적은 급여를 받는 사원 출력하기

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

서브쿼리의 모든 결과 값 (950, 1250, 1500, 1600, 2850)보다 작은 값을 가진 메인쿼리의 행만 TRUE가 되어 출력됩니다. 즉 메인쿼리 값 중 950보다 작은 값을 가진 데이터만 결과 데이터로 출력됩니다.

반대로 > ALL 조합을 사용하는 경우, 서브쿼리 결과 값 중, 가장 큰 값인 2850과 비교하여 큰 값을 가진 데이터만 출력됩니다.

EXISTS 연산자

서브쿼리에 결과 값이 하나 이상 존재하면 조건식이 모두 TRUE, 존재하지 않으면 FALSE가 되는 연산입니다.

예시) 서브쿼리 결과 값이 존재하는 경우

SELECT SAL, EMPNO
FROM EMP
WHERE EXISTS       (
                           SELECT DNAME
                           FROM EMP
                           WHERE DEPTNO = 30
                           )
;
이 경우, EMP 테이블의 모든 데이터를 출력합니다.

만약 DEPT 테이블에 존재하지 않는 조건의 서브쿼리를 실행하면 결과 데이터로 아무 행도 출력하지 않습니다.