--혹 안 될지도 몰라서 테스트 진행을... ^^; CREATE TABLE TEMP ( IDX INT PRIMARY KEY NOT NULL, CONTENT VARCHAR(10), ALTDATE DATE ); INSERT INTO TEMP VALUES(1, 'Hong', SYSDATE); INSERT INTO TEMP VALUES(2, 'Bhak', SYSDATE); INSERT INTO TEMP VALUES(3, 'Kim', SYSDATE); INSERT INTO TEMP VALUES(4, 'Park', SYSDATE); CREATE TABLE TEMP2 AS SELECT * FROM TEMP; ALTER TABLE TEMP2 ADD CONSTRAINT PK_TEMP2_IDX PRIMARY KEY (IDX); INSERT INTO TEMP2 VALUES(5, 'Choi', SYSDATE + 1); INSERT INTO TEMP2 VALUES(6, 'Lee', SYSDATE + 1); DELETE TEMP2 WHERE IDX = 4; INSERT INTO TEMP2 VALUES(7, 'Song', SYSDATE - 1); --범위 외 데이타 INSERT INTO TEMP2 VALUES(8, 'Kang', SYSDATE - 1); --범위 외 데이타 SELECT * FROM TEMP T1 FULL OUTER JOIN TEMP2 T2 ON T1.IDX = T2.IDX WHERE (TO_CHAR(T1.ALTDATE, 'YYYYMMDD') = '20131120' OR T1.ALTDATE IS NULL) AND (TO_CHAR(T2.ALTDATE, 'YYYYMMDD') IN('20131120', '20131121') OR T2.ALTDATE IS NULL) AND (T1.ALTDATE IS NULL OR T2.ALTDATE IS NULL) ;
-- FULL OUTER JOIN -- SELECT NVL(a.pk1, b.pk1) pk1 , a.pk1 pk1_a , b.pk1 pk1_b , DECODE(NULL, a.pk1, '삭제', b.pk1, '추가', '유지') flag FROM (SELECT pk1, pk2 FROM t WHERE pk2 = '20131104') a FULL OUTER JOIN (SELECT pk1, pk2 FROM t WHERE pk2 = '20131105') b ON a.pk1 = b.pk1 ORDER BY NVL(a.pk1, b.pk1) ; -- GROUP BY -- SELECT pk1 , CASE WHEN MAX(pk2) = '20131104' THEN '삭제' WHEN MIN(pk2) = '20131105' THEN '추가' ELSE '유지' END flag FROM t WHERE pk2 IN ('20131104','20131105') GROUP BY pk1 ORDER BY pk1 ;