서브 쿼리

쿼리 블록은 다른 쿼리 블록을 포함할 수 있다. 다른 쿼리 블록에 포함된 쿼리 블록을 서브 쿼리, 다른 쿼리 블록을 포함한 쿼리 블록을 메인 쿼리라고 한다.

# 예) 급여가 평균 이상인 사원 조회
SELECT ename, sal
FROM emp
WHERE sal >= (SELECT AVG(sal) FROM emp);

서브 쿼리는 사용 위치에 따라 분류된다.

중첩 서브쿼리 (nested subquery) : WHERE 절, HAVING절
스칼라 서브 쿼리 (scalar subquery) : SELECT 절
인라인 뷰 (inline view) : FROM 절

12.1 중첩 서브 쿼리

중첩 서브쿼리는 메인 쿼리와의 상관 관계가 없으면 비상관 서브쿼리, 상관 관계가 있으면 상관 서브쿼리로 구분할 수 있다. 그리고 서브 쿼리가 반환하는 행의 개수에 따라 단일 행 서브 쿼리와 다중 행 서브쿼리로도 구분할 수 있다.

12.1.1 비상관 서브 쿼리

서브 쿼리의 WHERE 절에 메인 쿼리와의 조인 조건이 존재하지 않는다. 서브 쿼리가 반환하는 행의 개수에 따라 단일 행 비상관 서브 쿼리와 다중 행 비상관 서브 쿼리로 구분할 수 있다.

단일 행 비상관 서브 쿼리

# 단일 행
SELECT *
FROM t1
WHERE c1 = (SELECT MAX(c1) AS c1 FROM t2);

# 다중 열 서브쿼리
SELECT *
FROM t1
WHERE (c1, c2) = (SELECT MAX(c1), MAX(c2) FROM t2);

다중 행 비상관 서브 쿼리

# IN 조건
SELECT *
FROM t1
WHERE c1 IN (SELECT c1 FROM t2);

# NOT IN 조건
SELECT *
FROM t1
WHERE c1 NOT IN (SELECT c1 FROM t2); -- 열의 값에 null이 있는 경우 결과가 반환되지 않는다. AND NULL은 항상 UNKNOWN이기 때문에 결과가 반환되지 않는다.

# ANY(일부), ALL(전체)
SELECT *
FROM t1
WHERE c1 > ANY(SELECT c1 FROM t2);

12.1.2 상관 서브 쿼리

메인 쿼리와 관계가 있는 서브 쿼리로 서브 쿼리의 WHERE 절에 메인 쿼리와의 조인 조건이 존재한다.

단일 행 상관 서브 쿼리

SELECT a.*
FROM t1 a
WHERE a.c2 = (SELECT MAX(x.c2)
              FROM t2 x
              WHERE x.c1 = a.c1); -- 메인 쿼리와 조인 조건

다중 행 상관 서브 쿼리

# EXISTS 조건 : 서브 쿼리에 존재하는 행 반환
SELECT a.*
FROM t1 a
WHERE EXISTS (SELECT 1 FROM t2 x WHERE x.c1 = a.c1);

# NOT EXISTS 조건 : 서브 쿼리에 존재하지 않는 행 반환
SELECT a.*
FROM t1 a
WHERE NOT EXISTS (SELECT 1 FROM t2 x WHERE x.c1 = a.c1);

12.1.3 사용 기준

행	비상관 서브 쿼리	상관 서브 쿼리
단일 행	비교 조건	비교 조건
다중 행	IN, NOT IN	EXISTS, NOT EXISTS

# 단일 행 비상관 서브 쿼리 : 메인 쿼리에 단일 값을 입력할 때 사용 예) MAX(), = 등호조회
SELECT 고객번호, 고객명
FROM 고객
WHERE 고객번호 = (SELECT MAX(주문고객번호) KEEP (DENSE_RANK FIRST ORDER BY 주문일자 DESC) FROM 주문);

# 단일 행 상관 서브 쿼리 : 메인 쿼리의 열을 서브 쿼리의 결과와 비교할 때 사용
SELECT a.고객번호, b.주문번호, b.주문일자
FROM 개인고객 a, 주문 b
WHERE b.주문고객번호 = a.고객번호
AND b.주문일자 = (SELECT MAX(x.주문일자) FROM 주문 x WHERE x.주문고객번호 = a.고객번호);

# 다중행 서브쿼리 : IN 조건(서브쿼리 먼저조회 -> 메인쿼리에 값 공급), EXISTS 조건(메인쿼리 먼저 조회 -> 서브쿼리로 존재여부 확인)

