파트 5-5. join 및 union
- 1. INNER JOIN(내부 조인)
- 2. OUTER JOIN(외부 조인)
- 3. CROSS JOIN(상호 조인)
- 4. SELF JOIN(자체 조인)
- 5. union / union all / not in / in
1. INNER JOIN(내부 조인)
조인이란 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것을 말한다.
inner join은 조인 중에서 가장 많이 사용되는 조인이다. 대개의 업무에서 조인은 inner join을 사용한다. 일반 적으로 join이라고 얘기하면 inner join을 지칭하는 것이다.
select <열 목록>
from <첫 번째 테이블>
inner join <두 번째 테이블>
on <조인될 조건>
[where 검색조건]
예를 들어 구매 테이블 중에서 jyp라는 아이디를 가진 사람이 구매한 물건을 발송하기 위해서 이름/주소/연락처 등을 조인해서 검색하려면 다음과 같이 작성한다.
select *
from buytbl -- from에 사용한 것이 출력에서 먼저 나옴
inner join usertbl
on buytbl.userid = usertbl.userid
where buytbl.userid = 'jyp';
명확한 지정을 안해서 생기는 오류와 해결법
-- userid를 buytbl.userid로 수정해야한다.
select userid,name,prodname,addr,concat(mobile1,mobile2) as '연락처'
from buytbl -- from에 사용한 것이 출력에서 먼저 나옴
inner join usertbl
on buytbl.userid = usertbl.userid
-- 오류 발생
error code : 1052. colum 'userid' in field list is ambiguous
테이블 두 개에 같은 열이름이 있어서 어떤 것을 사용해야할지에 관한 오류이다. 따라서 테이블이름.열이름 이로 입력해준다. 보통 명확하게 하기 위해서 모두 테이블.이름을 사용한다. 하지만 이렇게 될 경우 코드가 너무 길어져 오히려 복잡해 보인다. 이때 각 테이블에 별칭을 줘서 코드를 간결하게 할 수 있다. 아래가 가장 무난하고 일반적인 코드라고 보면 된다.
select B.userid,U.name,B.prodname,U.addr,concat(U.mobile1,U.mobile2) as '연락처'
from buytbl B
inner join usertbl U
on B.userid = U.userid
order by B.num;
inner join의 유용성
inner join은 한쪽에는 없는 목록, 즉 양쪽에 있는 목록만 나오기 때문에 유용한 경우가 있다. 예를 들어 쇼핑몰에서 한번이라도 구매한 기록이 있는 우수회원들에게 감사의 안내문을 발송하도록 하는 경우에 다음과 같이 distinct문을 활용해서 회원의 주소록을 뽑을 수 있다.
-- distinct 를 이용해서 userid가 중복되는 경우를 제외해서 한 번만 나타내준다.
select distinct u.userid,u.name,u.addr
from usertbl u
inner join buytbl b
on u.userid = b.userid
order by u.userid;
세 개의 테이블 조인
세 개의 테이블의 조인을 살펴보자. 학생과 동아리의 관계를 생각해 보자. 한 학생은 여러 개의 동아리에 가입할 수 있고 하나의 동아리에는 여러 명의 학생이 가입할 수 있다. 두 개는 서로 다대다의 관계라고 표현할 수 있다. 다대다 관계는 논리적으로는 구성이 가능하지만 이를 물리적으로 구성하기 위해서는 두 테이블의 사이에 연결 테이블을 둬서 이 연결 테이블과 두 테이블이 일대다 관계를 맺도록 구성해야 한다.
USE sqldb;
-- 학생 테이블 구성
CREATE TABLE stdtbl
( stdName VARCHAR(10) NOT NULL PRIMARY KEY,
addr CHAR(4) NOT NULL
);
-- 동아리 테이블 구성
CREATE TABLE clubtbl
( clubName VARCHAR(10) NOT NULL PRIMARY KEY,
roomNo CHAR(4) NOT NULL
);
-- 연결 테이블 구성
CREATE TABLE stdclubtbl
( num int AUTO_INCREMENT NOT NULL PRIMARY KEY,
stdName VARCHAR(10) NOT NULL,
clubName VARCHAR(10) NOT NULL,
FOREIGN KEY(stdName) REFERENCES stdtbl(stdName),
FOREIGN KEY(clubName) REFERENCES clubtbl(clubName)
);
-- 각각 테이블에 정보 삽입
INSERT INTO stdtbl VALUES ('김범수','경남'), ('성시경','서울'), ('조용필','경기'), ('은지원','경북'),('바비킴','서울');
INSERT INTO clubtbl VALUES ('수영','101호'), ('바둑','102호'), ('축구','103호'), ('봉사','104호');
INSERT INTO stdclubtbl VALUES (NULL, '김범수','바둑'), (NULL,'김범수','축구'), (NULL,'조용필','축구'), (NULL,'은지원','축구'), (NULL,'은지원','봉사'), (NULL,'바비킴','봉사');
-- 각 학생마다 어떤 동아리를 들었는가 확인
SELECT S.stdName, S.addr, SC.clubName, C.roomNo
FROM stdtbl S
-- stdtbl과 stdclubtbl을 먼저 inner join
INNER JOIN stdclubtbl SC
ON S.stdName = SC.stdName
-- 위에서 inner join된 결과를 가지고 clubtbl과 다시 inner join
INNER JOIN clubtbl C
ON SC.clubName = C.clubName
ORDER BY S.stdName;
-- 각 동아리마다 어떤 학생이 있는가 확인
SELECT C.clubName, C.roomNo, S.stdName, S.addr
FROM stdtbl S
INNER JOIN stdclubtbl SC
ON SC.stdName = S.stdName
INNER JOIN clubtbl C
ON SC.clubName = C.clubName
ORDER BY C.clubName;
2. OUTER JOIN(외부 조인)
OUTER JOIN은 조인의 조건에 만족되지 않는 행까지도 포함시키는 것이다. 자주 사용되지는 않는다.
select <열 목록>
from <첫 번째 테이블(left 테이블)>
<left | right | full> outer join <두 번째 테이블(right 테이블)>
on <조인될 조건>
[where 검색조건];
-- 예시
select u.userid, u.name, b.prodname, u.addr
from usertbl u
left outer join buytbl b
on u.userid = b.userid;
left outer join문의 의미를 왼쪽 테이블(usertbl)의 것은 모두 출력한다로 해석하면 된다. 즉, 이 경우에는 u.userid = b.userid가 아닌 경우에도 해당 열에 대해서 usertbl은 출력하는 것이다. 동일한 결과를 얻기 위해서 right outer join문을 사용하려면 usertbl과 buytbl위치를 바꿔주면 된다.
left outer join을 줄여서 left join 이라고만 써도 된다.
그렇다면 이 방법을 조금 더 활용해서 회원 목록에서 한 번도 구매한 적이 없는 회원 목록을 뽑아보자.
select u.userid, u.name, b.prodname, u.addr
from usertbl u
left outer join buytbl b
on u.userid = b.userid
where b.prodname is null;
실습하기
위에 세 개의 테이블있던 학생 동아리 그림으로 실습해보자.
USE sqldb;
SELECT S.stdName, S.addr, C.clubName, C.roomNo
-- 먼저 stdtbl과 stdclubtbl 공통을 뽑아내고 stdtbl은 전체를 뽑아낸다.
FROM stdtbl S
LEFT OUTER JOIN stdclubtbl SC
ON S.stdName = SC.stdName
-- 위에서 뽑아낸것은 유지하고 clubname을 조인한다.
LEFT OUTER JOIN clubtbl C
ON SC.clubName = C.clubName
ORDER BY S.stdName;
SELECT C.clubName, C.roomNo, S.stdName, S.addr
-- stdtbl과 stdclubtbl의 공통을 뽑아내고 stdtbl은 전체를 뽑아낸다.
FROM stdtbl S
LEFT OUTER JOIN stdclubtbl SC
ON SC.stdName = S.stdName
-- 위에서 뽑아낸 것에서 clubtbl과 공통만 뽑아내고 clubtbl은 전체를 뽑아낸다.
RIGHT OUTER JOIN clubtbl C
ON SC.clubName = C.clubName
ORDER BY C.clubName ;
-- 각각의 결과를 union으로 합친다.
SELECT S.stdName, S.addr, C.clubName, C.roomNo
FROM stdtbl S
LEFT OUTER JOIN stdclubtbl SC
ON S.stdName = SC.stdName
LEFT OUTER JOIN clubtbl C
ON SC.clubName = C.clubName
UNION
SELECT S.stdName, S.addr, C.clubName, C.roomNo
FROM stdtbl S
LEFT OUTER JOIN stdclubtbl SC
ON SC.stdName = S.stdName
RIGHT OUTER JOIN clubtbl C
ON SC.clubName = C.clubName;
3. CROSS JOIN(상호 조인)
한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능을 한다. 그래서 cross join의 결과 개수는 두 테이블 개수를 곱한 개수가 된다. 
select *
from buytbl
cross join usertbl;
cross join에는 on구문을 사용할 수 없다. cross join의 용도는 테스트로 사용할 많은 데이터를 생성할 때 주로 사용한다.
4. SELF JOIN(자체 조인)
self join은 별도의 구문이 있는 것이 아니라 자기 자신과 자기 자신이 조인한다는 의미이다. 대표적으로 활용되는 예가 조직도와 관련된 테이블이다.
위와 같은 조직도에서 직원의 상관 번호를 알고자 한다면 다음과 같다.
-- 먼저 위 그림과 같이 테이블을 만든다.
CREATE TABLE empTbl (emp CHAR(3), manager CHAR(3), empTel VARCHAR(8));
INSERT INTO empTbl VALUES('나사장',NULL,'0000');
INSERT INTO empTbl VALUES('김재무','나사장','2222');
INSERT INTO empTbl VALUES('김부장','김재무','2222-1');
INSERT INTO empTbl VALUES('이부장','김재무','2222-2');
INSERT INTO empTbl VALUES('우대리','이부장','2222-2-1');
INSERT INTO empTbl VALUES('지사원','이부장','2222-2-2');
INSERT INTO empTbl VALUES('이영업','나사장','1111');
INSERT INTO empTbl VALUES('한과장','이영업','1111-1');
INSERT INTO empTbl VALUES('최정보','나사장','3333');
INSERT INTO empTbl VALUES('윤차장','최정보','3333-1');
INSERT INTO empTbl VALUES('이주임','윤차장','3333-1-1');
SELECT A.emp AS '부하직원' , B.emp AS '직속상관', B.empTel AS '직속상관연락처'
-- 같은 테이블을 별칭만 다르게 하고 조인
FROM empTbl A
INNER JOIN empTbl B
ON A.manager = B.emp
WHERE A.emp = '우대리';
5. union / union all / not in / in
union
union은 두 쿼리의 결과를 행으로 합치는 것이다.
select 문장 1
union [all]
select 문장 2
select 문장 1과 문장 2의 결과 열의 개수가 같아야하고 데이터 형식도 각 열 단위로 같거나 서로 호환되는 데이터 형식이어야 한다. 또한 열 이름은 문장 1의 열 이름을 따른다. union만 사용하면 중복된 열은 제거되고 데이터가 정렬되어 나오며, union all을 사용하면 중복된 열까지 모두 출력된다.
noi in
not in은 첫 번째 쿼리의 결과 중에서, 두 번째 쿼리에 해당하는 것을 제외하기 위한 구문이다. 예로 sqldb의 사용자를 모두 조회하되 전화가 없는 사람을 제외하고자 한다면 다음과 같다.
select name, concat(mobile1,mobile2) as '전화번호' from usertbl
-- ()에 해당하는 name에 포함되지 않은 name만 출력한다는 where 조건
where name not in (select name from usertbl where mobile1 is null);
in
not in과 반대로 첫 번째 쿼리의 결과 중에서, 두 번째 쿼리에 해당되는 것만 조회하기 위해서 in을 사용한다. 전화번호가 없는 사람만 찾아보자
select name, concat(mobile1,mobile2) as '전화번호' from usertbl
where name in (select name from usertbl where mobile1 is null);
