[SQLD] SQL 활용 - 서브쿼리(Subquery)

서브쿼리

서브 쿼리란 하나의 SQL문 안에 또 다른 SQL문을 말한다. 즉 SELECT문 내에 다시 SELECT문 사용하는 형태의 SQL문이다. 서브쿼리 밖에 있는 SELECT문은 메인 쿼리라고 한다.

서브쿼리 형태

• 일반적인 서브 쿼리 - WHERE절
• 스칼라 서브 쿼리 - SELECT 절
• 인라인 뷰 - FROM 절

서브 쿼리 특징

1. 괄호로 감싸서 사용한다.
2. 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다.
   • 단일 행 비교 연산자 : =, ≥, ≤, >,<
   • 복수 행(다중 행) 비교 연산자: IN, ANY(SOME), ALL, EXISTS
3. 서브 쿼리에는 ORDER BY를 사용할 수 없다.
   • ORACLE INLINE VIEW에서는 가능(FROM절)
4. WHERE / SELECT / FROM / HAVING / ORDER BY / INSERT VALUES / UPDATE SET 에서 사용 가능하다.

단일 행 서브쿼리와 다중 행 서브쿼리

서브쿼리는 반환하는 행 수가 한 개인 것과 여러 개인 것에 따라서 단일 행 서브쿼리와 다중 행 서브쿼리로 분류한다. 

 

단일 행 서브쿼리는 단 하나의 행만 반환하는 서브쿼리로 비교 연산자 ( <=, <, >=, >, <> )를 사용한다.

다중 행 서브쿼리는 여러 개의 행을 반환하는 것으로 IN, ANY, ALL, EXISTS를 사용한다.

 

단일 행 서브쿼리

SELECT * 
FROM DEPT d 
WHERE DEPTNO = (
	SELECT DEPTNO 
	FROM EMP e 
	WHERE e.ENAME = 'SMITH'
)

다중 행 서브 쿼리

IN

메인 쿼리의 비교조건이 서브쿼리의 결과 중 하나만 동일하면 참

SELECT 
	p.PRODUCT_ID,
	p.PRODUCT_NAME 
FROM PRODUCTS p 
WHERE PRODUCT_ID IN (
	SELECT PRODUCT_ID 
	FROM ORDER_ITEMS
)

    -> 서브 쿼리 안의 출력 컬럼은 반드시 단일 컬럼이어야 한다

 

    다중 컬럼 서브 쿼리

SELECT *
FROM EMPLOYEES e 
WHERE (MANAGER_ID, JOB_TITLE) IN (
	('9', 'Accountant'), ('4', 'Programmer')
)

    -> 컬럼 수를 맞춰 주어야 한다.

    -> 가장 안쪽 소괄호 사이에는 AND, 바깥 소괄호 쉼표 사이에는 OR이 생략되어 있다.

 

 

ALL

메인 쿼리와 서브 쿼리의 결과가 모두 동일하면 참

    <ALL : 최솟값 반환

    >ALL : 최댓값 반환

SELECT *
FROM EMP e 
WHERE DEPTNO >= ALL(20,30) -- 20과 30보다 큰 

 

ANY

메인 쿼리의 비교조건이 서브쿼리의 결과 중 하나 이상 동일하면 참

    <ANY : 하나라도 크게 되면 참

    >ANY : 하나라도 작게 되면 참

SELECT * 
FROM EMP e 
WHERE DEPTNO  = ANY(10, 20)
--WHERE DEPTNO  >= ANY(10, 20) 형태도 사용 가능

 

EXISTS

메인 쿼리와 서브 쿼리의 결과가 하나라도 존재하면 참

SELECT 
	p.PRODUCT_ID,
	p.PRODUCT_NAME 
FROM PRODUCTS p 
WHERE EXISTS ( -- 부정 연산자 NOT 사용 가능 WHERE NOT EXISTS
	SELECT 1 --컬럼 의미 없음
	FROM ORDER_ITEMS oi --AS생략가능
	WHERE oi.PRODUCT_ID = p.PRODUCT_ID 
)

 

스칼라 서브쿼리 ( Scala Subquery)

SELECT문에서 사용하는 서브쿼리로 반드시 단일 값 단일행만 반환하는 서브쿼리이다. 여러 행이 반환될 시 오류가 발생한다.

 

비연관 서브 쿼리

SELECT 
	PRODUCT_ID,
	PRODUCT_NAME,
	(SELECT TRUNC(AVG(p2.LIST_PRICE)) FROM PRODUCTS p2  WHERE p2.CATEGORY_ID =1) AS C_AVG
FROM PRODUCTS p

• 서브 쿼리가 메인 쿼리와 연관이 없다

연관 서브 쿼리

SELECT 
	PRODUCT_ID,
	PRODUCT_NAME,
	CATEGORY_ID, 
	(SELECT TRUNC(AVG(p2.LIST_PRICE)) FROM PRODUCTS p2 WHERE p2.CATEGORY_ID = p.CATEGORY_ID) AS C_AVG
FROM PRODUCTS p

인라인 뷰 ( Inline View)

FROM절에서 사용되는 서브쿼리로 SQL이 실행될 때 메모리에 가상으로 올라가는 테이블이다. 가상의 테이블이기 때문에 DB에 정보가 저장되지 않는다. 또한 테이블로 볼 수 있기 때문에 괄호로 감싼뒤 별칭 사용이 가능하다.

 

예시코드 - 가상의 테이블 E_TMP와 조인해 부서별 최소 연봉 데이터 추출

SELECT 
	E.ENAME,
    E_TMP.SAL_MIN 
FROM EMP E 
INNER JOIN( 
	SELECT 
    	DEPTNO, 
        MIN(SAL) AS SAL_MIN 
    FROM EMP 
    GROUP BY DEPTNO 
) E_TMP ON E.SAL = E_TMP.SAL_MIN

 

인라인 뷰는 ROWNUM  사용 시 많이 작성한다. 페이징 쿼리도 ROWNUM을 사용하므로 인라인 뷰를 사용한다.

 

ROWNUM

SELECT ROWNUM, E.*
FROM EMP E
ORDER BY HIREDATE DESC
--결과를 보면 ROWNUM이 뒤죽박죽으로 나온다

SELECT ROWNUM, TMP.*
FROM (
	SELECT *
	FROM EMP e 
	ORDER BY HIREDATE DESC
) TMP
--ROWNUM이 순서대로 나옴

Paging Query

1페이지 출력
SELECT ROWNUM AS RN, TMP.*
	FROM (
		SELECT *
		FROM EMP e 
		ORDER BY HIREDATE DESC
	) TMP
WHERE ROWNUM BETWEEN 1 AND 5
----6 AND 10은 출력 할 수 없음

SELECT *
FROM (
	SELECT ROWNUM AS RN, TMP.*
	FROM (
		SELECT *
		FROM EMP e 
		ORDER BY HIREDATE DESC
	) TMP
) TMP2
WHERE TMP2.RN BETWEEN 6 AND 10

1. 정렬된 데이터를 먼저 뽑음

2. 만든 데이터를 인라인뷰로 만들어 ROWNUM을 뽑음

3. WHERE절에서 ROWNUM을 사용할 수 없음(오라클이 임의로 발급한 상태)

4. 사용하기 위해 전체로 한번 더 감싸줘 ROWNUM 사용 가능 (ROWNUM이 붙어 있는 상태의 데이터) - 별칭으로 사용하기