[oracle] intersect and minus

Published: by Creative Commons Licence

교집합

WITH TAB1 AS (SELECT '10' AS FALG FROM DUAL
              UNION ALL
              SELECT '20' AS FALG FROM DUAL)
, TAB2 AS (SELECT '10' AS FALG FROM DUAL
           UNION ALL
           SELECT '30' AS FALG FROM DUAL)
SELECT FALG FROM TAB1
INTERSECT
SELECT FALG FROM TAB2
;


차집합

TAB1 - TAB2 이므로 TAB1기준으로 공통된 부분을 제외하고 남은 행을 리턴

WITH TAB1 AS (SELECT '10' AS FALG FROM DUAL
              UNION ALL
              SELECT '20' AS FALG  FROM DUAL)
, TAB2 AS (SELECT '10' AS FALG FROM DUAL
           UNION ALL
           SELECT '30' AS FALG FROM DUAL)
SELECT FALG FROM TAB1
MINUS
SELECT FALG FROM TAB2
;

차집합 예시

인입값으로 TB_BRAND를 조회해야 하는데 실제로 조회 되지 않는 데이터가 무엇인지 알고 싶을때


WITH TB_BRAND
AS (
SELECT '1126089' AS BRAND_NO FROM DUAL UNION ALL
SELECT '4975' AS BRAND_NO FROM DUAL UNION ALL
SELECT '1136949' AS BRAND_NO FROM DUAL UNION ALL
SELECT '7361' AS BRAND_NO FROM DUAL UNION ALL
SELECT '5056' AS BRAND_NO FROM DUAL UNION ALL
SELECT '050426' AS BRAND_NO FROM DUAL UNION ALL
SELECT '6124' AS BRAND_NO FROM DUAL UNION ALL
SELECT '8339' AS BRAND_NO FROM DUAL UNION ALL
SELECT '0175' AS BRAND_NO FROM DUAL UNION ALL
SELECT '1165316' AS BRAND_NO FROM DUAL UNION ALL
SELECT '1105166' AS BRAND_NO FROM DUAL UNION ALL
SELECT '8340' AS BRAND_NO FROM DUAL UNION ALL
SELECT '5930' AS BRAND_NO FROM DUAL UNION ALL
SELECT '0162' AS BRAND_NO FROM DUAL UNION ALL
SELECT '0656' AS BRAND_NO FROM DUAL UNION ALL
SELECT '5135' AS BRAND_NO FROM DUAL UNION ALL
SELECT '017249' AS BRAND_NO FROM DUAL UNION ALL
SELECT '033618' AS BRAND_NO FROM DUAL UNION ALL
SELECT '050072' AS BRAND_NO FROM DUAL UNION ALL
SELECT '5227' AS BRAND_NO FROM DUAL UNION ALL
SELECT '5100' AS BRAND_NO FROM DUAL
)
SELECT DISTINCT
        REGEXP_SUBSTR(
            REGEXP_REPLACE(:IN_DATA, '''') -- 작은따옴표 제거
                , '[^, ]+', 1, LEVEL) BRAND_NO  FROM DUAL -- 쉼표와 공배 제거해서 레벨링
CONNECT BY REGEXP_SUBSTR(REGEXP_REPLACE(:IN_DATA, ''''), '[^, ]+', 1, LEVEL) > 0 -- 레벨링이 된건 출력
MINUS -- 차집합
SELECT BRAND_NO
FROM   TB_BRAND a
WHERE  BRAND_NO IN ('1126089', '4975', '1136949', '7361', '5056', '50426', '6124', '8339', '175', '1165316', '1105166', '8340', '5930', '162', '656', '5135', '17249', '33618', '50072', '5227', '5100')
;
BRAND_NO
162
17249
175
33618
50072
50426
656