카티션곱을 이용한 데이터 추출 방법

테이블 a

sp_key

111

222

333

테이블 b

id	sp_key
test1	111
test1	222
test1	333
test2	111
test2	222

결과

id	sp_key
test2	333

테이블 a의 3개 sp_key 컬럼 데이터를 기준으로 테이블 b의 각 id에 sp_key데이터가 없는 것을 추출하는 방법에 대해서 설명한다.

대개 쿼리를 작성할 때 inner join이나 outer join을 많이 사용하지만 위와 같이 두 개의 테이블 집합에서 공통으로 존재하는 데이터를 뽑아내는 것이 아닌 없는 데이터를 뽑을 때에는 어려움을 많이 느낀다.

위의 문제를 해결할 수 있는 방법이 몇 가지 있는데 그 중 첫 번째 방법은 카티션 곱을 이용하여 데이터를 복제한 후 결과 값을 추출하는 방법이다.

SELECT a.id, b.sp_key

  FROM (        

        SELECT 'test1' AS id FROM DUAL

        UNION ALL              

        SELECT 'test2' FROM DUAL

       ) a, 

       (

       SELECT '111' AS sp_key FROM DUAL

       UNION ALL

       SELECT '222' FROM DUAL

       UNION ALL

       SELECT '333' FROM DUAL

       ) b

MINUS

SELECT id, sp_key

  FROM (

       SELECT 'test1' AS id, '111' AS sp_key FROM DUAL

       UNION ALL

       SELECT 'test1', '222' FROM DUAL

       UNION ALL

       SELECT 'test1', '333' FROM DUAL

       UNION ALL

       SELECT 'test2', '111' FROM DUAL

       UNION ALL

       SELECT 'test2', '222' FROM DUAL

       ) c

위의 쿼리는 카티션 곱과 MINUS 집합 연산을 통해서 결과를 뽑아낸 예제이다.

위의 예제를 통해 그림으로 간단하게 표현하면 다음과 같다.

인라인 뷰로 생성되어 있는 a, b 테이블을 카티션 곱하게 되면 id : test2, sp_key : 3 값이 추출되어 있는 것을 확인할 수 있을 것이다.

카티션 곱을 이용하여 데이터를 추출한 후 c 테이블의 집합을 MINUS 하면 뽑고자 하는 데이터가 추출되는 것을 알 수 있다.

ID      SP_KEY  

------  --------

TEST2   333     

두 번째 방법은 MINUS 집합 연산을 사용하지 않고, NOT IN 조건을 걸어 데이터를 추출하는 방법이다.

CREATE TABLE T1 (

  SP_KEY VARCHAR(32) NOT NULL

) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE T2 (

  ID VARCHAR(32) NOT NULL,

  SP_KEY VARCHAR(32) NOT NULL

) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO T1 (SP_KEY) VALUES ('111');

INSERT INTO T1 (SP_KEY) VALUES ('222');

INSERT INTO T1 (SP_KEY) VALUES ('333');

INSERT INTO T2 (ID, SP_KEY) VALUES ('TEST1', '111');

INSERT INTO T2 (ID, SP_KEY) VALUES ('TEST1', '222');

INSERT INTO T2 (ID, SP_KEY) VALUES ('TEST1', '333');

INSERT INTO T2 (ID, SP_KEY) VALUES ('TEST2', '111');

INSERT INTO T2 (ID, SP_KEY) VALUES ('TEST2', '222');

아래 쿼리 또한 카티션 곱을 이용하여 원하는 데이터를 추출한다.

SELECT T2.ID , T1.SP_KEY

  FROM T1, (SELECT ID FROM T2 GROUP BY ID) T2

 WHERE (ID , SP_KEY) NOT IN (SELECT ID, SP_KEY FROM T2) 

 ORDER BY ID, SP_KEY;

SELECT T2.ID , T1.SP_KEY

  FROM T1, (SELECT ID FROM T2 GROUP BY ID) T2 <- 여기 까지의 쿼리 결과가 다음과 같다.

SELECT ID, SP_KEY FROM T2 <- 해당 쿼리 결과

즉, 위 두 개의 결과 집합에서 NOT IN 조건을 걸어주게 되면 원하는 결과 값을 추출할 수 있다.

ID      SP_KEY  

------  --------

TEST2   333