1. 테이블 전체 행 비교인지?
2. 특정 키컬럼들만 비교인지?
1번의 경우라면 차집합을 이용하시는게 좋을 듯 하구요
2번의 경우라면 NOT IN, NOT EXISTS, OUTER JOIN 후 널체크 등의 방법이 있습니다.
1번과 2번 모두 쿼리 각각 두번 사용해서 Union All 하셔야 할 듯.
full outer join 했으면 Union All 없이 한번에 되겠네요.
-- 1. Minus (SELECT * FROM aaa MINUS SELECT * FROM bbb) UNION ALL (SELECT * FROM bbb MINUS SELECT * FROM aaa) ; -- 2. Not In SELECT * FROM aaa WHERE pk NOT IN (SELECT pk FROM bbb) UNION ALL SELECT * FROM bbb WHERE pk NOT IN (SELECT pk FROM aaa) ; -- 3. Not Exists SELECT * FROM aaa a WHERE NOT EXISTS (SELECT 1 FROM bbb b WHERE b.pk = a.pk) UNION ALL SELECT * FROM bbb b WHERE NOT EXISTS (SELECT 1 FROM aaa a WHERE a.pk = b.pk) ; -- 4. Outer Join & Is Null SELECT a.* FROM aaa a LEFT OUTER JOIN bbb b ON a.pk = b.pk WHERE b.pk IS NULL UNION ALL SELECT b.* FROM bbb b LEFT OUTER JOIN aaa a ON b.pk = a.pk WHERE a.pk IS NULL ; -- 5. Full Outer Join & Is Null SELECT a.* , b.* FROM aaa a FULL OUTER JOIN bbb b ON a.pk = b.pk WHERE (a.pk IS NULL OR b.pk IS NULL) ;