본문 바로가기
데이터베이스/ORACLE

[Oracle] 조회된 ROW가 없을 때, RETURN 하는 방법

산을넘는다 2020. 8. 31. 04:04 

SELECT SUBQUERY1.NUM AS "1월", SUBQUERY2.NUM AS "2월", SUBQUERY3.NUM AS "3월", SUBQUERY4.NUM AS "4월",
        SUBQUERY5.NUM AS "5월", SUBQUERY6.NUM AS "6월", SUBQUERY7.NUM AS "7월", SUBQUERY8.NUM AS "8월"
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-01') SUBQUERY1,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-02') SUBQUERY2,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-03') SUBQUERY3,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-04') SUBQUERY4,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-05') SUBQUERY5,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-06') SUBQUERY6,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-07') SUBQUERY7,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-08') SUBQUERY8;

 

테이블에는 1월부터 8월까지의 로우만 있었다.

그래서 9월을 조회했을 때 반환되는 행이 없었던 것이다.

이때 아래처럼 결과가 나오질 않았다.

 

그러던 중 블로그를 참고하여 해결함.

SELECT SUBQUERY1.NUM AS "1월", SUBQUERY2.NUM AS "2월", SUBQUERY3.NUM AS "3월", SUBQUERY4.NUM AS "4월",
        SUBQUERY5.NUM AS "5월", SUBQUERY6.NUM AS "6월", SUBQUERY7.NUM AS "7월", SUBQUERY8.NUM AS "8월", SUBQUERY9.NUM AS "9월"
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-01') SUBQUERY1,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-02') SUBQUERY2,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-03') SUBQUERY3,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-04') SUBQUERY4,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-05') SUBQUERY5,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-06') SUBQUERY6,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-07') SUBQUERY7,
      
      (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-08') SUBQUERY8,
      
     --(SELECT NVL(SUM(CARD_TRANSACTION_AMOUNT), 0) AS NUM
     --(SELECT SUM(NVL(CARD_TRANSACTION_AMOUNT, 0)) AS NUM
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-09') SUBQUERY9;

 

SELECT SUBQUERY1.NUM AS "1월", SUBQUERY2.NUM AS "2월", SUBQUERY3.NUM AS "3월", SUBQUERY4.NUM AS "4월",
        SUBQUERY5.NUM AS "5월", SUBQUERY6.NUM AS "6월", SUBQUERY7.NUM AS "7월", SUBQUERY8.NUM AS "8월", SUBQUERY9.NUM AS "9월"
FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-01') SUBQUERY1,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-02') SUBQUERY2,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-03') SUBQUERY3,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-04') SUBQUERY4,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-05') SUBQUERY5,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-06') SUBQUERY6,
      
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-07') SUBQUERY7,
      
      (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-08') SUBQUERY8,
      
     --(SELECT NVL(SUM(CARD_TRANSACTION_AMOUNT), 0) AS NUM
     --(SELECT SUM(NVL(CARD_TRANSACTION_AMOUNT, 0)) AS NUM
     (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-09'
      
      UNION ALL
      
      SELECT NULL AS NUM
      FROM DUAL
      WHERE NOT EXISTS(SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-09')
      
      ) SUBQUERY9;

반환되는 로우가 없으면 0을 받고싶었기에,

 UNION ALL
      
      SELECT 0 AS NUM
      FROM DUAL
      WHERE NOT EXISTS(SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
      FROM CARD_TRANSACTION_TEST01
      GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
      HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-09')

 

아래의 블로그에서 추천하는 방법 2.

그런데 이렇게 하니

여러개의 로우가 반환됨. 언제 로우가 반환되지 않는지 알때 쓰는건가.. 

아니면 블로그에 기재되지 않은 부분이 있었던가 둘 중 하나겠지.

 

SELECT SUBQUERY1.NUM AS "1월", SUBQUERY2.NUM AS "2월", SUBQUERY3.NUM AS "3월", SUBQUERY4.NUM AS "4월",
        SUBQUERY5.NUM AS "5월", SUBQUERY6.NUM AS "6월", SUBQUERY7.NUM AS "7월", SUBQUERY8.NUM AS "8월",
        SUBQUERY9.NUM AS "9월", SUBQUERY10.NUM AS "10월", SUBQUERY11.NUM AS "11월", SUBQUERY12.NUM AS "12월"
FROM (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-01'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY1,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-02'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY2,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-03'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY3,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-04'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY4,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-05'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY5,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-06'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY6,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-07'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY7,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-08'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY8,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-09'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY9,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-10'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY10,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-11'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY11,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-12'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY12;

 

TOP N 쿼리를 적용해서 맨 위의 로우만 반환하게 했음.

이렇게 ROWNUM 쓰는 방법과 맨 위의 방법 속도차이 비교해보자

SELECT SUBQUERY1.NUM AS "1월", SUBQUERY2.NUM AS "2월", SUBQUERY3.NUM AS "3월", SUBQUERY4.NUM AS "4월",
        SUBQUERY5.NUM AS "5월", SUBQUERY6.NUM AS "6월", SUBQUERY7.NUM AS "7월", SUBQUERY8.NUM AS "8월",
        SUBQUERY9.NUM AS "9월", SUBQUERY10.NUM AS "10월", SUBQUERY11.NUM AS "11월", SUBQUERY12.NUM AS "12월"
FROM (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-01'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY1,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-02'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY2,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-03'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY3,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-04'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY4,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-05'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY5,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-06'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY6,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-07'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY7,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-08'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY8,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-09'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY9,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-10'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY10,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-11'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY11,
      
     (SELECT * 
       FROM (SELECT SUM(CARD_TRANSACTION_AMOUNT) AS NUM
             FROM CARD_TRANSACTION_TEST01
             GROUP BY TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM')
             HAVING TO_CHAR(CARD_TRANSACTION_TIME, 'YYYY-MM') = '2020-12'
             
             UNION ALL
             
             -- SELECT NULL AS NUM
             SELECT 0 AS NUM
             FROM DUAL)
       WHERE NUM IS NOT NULL OR ROWNUM = 1) SUBQUERY12
WHERE ROWNUM <=1;

 

오라클 환경에서 조회되는 ROW가 없을 때, RETURN 하는 방법

 

<p>&nbsp;</p>
<p>일반적으로 조회 시 조건절에 부합하는 데이터 ROW가 없을 경우에는 조회되지 않는다.</p>
<p>그러나 JOIN 등 특수한 경우로 조회되는 ROW가 없더라도 반환시켜야 하는 일이 생긴다.</p>
<p>&nbsp;</p>
<p>특정 컬럼만 NULL 인 경우에는 NVL 함수를 이용하면 되지만,</p>
<p><span style="color: #9d9d9d;"><i>-- NVL(COLUMN, 0) : COLUMN이 NULL 인 경우 0을 반환</i></span></p>
<p>ROW 전체가 조회되지 않을 경우에는 어떻게 처리해야 할까?</p>
<p>&nbsp;</p>

 

[방법 1] UNION 과 NOT EXISTS 의 활용

SELECT COLUMN_NAME
  FROM TABLE_NAME
 WHERE KEY_VALUE = :KEY

 UNION ALL

SELECT NULL AS COLUMN_NAME
  FROM DUAL
 WHERE NOT EXISTS ( SELECT COLUMN_NAME
                      FROM TABLE_NAME
                     WHERE KEY_VALUE = :KEY )

<p>위 방법은 간단하고, 명료하다.</p>
<p>조건절로 조회되는 ROW의 유무에 따라 UNION 상단 혹은 하단의 쿼리 결과가 조회된다.</p>
<p>UNION 상단부의 조회 ROW가 없을 경우, 하단부에 ROW가 조회되므로 무조건 하나 이상의 ROW를 RETURN 한다.</p>
<p>&nbsp;</p>
<p>위 방법으로 조회 시, 소요시간이 오래될 경우 아래의 방법을 사용한다.</p>

 

 

[방법 2] UNION 과 ROWNUM 의 활용

SELECT *
  FROM ( SELECT COLUMN_NAME
           FROM TABLE_NAME
          WHERE KEY_VALUE = :KEY

          UNION ALL

         SELECT NULL AS COLUMN_NAME
           FROM DUAL )
 WHERE COLUMN_NAME IS NOT NULL OR ROWNUM = 1

<p>UNION 사용은 동일하나, 방법 1에 비해 조회 시 태우는 총 INDEX가 더 적어 조회 시간이 조금 더 빠르다.</p>
<p>UNION 하단절의 조회결과는 NULL값 컬럼을 포함한 ROW로 UNION 상단이 NULL일때만 RETURN 된다.</p>

 

 

[방법 3] GROUP 함수의 활용

 

SELECT MAX(COLUMN_NAME) COLUMN_NAME
  FROM TABLE_NAME
 WHERE KEY_VALUE = :KEY

<p>조회 결과의 쓰임이 단일 ROW 거나 GROUP BY가 가능한 경우에는 GROUP 함수를 적극 활용한다.</p>
<p>MAX, MIN, SUM 등의 함수는 반환 ROW가 없을 경우에도 NULL을 반환한다.</p>

 

 

 

아래 블로그 감사합니다.

https://wanna-b.tistory.com/104

 

[ORACLE] 조회된 ROW가 없을 때, RETURN 하는 방법

오라클 환경에서 조회되는 ROW가 없을 때, RETURN 하는 방법 ( Return row if no data found in Oracle ) 일반적으로 조회 시 조건절에 부합하는 데이터 ROW가 없을 경우에는 조회되지 않는다. 그러나 JOIN 등 특..

wanna-b.tistory.com

 

'데이터베이스 > ORACLE' 카테고리의 다른 글

[Oracle] 연산이 필요한 게 아니라면 NUMBER 타입을 쓰지 말자  (0) 2020.09.02
[Oracle] 비교절에 현재시간 + 문자열 넣기  (0) 2020.08.31
[Oracle] date 형식의 컬럼에 데이터 넣기  (0) 2020.08.30
[Oracle] 댓글 테이블 생성  (0) 2020.08.26
[ORACLE] table 구조 보기 : desc  (0) 2020.08.06

'데이터베이스/ORACLE' Related Articles

티스토리툴바