DataBase/SQL

SQL BOOSTER - 서브쿼리 (SELECT)

now0204 2024. 6. 23. 14:55

 

서브쿼리는 조인과 유사하면서도 조인보다 이해가 쉬운 장점이 있다.

 

서브쿼리를 익히기 전에, 서브쿼리는 성능이 좋지 못할 수 있다는 것을 알기 바란다.

SQL의 실행계획이 특정된 방법으로 제약될 가능성이 있기 때문이다. 

 

서브쿼리를 익힌 후에 가장 조심할 부분은 무분별한 서브쿼리 남발이다. 

모든 조인을 서비쿼리로 해결하려고 해서는 절대 안된다. 성능에 영향을 주지 않는 범위에서 적절할게 사용해야 한다.

 

> 서브쿼리는 사용 위치와 방법에 따라 다음 네 가지로 분류할 수 있다.

 

- SELECT 절의 단독 서브쿼리

- SELECT 절의 상관 서비쿼리

- WHERE 절의 단독 서브쿼리

- WHGER절의 상관 서브쿼리 

 

> 메인 SQL과 상관없이 실핼 할 수 있으면 단독 서브쿼리, 메인 SQL에서 값을 받아 처리해야 하면 상관 서브쿼리라고 한다.

> SELECT 절에서 사용되는 서브쿼리는 스칼라 서브쿼리라고 부른다. 

 

2. SELECT 절의 단독 서브쿼리 

 

SELECT절의 서브쿼리는 어려운 SQL을 해결하기에 가장 손쉬운 방법이다.

SELECT 절의 단독 서브쿼리는 SQL의 SELECT 절에 사용이 되며, 메인 SQL과 상관없이 단독으로 실행 가능한 서브쿼리를 뜻한다. 

 

SELECT TO_CHAR(T1.ORD_DT, 'YYYYMMDD') ORD_YMD, SUM(T1.ORD_AMT) ORD_AMT
,(SELECT SUM(A.ORD_AMT) FROM T_ORD A 
WHERE A.ORD_DT >= TO_DATE ('20170801','YYYYMMDD')
AND A.ORD_DT < TO_DATE('20170901','YYYYMMDD')
)TOTAL_ORD_AMT
FROM T_ORD T1
WHERE T1.ORD_DT >= TO_DATE('20170801','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170901','YYYYMMDD')
GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMMDD')

 

위 SQL에 결과 컬럼을 하나 더 추가해보자, 추가할 컬럼은 주문일자의 주문금액 비율이다.

주문금액 비율의 공식은 다음과 같다. 

 

주문금액 비율 = 주문일자 주문금액 합계 / 17년 8월의 총 주문 금액 * 100

 

주문금액 비율은 아래와 같이 서브쿼리를 구현할 수 있다. 

(절대 아래와 같은 패턴으로 SQL을 작성하면 안된다. T_ORD테이블을 불필요하게 반복 접근하기 때문)

SELECT TO_CHAR(T1.ORD_DT, 'YYYYMMDD') ORD_YMD, SUM(T1.ORD_AMT)ORD_AMT
,( SELECT SUM(A.ORD_AMT) 
	FROM T_ORD A
    WHERE A.ORD_DT >= TO_DATE('20170801','YYYYMMDD')
    AND   A.ORD_DT < TO_DATE('20170901','YYYYMMDD')
)TOTAL_ORD_AMT
,ROUND(
	SUM(T1.ORD_AMT) / 
    (SELECT SUM(A.ORD_AMT) FROM T_ORD A WHERE A.ORD_DT >= TO_DATE('20170801','YYYYMMDD')
    AND   A.ORD_DT < TO_DATE('20170901','YYYYMMDD')) * 100,2) ORD_AMT_RT
 FROM T_ORD T1
 WHERE A.ORD_DT >= TO_DATE('20170801','YYYYMMDD')
 AND   A.ORD_DT < TO_DATE('20170901','YYYYMMDD')
 GROUP BY TO_CHAR(T1.ORD_DT, 'YYYYMMDD');

 

  • 4~7번 라인과 11~14번 라인은 완전히 같은 서브쿼리이다. 17년 8월의 총 주문금액과 주문금액 비율을 구하기 위해 서브쿼리를 반복했다.
  • 같은 서브쿼리가 두 번이나 사용된 SQL은 성능에 문제도 있지만, SQL을 변경할 때 손이 많이 가는 번거로움도 있다. 
  • 위 SQL에서 총 주문금액의 기준이 바뀌면, 두 서브쿼리를 모두 변경해야 한다.
  • 위 SQL은 아래와 같이 서브쿼리를 하나만 남기고 인라인-뷰로 변경하는 것이 좋다. SQL이 훨씬 간단해졌다.
