WITH VEHIC_MST AS (
SELECT 'KA1234' VEHIC_NO, 'aaa' INFO FROM DUAL UNION ALL
SELECT 'KA1235' VEHIC_NO, 'bbb' INFO FROM DUAL UNION ALL
SELECT 'KA1236' VEHIC_NO, 'ccc' INFO FROM DUAL UNION ALL
SELECT 'KA1237' VEHIC_NO, 'ddd' INFO FROM DUAL
)
, COUPON_MST AS (
SELECT 'SV' CUPN_BUSI, '10001' CUPN_BASE_NO, 50000 CUPN_AMT FROM DUAL UNION ALL
SELECT 'SV' CUPN_BUSI, '20001' CUPN_BASE_NO, 50000 CUPN_AMT FROM DUAL UNION ALL
SELECT 'MK' CUPN_BUSI, '10001' CUPN_BASE_NO, 50000 CUPN_AMT FROM DUAL UNION ALL
SELECT 'MK' CUPN_BUSI, '20001' CUPN_BASE_NO, 100000 CUPN_AMT FROM DUAL
)
, COUPON_DETL AS (
SELECT 'SV' CUPN_BUSI, '10001' CUPN_BASE_NO, 'KA1234' VEHIC_NO FROM DUAL UNION ALL
SELECT 'MK' CUPN_BUSI, '10001' CUPN_BASE_NO, 'KA1234' VEHIC_NO FROM DUAL UNION ALL
SELECT 'MK' CUPN_BUSI, '20001' CUPN_BASE_NO, 'KA1235' VEHIC_NO FROM DUAL
)
SELECT A.VEHIC_NO, A.INFO, NVL(D.CUPN_AMT, 0) CUPN_AMT
  FROM VEHIC_MST A
     , (SELECT C.VEHIC_NO, SUM(B.CUPN_AMT) CUPN_AMT
         FROM COUPON_MST B
            , COUPON_DETL C
        WHERE C.CUPN_BUSI='MK' 
          AND C.CUPN_BASE_NO ='20001'
          AND B.CUPN_BUSI = C.CUPN_BUSI
          AND B.CUPN_BASE_NO = C.CUPN_BASE_NO
        GROUP BY C.VEHIC_NO
        ) D
 WHERE A.VEHIC_NO = D.VEHIC_NO(+)
 ORDER BY A.VEHIC_NO
;

-- ANSI JOIN --
SELECT a.vehic_no
     , a.info1
     , a.info2
     , NVL(SUM(c.cupn_amt), 0) cupn_amt
  FROM vehic_mst a
  LEFT OUTER JOIN coupon_detl b
    ON b.vehic_no     = a.vehic_no
   AND b.cupn_busi    = 'MK'
   AND b.cupn_base_no = '20001'
  LEFT OUTER JOIN coupon_mst  c
    ON b.cupn_busi    = c.cupn_busi   
   AND b.cupn_base_no = c.cupn_base_no
 GROUP BY a.vehic_no, a.info1, a.info2
;


-- Oracle 전통 JOIN --
SELECT a.vehic_no
     , a.info1
     , a.info2
     , NVL(SUM(c.cupn_amt), 0) cupn_amt
  FROM vehic_mst   a
     , coupon_detl b
     , coupon_mst  c
 WHERE b.vehic_no    (+) = a.vehic_no
   AND b.cupn_busi   (+) = 'MK'
   AND b.cupn_base_no(+) = '20001'
    ON c.cupn_busi   (+) = b.cupn_busi   
   AND c.cupn_base_no(+) = b.cupn_base_no
 GROUP BY a.vehic_no, a.info1, a.info2
;