[SQLD] 서브 쿼리의 활용, Group by절, Merge 구문

이번 포스팅에서는 서브 쿼리의 활용과 Group by절, Merge 구문에 대해 다뤄보도록 하겠습니다. 이전 글에서 서브 쿼리 개념과 종류에 대해 알아봤다면 이번에 실제 활용은 어떻게 하는지를 살펴보겠습니다. 그리고 집계를 위한 Group by절의 활용, 마지막으로 Merge 구문은 Insert와 어떻게 다른지를 알아봅니다. 

 

 

Sub Query 활용

1. 서브 쿼리에서 그룹함수 사용

단일행을 반환하는 Sub Query에서 그룹함수를 사용할 수 있습니다. 메인 쿼리에서 그룹함수의 결과와 비교하여 데이터 출력이 가능합니다. 예를 들어, 주문금액 평균보다 큰 주문금액의 주문내역을 조회하는 SQL을 아래와 같이 작성할 수 있습니다. 

SELECT *
FROM   TB_ORD A
WHERE  A.TOT_ORD_AMT > (
                        SELECT AVG(B.TOT_ORD_AMT)
                        FROM   TB_ORG B
                        )
;                                            

2. 다중행을 반환하는 서브 쿼리

Sub Query에서 복수개의 행을 반환할 경우 서브쿼리간 연결자가 ‘=‘일 경우 오류가 발생합니다. 흔히들 실수하는 부분인데요. 다중행을 연결할 수 있는 IN과 같은 연산자로 SQL을 작성해야지 오류 없이 실행됩니다.

SELECT *
FROM   TB_ORD A
WHERE  A.CUST_NO IN (
                     SELECT CUST_NO 
                     FROM   TB_ORD
                     WHERE  SAL_CHARG_NO = 'E0000000000009'
                     )
;

 

3. 스칼라 서브 쿼리 활용

Scalar 서브 쿼리는 Select List Item 으로  사용합니다. 건건이 루프(Loop) 처리를 하기 때문에 결과 집합이 크면 성능이 느려지기 때문에 주위해야 합니다. Scalar 서브 쿼리 수행 결과는 반드시 하나의 Row의 하나의 Column 입니다. Scalar 서브 쿼리 결과 값이 0 row 이면 NULL 값이 리턴됩니다. 

SELECT  EMP_NO
      , EMP_NM
      , (SELECT ORG_NM 
         FROM   TB_ORG O 
         WHERE  O.MANG_NO = E.EMP_NO
        )
FROM   TB_EMP E
;

4. WITH절 서브 쿼리 활용

SQL에서 WITH절을 이용해 마치 뷰(VIEW)처럼 활용할 수 있습니다. WITH절을 사용하면 SQL문 내에서 해당 서브쿼리를 두번 부르게 되면 한번만 데이터를 불러서 처리하는 장점이 있습니다. 그리고 쿼리문이 간소화되는 측면도 있습니다. 물론 모든 서브쿼리를 WITH절로 만들어 사용할 필요는 없습니다. 

WITH W_EMP AS (SELECT CUST_NO 
    FROM   TB_EMP
    WHERE  SAL_VAL > 5000
    )
SELECT *
FROM   TB_ORD A, W_EMP B
WHERE  A.CUST_NO = B.CUST_NO
;

 

GROUP BY 절

 

Group by를 사용할 경우는 Select에는 아래와 같은 그룹 함수를 사용합니다. 그리고 Group by 뒤에는 그룹을 묶고자 하는 column이 옵니다. 아래 예제처럼 CUST_NO를 Group by절 뒤에 두면 CUST_NO(고객번호)로 그룹핑된 건수를 나타냅니다. 

Function	예      제	설  명
AVG	AVG(salary)	salary의 평균
MAX	MAX(salary)	salary의 최대값
MIN	MIN(salary)	salary의 최소값
SUM	SUM(salary)	salary의 합
C0UNT	COUNT(comm_pct) COUNT(*)	Comm_pct가 not null인 개수 전체 Row의 개수

 

HAVING절은 Group By절 다음에 나올 수 있습니다. Group BY된 결과 중 그룹함수 관련 조건에 맞는 결과만 조회 할 수 있게 제한합니다. 아래 예제에는 주문 건 중 2건 이상을 주문한 고객번호와 주문건수를 조회하는 SQL입니다. 

SELECT CUST_NO, COUNT(*) ORD_CNT
FROM   TB_ORD
GROUP BY CUST_NO
HAVING COUNT(*) > 2
;
 

 

MERGE 구문

 

Merge 구문은 알고보면 단순합니다. 추가하는 데이터가 테이블에 존재하지 않을 때는 INSERT가 수행되고, 이미 데이터가  존재할 경우에는 UPDATE가 수행하고자 할 때 사용합니다. 두가지 Action을 하나의 SQL로 처리할 수 있도록 제공하는 SQL 구문입니다. 

MERGE문장 작성 순서

1. 새로운 행이 추가되거나 갱신이 될 테이블을 지정
2. 다른 테이블을 참조하여 데이터를 비교한다면 대상 테이블 명을 별칭과 함께 정의
3. 새로운 행을 추가하려고 하는 테이블과 대상 테이블 간에 조인이 필요하면 ON절을 활용
4. 조인 조건에 의해 만족하는 데이터가 있을 경우엔 UPDATE가 수행, 만족하는 데이터가 없는 경우 INSERT문이 수행

CREATE TABLE TB_EMP_HIST
(
 EMP_NO        VARCHAR2(14) NOT NULL ,
 GRD           VARCHAR2(20) NULL ,
 BASI_HOM_ADDR VARCHAR2(200) NULL ,
 DETL_HOM_ADDR VARCHAR2(200) NULL ,
 HOM_TEL       VARCHAR2(14) NULL ,
 CEL_TEL       VARCHAR2(14) NULL ,
 EMAIL         VARCHAR2(200) NULL ,
 JOIN_DT       VARCHAR2(8) NULL ,
 SAL           NUMBER NULL 
);
 
 
MERGE INTO TB_EMP_HIST EH
USING TB_EMP E
   ON (E.EMP_NO = EH.EMP_NO)
WHEN MATCHED THEN
 UPDATE SET EH.GRD           = E.GRD
          , EH.SAL           = E.SAL
          , EH.BASI_HOM_ADDR = E.BASI_HOM_ADDR
          , EH.DETL_HOM_ADDR = E.DETL_HOM_ADDR
          , EH.HOM_TEL       = E.HOM_TEL
          , EH.CEL_TEL       = E.CEL_TEL
          , EH.EMAIL         = E.EMAIL
          , EH.JOIN_DT       = E.JOIN_DT
WHEN NOT MATCHED THEN
 INSERT VALUES ( E.EMP_NO
               , E.GRD
               , E.BASI_HOM_ADDR
               , E.DETL_HOM_ADDR
               , E.HOM_TEL
               , E.CEL_TEL
               , E.EMAIL
               , E.JOIN_DT
               , E.SAL 
              )
;