SELECT T1.ORD_YMD, T1.ORD_AMT, T1.TOTAL_ORD_AMT, ROUND(T1.ORD_AMT/T1.TOTAL_ORD_AMT*100,2) ORD_ATM_RT
FROM (
	SELECT TO_CHAR(T1.ORD_DT,'YYYYMMDD') ORD_YMD, SUM(T1.ORD_AMT)ORD_AMT,
    (SELECT SUM(A.ORD_AMT) FROM T_ORD A WHERE A.ORD_DT >= TO_DATE('20170801','YYYYMMDD')
    AND A.ORD_DT < TO_DATE('20170901','YYYYMMDD')
    )TOTAL_ORD_AMT
    FROM T_ORD T1
    WHERE T1.ORD_DT >= TO_DATE('20170801','YYYYMMDD')
    AND T1.ORD_DT < TO_DATE('20170901','YYYYMMDD')
    GROUP BY TO_CHAR(T1.ORD_DT, 'YYYYMMDD') 
)T1;

 

3. SELECT절의 상관 서브쿼리 

 

SELECT절의 상관 서브쿼리는 메인 SQL에서 조건 값을 받아 처리한다.

 

SELECT 절의 상관 서브쿼리는 코드성 데이터의 명칭을 가져오기 위해 사용할 수 있다. 또는 조인으로 가져오기 어려운 값을 처리하기 위해 사용할 수 있다.

 

SELECT절의 상관 서브쿼리를 이용하면 대부분의 조인을 해결할 수 있다. 하지만 모든 조인을 서브쿼리로 처리하는 것은 곤란하다. 

 

SELECT T1.ITM_TP
,(SELECT A.BAS_CD_NM FROM C_BAS_CD A
  WHERE A.BAS_CD_DV = 'ITEM_TP' AND A.BAS_CD = T1.ITM_TP AND A.LNG_CD ='KO') ITEM_TP_NM, 
  T1.ITEM_ID,T1.ITEM_NM
FROM M_ITEM T1;

 

서브쿼리에서 사용된 C_BAS_CD테이블은 기준코드 테이블이다. 4번 라인을 보면 메인 SQL의 T1.ITM_TP 값을 서브쿼리에서 조건 값으로 사용하고 있다. 

위 예제는 서브쿼리가 아닌 조인으로 해결할 수 있다. 하지만 코드 명칭을 가져오는 경우는 매우 자주 발생하며, 

이를 모두 조인으로 구현하면 SQL이 지저분해진다. 

코드명 처리는 조인보다는 SELECT절의 상관 서브쿼리를 사용하는 것이 일반적이다. 

코드처럼 값의 종류가 많지 않은 경우 서브쿼리를 사용하면 캐싱 효과로 성능이 좋아질 수도 있다.

 

* 서브쿼리 캐싱 효과

서브쿼리의 입력값과 결괏값을 캐시에 저장해 놓고 재사용하는 것을 뜻한다.

입력된 값이 캐시에 존재하면 서브쿼리의 실행 없이 캐시의 값을 그대로 사용해 빠른 응답 속도를 제공한다.

