UNION

- 유니온(UNION)

SELECT * FROM A
UNION (ALL)
SELECT * FROM B

A 와 B 의 테이블의 해당하는 컬럼들을 연결하여 보여줍니다.
OR과 유사하다고 생각하면 되며 실제로 OR을 사용하는 쿼리를 UNION ALL으로 대체시 수행속도를 향상할 수 있습니다.
UNION은 중복된 데이타를 제거하며 UNION ALL은 중복된 데이타를 모두 보여 줍니다.
가능하다면 UNION ALL을 사용하는 것이 좋습니다.
(DISTINCT를 사용하는 것보다는 UNION만 쓰는것이 더 효율적이라 생각됩니다.)

** 유니온(UNION) 서브쿼리 중복제거 최신건 다건조회 활용 쿼리문 **

SELECT *
FROM(

    SELECT
          ug_lim_mbdy_dsc
       ,ug_lim_mbdc
       ,apl_st_dt
       ,apl_ed_dt
       ,sp_rgn_dsc
       ,sp_rgn_tpc
       ,mcht_mtalnm
       ,mcht_bzcnm
       ,provnm
       ,ccwnm
    FROM (

     -- 가맹점일 경우
         SELECT
            a.ug_lim_mbdy_dsc
           ,a.ug_lim_mbdc
           ,a.apl_st_dt
           ,a.apl_ed_dt
           ,' ' AS sp_rgn_dsc
           ,' ' AS sp_rgn_tpc
           ,b.mcht_mtalnm
           ,' ' AS mcht_bzcnm
           ,' ' AS provnm
           ,' ' AS ccwnm
         FROM tb_cs_jh_ch_cduglimbrk a
                   ,tb_cs_mc_cm_bsc b
                   ,(
                   SELECT        -- 현재일자 이후건 모두 조회

             tup_c

            ,ug_lim_mbdy_dsc

                        ,ug_lim_mbdc
                        ,apl_st_dt
                    FROM  tb_cs_jh_ch_cduglimbrk
                    WHERE tup_c = :tup_c
                     AND apl_ed_dt >= :now_dt      /* 현재일자 */
      

        UNION    --중복제거 데이타 제거해야함

     

        SELECT         -- 과거포함 가장 최신건 조회

                tup_c

               ,ug_lim_mbdy_dsc

               ,ug_lim_mbdc

                            ,max(apl_st_dt) apl_st_dt

        FROM  tb_cs_jh_ch_cduglimbrk

         WHERE tup_c = :tup_c

         GROUP BY tup_c, ug_lim_mbdy_dsc, ug_lim_mbdc

        ) c          

         WHERE a.tup_c = :tup_c
              
         AND a.tup_c = c.tup_c
         AND a.ug_lim_mbdy_dsc = c.ug_lim_mbdy_dsc
         AND a.ug_lim_mbdc = c.ug_lim_mbdc
         AND a.apl_st_dt = c.apl_st_dt
              
          AND a.ug_lim_mbdc = b.mcht_no
          AND a.ug_lim_mbdy_dsc = '1'
      
         UNION ALL
      
         -- 업종일 경우
         SELECT
            a.ug_lim_mbdy_dsc
           ,a.ug_lim_mbdc
           ,a.apl_st_dt
           ,a.apl_ed_dt
           ,' ' AS sp_rgn_dsc
           ,' ' AS sp_rgn_tpc
           ,' ' AS mcht_mtalnm
           ,b.mcht_bzcnm
           ,' ' AS provnm
           ,' ' AS ccwnm
         FROM tb_cs_jh_ch_cduglimbrk a
                   ,tb_cs_mc_cc_bzc_c b
                  ,(
                  SELECT        -- 현재일자 이후건 모두 조회
                             tup_c

                 ,ug_lim_mbdy_dsc

                 ,ug_lim_mbdc

                   ,apl_st_dt

        FROM  tb_cs_jh_ch_cduglimbrk

         WHERE tup_c = :tup_c

              AND apl_ed_dt >= :now_dt      /* 현재일자 */

      

        UNION    --중복제거 데이타 제거해야함

     

        SELECT         -- 과거포함 가장 최신건 조회

                tup_c

               ,ug_lim_mbdy_dsc

                ,ug_lim_mbdc

               ,max(apl_st_dt) apl_st_dt

        FROM  tb_cs_jh_ch_cduglimbrk

        WHERE tup_c = :tup_c

        GROUP BY tup_c, ug_lim_mbdy_dsc, ug_lim_mbdc

        ) c                     

         WHERE a.tup_c = :tup_c

             AND a.tup_c = c.tup_c
             AND a.ug_lim_mbdy_dsc = c.ug_lim_mbdy_dsc
             AND a.ug_lim_mbdc = c.ug_lim_mbdc
             AND a.apl_st_dt = c.apl_st_dt

             AND a.ug_lim_mbdc = b.mcht_bzcc
             AND a.ug_lim_mbdy_dsc = '2'
               
         UNION ALL
        
         -- 특정지역일 경우
         SELECT
           a.ug_lim_mbdy_dsc
          ,a.ug_lim_mbdc
           ,a.apl_st_dt
           ,a.apl_ed_dt
           ,a.sp_rgn_dsc
           ,a.sp_rgn_tpc
           ,' ' AS mcht_mtalnm
           ,' ' AS mcht_bzcnm
           ,a.provnm
           ,a.ccwnm
         FROM tb_cs_jh_ch_cduglimbrk a
                ,(
                 SELECT        -- 현재일자 이후건 모두 조회

                 tup_c

                 ,ug_lim_mbdy_dsc

                 ,ug_lim_mbdc

                 ,apl_st_dt

      FROM  tb_cs_jh_ch_cduglimbrk

      WHERE tup_c = :tup_c

          AND apl_ed_dt >= :now_dt      /* 현재일자 */

      

        UNION    --중복제거 데이타 제거해야함

     

        SELECT         -- 과거포함 가장 최신건 조회

                  tup_c

                 ,ug_lim_mbdy_dsc

                 ,ug_lim_mbdc

     ,max(apl_st_dt) apl_st_dt

        FROM  tb_cs_jh_ch_cduglimbrk

        WHERE tup_c = :tup_c

        GROUP BY tup_c, ug_lim_mbdy_dsc, ug_lim_mbdc

                    ) b                              
         WHERE a.tup_c = :tup_c

 AND a.tup_c = b.tup_c

 AND a.ug_lim_mbdy_dsc = b.ug_lim_mbdy_dsc

 AND a.ug_lim_mbdc = b.ug_lim_mbdc

 AND a.apl_st_dt = b.apl_st_dt

 AND a.ug_lim_mbdy_dsc = '3'

    )
    WHERE ug_lim_mbdy_dsc >= :ug_lim_mbdy_dsc
      AND ((ug_lim_mbdy_dsc > :ug_lim_mbdy_dsc)
          OR (ug_lim_mbdy_dsc = :ug_lim_mbdy_dsc AND sp_rgn_dsc > :sp_rgn_dsc)
          OR (ug_lim_mbdy_dsc = :ug_lim_mbdy_dsc AND sp_rgn_dsc = :sp_rgn_dsc AND provnm > :provnm)
          OR (ug_lim_mbdy_dsc = :ug_lim_mbdy_dsc AND sp_rgn_dsc = :sp_rgn_dsc AND provnm = :provnm AND ug_lim_mbdc > :ug_lim_mbdc)
          OR (ug_lim_mbdy_dsc = :ug_lim_mbdy_dsc AND sp_rgn_dsc = :sp_rgn_dsc AND provnm = :provnm AND ug_lim_mbdc = :ug_lim_mbdc AND apl_st_dt >= :apl_st_dt)
          )

    ORDER BY ug_lim_mbdy_dsc, sp_rgn_dsc, provnm, ug_lim_mbdc, apl_st_dt

)
WHERE ROWNUM <= 16

길지만 하나의 조회 쿼리문 입니다. (이때까지 사용해본 조회 쿼리 중에서 가장 길군요.^^)

조회시 유용한 쿼리가 많이 포함되어 있으므로 천천히 읽어보시고
유용하게 활용하시기 바랍니다.

보시고 이해가 가지 않는 부분은 댓글로 질문해 주시면 성의껏 답변해 드리겠습니다.