12.2 스칼라 서브 쿼리

스칼라 서브쿼리는 SELECT 절에 사용하는 서브 쿼리다. 스칼라는 단일 값을 의미한다. 따라서 다중 행이 반환되며 에러가 발생한다.

SELECT a.c1, (SELECT MAX(x.c2) FROM t2 x WHERE x.c1 = a.c1) AS c2
FROM t1 a;

12.3 인라인 뷰

인라인 뷰는 FROM 절에서 사용하는 서브 쿼리이다. 인라인 뷰는 쿼리에서 즉시 처리되는 뷰를 의미한다.

SELECT a.dname, b.ename
FROM (SELECT * FROM dept WHERE loc = 'DALLAS') a
   , (SELECT * FROM emp WHERE job = 'CLERK') b
WHERE b.detno = a.deptno;

12.4 사용 기준

조인

조인은 테이블을 연결하는 기본 방식이다. 조인 기준의 행이 줄어들거나 늘어날 수 있다.

중첩 서브 쿼리

중첩 서브 쿼리는 서브 쿼리로 메인 쿼리의 결과 집합을 제한할 때 사용한다. EXISTS 조건은 조인되는 값의 종류가 적고, 서브 쿼리 테이블의 크기가 클 때 유용하다. 메인 쿼리와 서브 쿼리의 조인 차수가 1:M일 때 사용하는 것이 일반적이다.

NOT EXISTS 조건은 메인 쿼리와 서브 쿼리의 조인 차수에 관계 없이 모두 사용할 수 있다. 아우터 조인으로도 작성 가능하다 가독성 측면에서는 NOT EXISTS 조건을 사용하는 편이 바람직 하다.

스칼라 서브 쿼리

스칼라 서브쿼리는 서브 쿼리로 단일 값을 조회할 때 사용한다. 스칼라 서브 쿼리는 조인되는 값의 종류가 적고, 서브 쿼리 테이블의 크기가 클 때 유용하다. (1:M일때 사용)그렇지 않다면 인라인 뷰로 변경하는 편이 성능 측면에서 유용하다.

인라인 뷰

복합 뷰는 인라인 뷰로 새로운 결과 집합을 만들거나 조인 차수를 1:1 관계로 만들 때 사용한다. 단순 뷰는 조인 순서를 제어하거나 반복되는 표현식을 제거할 때 사용할 수 있다.

12.5 WITH 절

WITH 절을 사용하면 서브 쿼리를 별도의 절(SUBQUERY FACTORING 절)에 기술할 수 있다.

12.5.1 SUBQUERY FACTORING 절

이 절을 사용하면 서브 쿼리에 이름을 부여하고, 이름이 부여된 서브 쿼리를 메인 쿼리에서 반복 사용할 수 있다. WITH query_name AS (subquery) ... SELECT ...

WITH w1 AS (SELECT deptno, SUM(sal) AS sal FROM emp GROUP BY deptno)
SELECT a.deptno, b.dname, a.sal
FROM w1 a, dept b
WHERE b.deptno = a.deptno;

WITH w1 AS (SELECT deptno, SUM(sal) AS sal FROM emp GROUP BY deptno)
   , w2 AS (SELECT SUM(sal) AS sal FROM w1)
SELECT a.deptno, a.dname, b.sal, (b.sal / c.sal) * 100 AS rt
FROM dept a, w1 b, w2 c
WHERE b.deptno = a.deptno;

12.5.2 PL/SQL 선언

12.1 버전부터 WITH 절애 PL/SQL 함수와 프로시저를 선언할 수 있다.

12.6 신규 기능

12.6.1 LATERAL 인라인 뷰

LATERAL 인라인 뷰를 사용하면 인라인 뷰에 메인 쿼리의 열을 기술할 수 있다. LATERAL 인라인 뷰 뒤에 (+) 기호를 사용하면 아우터 조인으로 조인된다.

12.6.2 CROSS APPLY 절

CROSS APPLY 절을 사용하면 LATERAL 인라인 뷰의 이너 조인과 결과가 동일하다.

12.6.3 OUTER APPLY 절을 사용할 수 있다.

OUTER APPLY 절은 LEFT OUTER JOIN의 변형이다.

12.6.4 기존 동작 변화

12.1 버전 이전에 서브 쿼리에 메인 쿼리의 조건을 기술하면 에러가 발생했으나 이후 버전부터는 에러가 발생하지 않는다. 스칼라 서브쿼리도 서브쿼리에 메인 쿼리의 조건을 기술할 수 있다.

12. 서브 쿼리