JOIN

 조인은 관계형 DB의 무결성에 의해 존재하는 두 개 이상의 테이블을 조건을 부여해 합쳐 필요에 따라 만드는 하나의 집합이다. 실습에서 계속해서 사용하고 있는 sqldb를 보면 userid가 pk인 usertbl과 pk가 아닌 fk로 설정된 buytbl이 일대다의 관계를 유지하고 있다. 다시 말하자면 usertbl에서는 독자적으로 존재하는 개별 userid가 buytbl에서는 구매기록이기에 여러차례 나올 수 있어 한 유저의 개인정보와 다수의 구매기록이 관계를 가지고 있다는 것이다. 이는 앞서 말했듯이 데이터가 중복되는 것을 막고 무결성을 보존하기 위한 관계형 데이터베이스의 특징이다. 

 

 두 개 이상의 테이블에서 관계를 가지는 정보들을 모아서 봐야되는 경우들이 생기는데 이를 상황에 따라 알맞게 조인을 하는 능력이 중요하다. 가장 많이 쓰이는 조인으로는 내부조인이 있는데, 내부조인은 두 테이블에서 지정한 열들 중에서 조건이 부합하는 공통분모를 합치는 것이다. 대부분 조인은 이 내부조인을 뜻하는 것이며, 이외에도 조인의 조건이 만족되지 않아도 특정 테이블의 데이터는 모두 포함하는 외부조인, 한 테이블의 한 행에 반대측 모든 행과 연결하는 상호조인, 한 테이블 내의 데이터 끼리 조인하는 자체조인이 있다. 가장 보편적으로 사용되고 중요도가 높은 내부조인의 사용법부터 봐보자.

 

내부조인

-- 내부조인 기본 구조
/*
SELECT <열 목록>
FROM <첫 번째 테이블>
	INNER JOIN <두 번째 테이블>
		ON <조인될 조건>
[WHERE 검색조건]
*/

-- 아이디가 JYP인 회원이 구매한 물건을 발송하기 위해서 주소가 필요하다. 이때 usertbl과 buytbl을 합치는 과정을 보면 이러하다. 
USE sqldb ;
SELECT *
	FROM buytbl
		INNER JOIN usertbl
			ON buytbl.userID = usertbl.userID
	WHERE buytbl.userID = 'JYP' ;

-- 특정 조건을 삽입하지 않았을 경우: 모든 행을 대조해서 검색한다
USE sqldb ;
SELECT *
	FROM buytbl
		INNER JOIN usertbl
			ON buytbl.userID = usertbl.userID
	ORDER BY num ; -- buytbl의 num(pk) 순서로 정렬하기 위함. 생략해도 무방하다.

-- 필요한 열만 추출할 경우: (아이디, 이름, 구매물품, 주소, 연락처)
USE sqldb ;
SELECT buytbl.userID, name, prodName, addr, CONCAT(mobile1, mobile2) AS '연락처'
	FROM buytbl
		INNER JOIN usertbl
			ON buytbl.userID = usertbl.userID
	ORDER BY num ;

-- 두 테이블을 조인하는 경우이므로 어느 테이블의 열인지 명확하게 구분하는 것이 더 정확한 코드를 만들 수 있는 방법이다. (별칭 지정하는 법)
USE sqldb ;
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 ;
-- 여러개의 테이블을 사용하는 경우에는 별칭을 지정해 해당 열이 어느 테이블에서 오는지 지칭하는 습관을 들이는 것이 좋다.

-- 역으로 회원 테이블에서 JYP라는 회원이 구매한 품목을 보는 조인을 만들어보자.
USE sqldb ;
SELECT U.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
	FROM usertbl U
		INNER JOIN buytbl B
			ON U.userID = B.userID
	WHERE U.userID = 'JYP' ;

-- 구매한 적이 있는 회원의 정보만 출력
SELECT DISTINCT U.userID, U.name, U.addr
	FROM usertbl U
		INNER JOIN buytbl B
			ON U.userID = B.userID
	ORDER BY U.userID ;
