18. MySQL SQL 고급 - Outer, Cross, Self Join 및 Union

OUTER JOIN(외부 조인)
Outer join은 조인의 조건에 만족되지 않는 행까지도 포함시키는 것.

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 OUTER JOIN buytbl B
        	ON U.userID = B.userID
    ORDER BY U.userID;

LEFT OUTER JOIN문의 의미를 왼쪽 테이블(usertbl)의 것은 모두 출력되어야 한다 정도로 해석하면 기억하기 쉬울 것이다. 또 LEFT OUTER JOIN을 줄여서 LEFT JOIN이라고만 써도 된다.

LEFT OUTER JOIN의 결과임.

위와 동일한 결과를 얻기 위해서 구문을 RIGHT OUTER JOIN으로 바꾸려면 단순히 왼쪽과 오른쪽 테이블의 위치만 바꿔주면 된다.

INNER JOIN의 활용 중에서 구매한 기록이 있는 우수 회원들의 목록만을 뽑는 것을 해 보았었다. 이번에는 한 번도 구매한 적이 없는 유령(?)회원의 목록을 뽑아보자.

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;

구매 기록이 없는 회원의 명단임.

이번에는 FULL OUTER JOIN(전체 조인 또는 전체 외부 조인)에 대해서 살펴보자. FULL OUTER JOIN은 LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합쳐진 것이라고 생각하면 된다. 그냥 줄여서 FULL OUTER JOIN이라고 부른다.
즉, 한쪽을 기준으로 조건과 일치하지 않는 것을 출력하는 것이 아니라, 양쪽 모두에 조건이 일치하지 않는 것을 모두 출력하는 개념이다. 활용도는 낮다.

<실습>
LEFT/RIGHT/FULL OUTER JOIN을 실습하자.

앞의 실습(17)에서 3개의 테이블을 가지고 INNER JOIN 했던 결과를 OUTER JOIN으로 고려하자. 또 두개의 조인을 고려한 FULL JOIN을 테스트하자.

step1. 앞에서 했던 실습(17)의 학생을 기준으로 출력된 결과를 보면, 동아리에 가입하지 않은 학생 성시경을 출력이 안됐다. OUTER JOIN으로 동아리에 가입하지 않은 학생도 출력되도록 수정하자. 간단히 INNER JOIN을 LEFT OUTER JOIN으로 변경하면 된다.

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;

SELECT C.clubName, C.roomNo, S.stdName, S.addr
	FROM stdtbl S
    	LEFT OUTER JOIN stdclubtbl SC
        	ON SC.stdName = S.stdName
        RIGHT OUTER JOIN clubtbl C
        	ON SC.clubName = C.clubName
    ORDER BY C.clubName;

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

CROSS JOIN
한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능.
그래서 CROSS JOIN의 결과 개수는 두 테이블 개수를 곱한 개수가 된다.

회원 테이블의 첫 행이 구매 테이블의 모든 행과 조인되고, 그것을 회원 테이블의 모든 행이 반복하는 것이다. 그러므로 회원 테이블의 개수인 10개와 구매 테이블의 개수인 12개가 곱해져서 120개의 결과가 된다. 이러한 CROSS JOIN을 카티션곱이라고도 부른다.

회원 테이블과 구매 테이블의 CROSS JOIN 구문은 다음과 같다.

USE sqldb;
SELECT *
	FROM buytbl
    	CROSS JOIN usertbl;

CROSS JOIN을 하려면 위와 동일한 구문으로 WHERE 구문 없이 FROM절에 테이블 이름들을 나열해도 된다. 이 역시 별로 권장하는 바는 아니다.
SELECT
FROM buytbl, usertbl;

CROSS JOIN에는 ON 구문을 사용할 수 없다. CROSS JOIN의 용도는 테스트로 사용할 많은 용량의 데이터를 생성할 때 주로 사용한다. 예를 들어, employees DB에서 약 30만 건이 있는 employees 테이블과 약 44만 건이 있는 titiles을 CROSS JOIN시키면, 30만 x 44만 = 약 1300억 건의 데이터를 생성할 수 있다. 진짜로 데이터를 생성하면 시스템이 다운되거나 디스크 용량이 모두 찰 수 있으므로 COUNT(*)함수로 개수만 카운트를 해보자.

USE employees;
SELECT COUNT(*) AS '데이터개수'
	FROM employees
    	CROSS JOIN titles;	

SELF JOIN(자체 조인)
별도의 구문이 있는 것이 아니라 자기 자신과 자기 자신이 조인한다는 의미다. SELF JOIN을 활용하는 경우의 대표적인 예가 조직도와 관련된 테이블이다.

위 조직도를 테이블로 나타내 보자.

이부장을 보면 이부장은 직원이므로 직원 이름 열에 존재한다. 그러면서 동시에 우대리와 지사원의 상관이어서 상관 이름 열에도 존재한다. 만약, 우대리의 상관이 구내번호를 알려면 EMP열과 MANAGER열을 조인해야 이부장의 구내번호를 알 수 있다.

<실습>

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
    WHERE A.emp = '우대리';

UNION / UNION ALL / NOT IN / IN

UNION 형식과 사용 예

SELECT 문장1
	UNION [ALL]
SELECT 문장2

대신 SELECT 문장1과 SELECT 문장2의 결과 열의 개수가 같아야 하고, 데이터 형식도 각 열 단위로 같거나 서로 호환되는 데이터 형식이어야 한다. 당연히 문장1의 결과는 INT인데, 문장2의 결과는 CHAR이라면 오류가 발생할 것이다. 또한 열 이름은 문장1의 열 이름을 따른다. UNION만 사용하면 중복된 열은 제거되고 데이터가 정렬되어 나오며, UNION ALL을 사용하면 중복된 열까지 모두 출력된다.

NOT IN은 첫 번째 쿼리의 결과 중에서, 두 번째 쿼리에 해당하는 것을 제외하기 위한 구문이다. 예로 sqldb의 사용자를 모두 조회하되 전화가 없는 사람을 제외하고자 한다면 다음과 같이 사용하면 된다.

SELECT name, CONCAT(mobile1, mobile2) AS '전화번호' FROM usertbl
	WHERE name NOT IN (SELECT name FROM usertbl WHERE mobile1 IS NULL);

NOT IN과 반대로 첫 번째 쿼리의 결과 중에서, 두 번째 쿼리에 해당되는 것만 조회하기 위해서는 IN을 사용하면 된다. 예로 전화가 없는 사람만 조회하고자 할 때 다음과 같이 사용한다.

SELECT name, CONCAT(mobile1, mobile2) AS '전화번호' FROM usertbl
	WHERE name IN (SELECT name FROM usertbl WHERE mobile1 IS NULL);