서브쿼리를 위해 사용할 수 있는 캐시는 무제한이 아니다. 코드와 같이 값의 종류가 작을 때만 캐싱 효과를 극대화할 수 있다. 

 


4. SELECT 절의 상관 서브쿼리를 사용할 때 주의할 패턴

 

4.1 반복되는 상관 서브쿼리 

 

아래 SQL은 조인으로 해결하는 것이 좋다.

 

SELECT T1.CUS_ID, TO_CHAR(T1.ORD_DT,'YYYYMMDD') ORD_YMD
        ,(SELECT A.CUS_NM FROM M_CUS WHERE A.CUS_ID = T1.CUS_ID) CUS_NM
        ,(SELECT A.CUS_GD FROM M_CUS WHERE A.CUS_ID = T1.CUS_ID) CUS_GD
        , T1.ORD_AMT
FROM T_ORD T1
WHERE T1.ORD_DT >= TO_DATE('20170801','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170901','YYYYMMDD')

 

위 SQL을 보면, 서브쿼리가 두 군데 사용되었다. 하나는 고객 이름을 가져오고 다른 하나는 고객등급을 가져온다. 

두 서브 쿼리 모두 고객(M_CUS)테이블을 사용하고 있으며, 메인 SQL에서 T1.CUS_ID를 입력값으로 받아 처리하고 있다.

이 같은 경우는 조인으로 변경해야 한다. 

불필요하게 M_CUS를 두 번이나 접근할 필요가 없다. 성능에서 손해 볼 가능성이 크다.

 

4.2 인라인-뷰가 포함된 SQL

 

SELECT 절의 상관 서브쿼리는 가능하면 인라인-뷰 바깥에서 사용해야한다.  

 

- 인라인-뷰 안에서 SELECT절 서브쿼리를 사용한 예

SELECT T1.CUS_ID
, SUBSTR(T1.ORD_YMD,1,6) ORD_YM
, MAX(T1.CUS_NM)
, MAX(T1.CUS_NM)
, MAX(T1.CUS_GD)
, T1.ORD_ST_NM, T1.PAY_TP_NM
,SUM(T1.ORD_AMT) ORD_AMT 

FROM ( 

SELECT T1.CUS_ID, TO_CHAR(T1.ORD_DT,'YYYYMMDD') ORD_YMD, T2.CUS_NM, T2.CUS_GD
		,(SELECT A.BAS_CD_NM FROM C_BAS_CD A WHERE A.BAS_CD_DV = 'ORD_ST' AND A.BAS_CD = T1.ORD_ST AND A.LNG_CD ='KO')
         ORD_ST_NM
        ,(SELECT A.BAS_CD_NM FROM C.BAS_CD A WHERE A.BAS_CD_DV ='PAY_TP' AND A.BAS_CD = T1.PAY_TP AND A.LNG_CD='KO')
         PAY_TP_NM
        , T1.ORD_AMT

FORM T_ORD T1, M.CUS T2
WHERE T1.ORD_DT >= TO_DATE ('20170801','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170901','YYYYMMDD')
AND T1.CUS_ID = T2.CUS_ID

)T1

GROUP BY T1.CUS_ID, SUBSTR(T1.ORD_YMD,1,6),T1.ORD_ST_NM, T1.PAY_TP_NM

 

SQL을 보면 코드명을 가져오는 SELECT 절 상관 서브쿼리가 인라인 뷰 안에서 사용되고 있다.

가져온 코드명은 인라인-뷰 바깥에서 GROUP BY 처리가 된다. 

 

인라인-뷰 안의 결과 건수는 1000건, 바깥 GROUP BY에 최종 결과가 100건이라면, 

인라인 뷰 안에서 상관 서브쿼리를 사용하면, 서브쿼리는 1000번 실행되며, 인라인뷰 바깥에서 사용하면 100번만 실행된다.

 

따라서 SELECT 절의 서브쿼리는 가장 바깥의 SELECT 절에만 사용하도록 노력하자 

 