-- OR --
SELECT U.userID, U.name, U.addr
	FROM usertbl U
    WHERE EXISTS (
		SELECT *
        FROM buytbl B
        WHERE U.userID = B.userID ) ;
        
-- 3개의 테이블 조인 (다대다 관계)
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, C.clubName, C.roomNo
	FROM stdtbl S
		INNER JOIN stdclubtbl SC
			ON S.stdName = SC.stdName
		INNER JOIN clubtbl C
			ON SC.clubName = C.clubName
	ORDER BY S.stdName ; -- 1차적으로 S 테이블과 SC 테이블을 결합하고, 2차적으로 전의 조인과 C 테이블을 조인하면 된다.

-- 동아리를 기준으로 나열
SELECT C.clubName, C.roomNo, S.stdName, S.addr
	FROM clubtbl C
		INNER JOIN stdclubtbl SC
			ON C.clubName = SC.clubName
		INNER JOIN stdtbl S
			ON SC.stdName = S.stdName
	ORDER BY C.clubName ;

 

외부조인

-- 외부조인 기본 구조
/*
SELECT <열 목록>
FROM <첫 번쨰 테이블(LEFT 테이블)>
	<LEFT ¦ RIGHT ¦ FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
		ON <조인될 조건>
[WHERE 검색조건] ;
*/

USE sqldb ;
SELECT U.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
	FROM usertbl U -- LEFT TABLE
		LEFT OUTER JOIN buytbl B -- RIGHT TABLE
			ON U.userID = B.userID
	ORDER BY U.userID ;
-- LEFT OUTER JOIN은 LEFT 테이블(위에선 usertbl)의 것은 모두 출력한다는 의미

-- 구매이력이 없는 유령회원의 조인 테이블을 만들자. 
USE sqldb ;
SELECT U.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
	FROM usertbl U
		LEFT OUTER JOIN buytbl B
			ON U.userID = B.userID
	WHERE B.prodName IS NULL 
    ORDER BY U.userID ;

-- 동아리가 없는 학생도 출력하도록 이전 실습 쿼리에서 수정해보자.
USE sqldb ;
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
	ORDER BY S.stdName ;

-- 동아리를 기준으로 해서 가입자가 없는 동아리를 포함한 값을 출력해보자.
USE sqldb ;
SELECT C.clubName, C.roomNo, S.stdName, S.addr
	FROM stdtbl S
		LEFT OUTER JOIN stdclubtbl SC
			ON S.stdName = SC.stdName
		RIGHT OUTER JOIN clubtbl C 
			ON SC.clubName = C.clubName
	ORDER BY C.clubName ;

-- 위의 두 결과를 하나로 합쳐보자.
USE sqldb ;
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 -- UNION은 두 결과값들을 하나의 결과값으로 합쳐주는 역할을 한다.
SELECT S.stdName, S.addr, C.clubName, C.roomNo
	FROM stdtbl S
		LEFT OUTER JOIN stdclubtbl SC
			ON S.stdName = SC.stdName
		RIGHT OUTER JOIN clubtbl C
			ON SC.clubName = C.clubName ;

 

상호조인 (Cross Join)

/* CROSS JOIN은 테이블1의 모든 행에 테이블 2의 모든 행을 조인하는 것이다.
	따라서 크로스 조인을 하면 (테이블1의 행 개수) X (테이블2의 행 개수) 만큼의 새로이 합쳐진 행이 만들어진다 */

USE sqldb ;
SELECT *
	FROM buytbl
		CROSS JOIN usertbl ;
-- 따라서 크로스 조인은 대용량의 테스트 데이터를 만들 때 사용한다.

-- 예시로 약 30만 건의 employees 테이블과 약 44만 건의 데이터가 있는 titles 테이블을 크로스 조인하면 몇개의 행이 만들어지는지 확인하자.
USE employees ;
SELECT COUNT(*) AS '데이터개수'
	FROM employees
		CROSS JOIN titles ;
-- 133,003,039,392(약1330억 개의 데이터)개의 데이터가 생성된다.

 

자체조인

/* 조직도 테이블을 예시로 만들어보자
	가상의 조직도 테이블 제작 */
USE sqldb ;
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 ;