SQL 합집합, 교집합, 차집합

aaa                 bbb

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

a   b   c           a   b   c

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

1   1   1            3   3   3

2   2   2            4   4   4

3   3   3            5   5   5

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

 

 

>> 합집합

select * from aaa

union

select * from bbb

 

-> 결과

1   1   1

2   2   2

3   3   3

4   4   4

5   5   5

 

 

>> 교집합 (inner join 가능)

select * from aaa

where exists

(select * from bbb where aaa.c = bbb.c)

 

>> SQL 2005 이상은 INTERSECT 사용 가능

select * from aaa

intersect

select * from bbb

 

-> 결과

3   3   3

 

 

>> 차집합 (outer join 가능)

select * from aaa

where not exists

(select * from bbb where aaa.c = bbb.c)

 

>> SQL 2005 이상은 EXCEPT 사용 가능

select * from aaa

except

select * from bbb

 

-> 결과

1   1   1

2   2   2

 

 

>> 합집합 - 교집합

select * from aaa where c not in (select aaa.c from aaa, bbb where bbb.c = aaa.c)
union
select * from bbb where c not in (select bbb.c from aaa, bbb where bbb.c = aaa.c)

 

>> SQL 2005 이상은 EXCEPT, INTERSECT 사용 가능

(select * from aaa
union
select * from bbb)
except

(select * from aaa

intersect

select * from bbb)

 

-> 결과

1   1   1

2   2   2

4   4   4

5   5   5