4.3 서브쿼리 안에서의 조인

 

SELECT T1.ORD_DT, T2.ORD_QTY, T2.ITM_ID, T3.ITM_NM
, (
SELECT SUM(B.EVL_PT) / COUNT(*)
FROM M_ITM A, T_ITM_EVL B
WHERE A.ITM_TP = T3.ITM_TP
AND B.ITM_ID = A.ITM_ITM_ID
AND B.EVL_DT < T1.ORD_DT
) ITM_TP_EVL_PT_AVG
FROM T_ORD T1, T_ORD_DET T2
WHERE T1.ORD_DT >= TO_DATE('20170801','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170901','YYYYMMDD')
AND T3.ITM_ID = T2.ITM_ID
AND T1.ORD_SEQ = T2.ORD_SEQ
ORDER BY T1.ORD_DT, T2.ITM_ID;

 

2~7번 라인을 보면 서브쿼리 안에서 M_ITM과 T_ITM_EVL을 조인 처리하고 있다.

 

서브쿼리 안에서 조인 할 수 있다는 것은 매우 강력한 기능이다.

하지만 상관 서브쿼리는 성능에 대한 단점을 가진다.

기본적으로 상관서브쿼리는 메인 SQL의 결과 건수 만큼 반복 수행된다. 그러므로 상관 서브쿼리를 사용하는 것이 성능에 큰 손해를 보는 것인지 판단 후 사용해야한다. 

 

4.4 상관서브쿼리 가이드

 

- 상관 서브쿼리에서 사용되는 WHERE절의 컬럼은 INDEX가 필수다! 

   -> 인덱스가 있어도 성능이 안나오면 상관서브쿼리를 제거하라

- 메인 SQL의 결과가 작을 때만 사용한다.

- 코드처럼 값의 종류가 작을 때는 상관서브쿼리를 사용하는게 이득일 수 있다.

- 가능하면 상관서브쿼리를 사용하지말자 

   -> 조인을 사용하는 것이 SQL실력에 도움이 된다. 

5. SELECT 절 서브쿼리 - 단일 값 

 

SELECT 절의 서브쿼리는 단일 값을 내보내야 한다. 여기서 단일 값이란 하나의 로우 그리고 하나의 컬럼으로 구성된 단 하나의 값을 뜻한다. 

SELECT 절의 서브쿼리가 두건 이상의 결과를 내보내거나 두 개 컬럼 이상의 결과를 내보내면 안된다. 

*상관 서브쿼리면, 메인 SQL과의 조건을 통해 1개의 값만 나와야하고, 단독 서브쿼리면, 그냥 값이 하나가 나와야할 듯 하다 (상관 서브쿼리의 결과가 메인 조합과 하나려면, 보통 메인 SELECT에서 사용한 컬럼을 상관 서브쿼리에서 조건으로 사용하면 됨!)

 

아래 SQL은 실행이 불가능하다.

SELECT_T1.ORD_DT,T1.CUS_ID
,(SELECT A.CUS_NM,A.CUS_GD FROM M_CUS A WHERE A.CUS_ID = T1.CUS_ID) CUS_NM_GD 
FROM T_ORD T1
WHERE T1.ORD_DT >= TO_DATE('20170401', 'YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170501', 'YYYYMMDD');
--SELECT 절의 서브쿼리에서 두 건 이상의 데이터가 나오는 경우.
SELECT_T1.ORD_DT,T1.CUS_ID
,(SELECT A.ITM_ID FROM T_ORD_DET A WHERE A.ORD_SEQ = T1.ORD_SEQ) ITM_LIST
FROM T_ORD T1
WHERE T1.ORD_DT >= TO_DATE('20170401', 'YYYYMMDD')
AND T1.ORD_DT 〈 TO_DATE('20170501', 'YYYYMMDD');

 

아래와 같은 서브쿼리는 실행할 수 있다. 

