SQL 차집합, 교집합, 합집합(Union, Union all, intersect, minus, exists)

--실습테이블 생성--

--테이블 생성--
create table class_1(
id varchar2(100),
constraint number_pk primary key(id),
name varchar2(100) not null,
total_scoure varchar2(100)
);

create table class_2(
id varchar(100),
constraint in_2 primary key(id),
name varchar2(100) not null,
total_scoure varchar2(100)
);

--값 삽입--
insert into class_1 values('1001A', '나상현', 500);
insert into class_1 values('1002A', '유다빈', 480);
insert into class_1 values('1003A', '권민', 390);
insert into class_1 values('1004A', '김한주', 210);

insert into class_2 values('1001A', '나상현', 500);
insert into class_2 values('1002A', '유다빈', 480);
insert into class_2 values('1004B', '김춘추', 280);
insert into class_2 values('1005B', '한로로', 400);


select * from class_1;
select * from class_2;

class_1

class_2

오늘은 합집합, 차집합, 교집합, 그리고 이 세가지를 exists명령어를 사용하여 출력하는 방법에 대해 복습할 예정이다. 그 전에 복습에서 사용할 테이블을 2개 생성하고 시작하도록 하자..!

 

 

합집합_Union all과 Union

작업을 수행하다보면, join말고 아예 여러개의 테이블들을 하나의 테이블로 합쳐서 출력해야하는 경우가 발생한다. 이럴경우 사용할 수 있는 것이 union all과 union이다.

--union all--
select * from class_1
union all
select * from class_2; --중복 값까지 전부  출력--

--union--
select * from class_1
union
select * from class_2;

union all의 경우에는 각 테이블의 중복값까지 전부 출력해서 보여준다. 위의 결과처럼 class_1의 테이블1,2행과 class_2의 5,6행에 중복값이 있어도 전부 출력하여 보여주는것을 알 수 있다.

 

반면 union의 경우 중복되는 값은 1건으로 출력하여 보여준다. 즉 union all과 union은 여러 테이블의 값을 합쳐서 보여주는 합집합을 해주는 구문이지만, union all은 중복까지 전부 출력하고, union의 경우에는 중복값은 1건으로 처리하여 출력하여 보여준다는 차이점을 가지고있다. 또한 union all과 union모두 계속해서 합집합구문으로 연결해준다면 2개이상의 테이블도 합집합의 결과로 출력할 수 있다.

 

Union all과 Union 별칭사용하기

select id as 확인번호, name as 이름, total_scoure as 과목합산점수
from class_1
union all
select id, name, total_scoure from class_2;


select id as 확인번호, name as 이름, total_scoure as 과목합산점수
from class_1
union
select * from class_2;

union all과 union을 사용하여 합집합으로 테이블을 출력할때도 별칭을 사용하여 출력할 수 있다. 하지만 주의할점은 합집합 시키는 테이블의 첫번째 테이블에 별칭을 적어주어야 하며, 당연히 위 아래 둘다 다른 별칭을 적어 사용할 수 는 없다.

 

Union all과 Union에서 order by사용하기

--order by사용하기--
select id, name from class_1
union all
select id, name from class_2
order by name;


select id, name from class_1
order by name  --에러!--
union all
select id, name from class_2
order by name;

union all과 union을 사용하여 합집합 하는 경우에도 order by를 사용할 수 있다. 하지만 주의할점은 각각 테이블에서 order by를 사용할 수 없고 order by는 별칭과는 반대로 맨 마지막에 연결되는 테이블의 구문에 적어야한다.

 

 

Union all과 Union에서 where절 사용하기

select * from class_1
where total_scoure >=300
union all
select * from class_2
where total_scoure >=400; --where절은 조건절이기때문에 각각 사용가능--

union all과 union에서도 where절을 사용할 수 있다. where절은 구문에서 출력되는 조건이기때문에 각각 사용이 가능하며, 첫번째 테이블 구문에서 사용했으나 두번째 구문에서 사용하지 않는등 자유롭게 사용이 가능하다.

 

intersect(교집합)과 minus(차집합)

--교집합, 차집합--

--intersect--
select * from class_1
intersect
select * from class_2;

--minus--
select * from class_1
minus
select * from class_2;

select * from class_2
minus
select * from class_1;

intersect

intersect는 연결되는 테이블에서 정확하게 같은값만을 출력하는 교집합이다. 만약 각각의 테이블에서 같은값이 여러개 있다면 자동적으로 교집합되는 값들을 1건으로 중복제거 처리한 후 출력한다.

minus의 경우 두 테이블에서 겹치지 않는 차집합을 출력해주는 차집합이다. minus를 사용할때 주의할점은 순서가 중요하다는 것이다. a minus b의 경우 a테이블을 기준으로 b테이블이 가지지 않은 값들을 출력하며, b minus a일경우 b테이블을 기준으로 a테이블이 가지지 않은 값들을 출력하기 때문에 어떤 테이블을 먼저 적는지에 대한 순서에 영향을 받는다.

 

 

exists 구문 사용하기

또한 이런 교집합과 차집합은 오라클에서 intersect와 minus라는 구문으로 쉽게 사용할 수 있지만 mysal의 경우 이 두 구문을 지원하지 않기때문에 exists구문을 이용하여 차집합과 교집합을 출력할 수 있다.

--exists 구문 사용하기--

--intersect--
select * from class_1 c1
where exists(select 1 from class_2 c2 where c1.id=c2.id);

--minus--
select * from class_1 c1
where not exists(select 1 from class_2 c2 where c1.id=c2.id);

exists는 where절에서 사용하며, 서브쿼리의 형태로 주로 사용한다. exists에 제시된 조건에 맞는 테이블이 메인쿼리에 있는 테이블에 존재하는지 존재하지 않는지를 확인할 수 있다. exists경우 서브쿼리안에 서술되는 where절이 주가 되므로 사실상 select 절은 별로 중요하지 않아, 보통 1이나 x를 적는 경우가 많다. 

 

첫번째 구문의 경우 class_1테이블을 조회할건데 조건이 class_2테이블에서 id가 class_1과 같은 데이터가 존재하는지(exists)확인하고 있다면 그에 해당하는 값들을 출력해달라는 뜻이기때문에 교집합이 출력되며,

두번째 구문의 경우,   class_1테이블을 조회할건데 조건이 class_2테이블에서 id가 class_1과 같은 데이터가 존재하는지(exists)확인하고 있다면 그에 해당하지 않는 값들을 출력해달라는 뜻이기때문에 차집합이 출력된다.

 

---

학습일기

맨처음에 join과 union의 차이점이 몰라 헷갈렸던게 기억에 남는복습이었다. 계속 둘다 똑같은거 아닌가..? 하면서 공부했었었는데 join은 키를 가지고 연결하는것이나 보니 중복값이 발생할 수 있다는걸 깨닫기까지 부끄럽지만 조금 시간이 걸렸었다..ㅎㅎ

그리고 나는 sql을 오라클로 거의 사용하다보니 intersect와 minus 구문 사용에 익숙해져있기때문에 사실상 exists로 intersect와 minus를 사용하는 경우가 조금 드물었는데, mysal의 경우에는 이 부분을 지원하지 않기때문에 교집합, 차집합을 사용할때마다 수시로 exists구문도 함꼐 사용하여 같이 학습하면 좋을것 같다..!