SELECT T1.ORD_DT,T1.CUS_ID
,(SELECT A.CUS_NM ||'('||CUS_GD||')' FROM M_CUS A WHERE A.CUS_ID = T1.CUS_ID) CUS_NM_GD
,(SELECT SUM(A.UNT_PRC * A.ORD_QTY) FROM T_ORD_DET A WHERE A.ORD_SEQ = T1.ORD_SEQ) ORD_AMT 
From T_ORD T1
WHERE T1.ORD_DT >= TO_DATE('20170401', 'YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170501', 'YYYYMMDD');

 

위와 같이 SELECT 절의 서브쿼리 안에서 여러 개의 컬럼을 결합하거나 계산을 수행하는 것은 얼마든지 가능하다.

SELECT T1.CUS_ID, T1.CUS_NM, (SELECT 
                                TO_NUMBER(
                                   SUBSTR(
                                    MAX(
                                      LPAD(TO_CHAR(A.ORD_SEQ),8,'0') || TO_CHAR(A.ORD_AMT)
                                      ),9))
                               FROM T_ORD A 
                               WHERE A.CUS_ID = T1.CUS_ID) LAST_ORD_AMT
FROM M_CUS T1
ORDER B T1.CUS_ID;
  • 3~9번 라인을 보면 TO_NUMBER SUBSTRM MAX, LAPD, TO_CHAR의 함수가 복잡하게 사용되고 있다. 마지막 주문을 가져오려면 ORD_SEQ와 ORD_AMT를 문자로 변경해 결합한 다음 가장 큰 값을 가져와야 한다. 
  • 그러므로 다양한 함수를 복잡하게 사용해야만 한다. 위 SQL은 서브쿼리에서 고객의 모든 주문을 읽어야 하므로 성능이 좋지 못할 수 있다. 
SELECT T1.CUS_ID, T1.CUS_NM, (
                               SELECT B.ORD_AMT
                               FROM T_ORD B
                               WHERE B.ORD_SEQ = (SELECT MAX(A.ORD_SEQ) FROM T_ORD A WHERE A.CUS_ID=T1.CUS_ID
                               ) LAST_ORD_AMT
FROM M__CUS T1
ORDER BY T1.CS_ID;
  • 7번 라인을 보면 서브쿼리의 WHERE 절에서 서브쿼리를 추가로 사용하고 있다. SELECT 절의 서브쿼리는 조회되는 데이터 건수 만큼 반복 실행된다고 인식해야 한다. 그러므로 조회되는 결과 건수가 작을 경우에면 이와 같은 방법을 사용해야한다. 

5.1 잠재적인 오류가 존재하는 서브쿼리 

--잠재적인 오류가 존재하는 서브쿼리
SELECT T1.ORD_DT, T1.CUS_ID, 
(SELECT A.ORD_QTY FROM T_ORD_DET A WHERE A.ORD_SEQ = T1.ORD_SEQ)ORD_QTY
FROM T_ORD T1
WHERE T1.ORD_SEQ = 2297;
--오류 발생 상황 
SELECT T1.ORD_DT, T1.CUS_ID, 
(SELECT A.ORD_QTY FROM T_ORD_DET A WHERE A.ORD_SEQ = T1.ORD_SEQ)ORD_QTY
FROM T_ORD T1
WHERE T1.ORD_SEQ = 2291;
  • 2291의 데이터가 두 건이 있기 때문에 문제가 발생한다. 
  • 상관 서브쿼리 안에 조건은 한 건만 존재하도록 잘 조정해야한다. 

 

https://www.yes24.com/Product/Goods/82818767

 

SQL BOOSTER - 예스24

SQL BOOSTER는 프로젝트 성공을 위한 SQL 필독서다. 이 책은 마치 프로젝트를 진행하는 순서처럼 구성되어 있다. 프로젝트 투입을 위해 필요한 SQL 기술을 설명하고, 성능 테스트를 위해 필요한 기술

www.yes24.com