MySQL - JOIN 정리 (INNER, OUTER, CROSS, SELF JOIN)

조인이란 ?

조인(Join)은 SELECT와 더불어 가장 많이 사용하는 옵션 중 하나이고, 두 개 이상의 테이블을 묶어서 하나의 결과 집합으로 만들어 내는 것입니다. 즉, 서로 다른 테이블에서 데이터를 가져올 때 사용하는 것이 조인(Join)입니다.

 

INNER JOIN(내부 조인)

INNER JOIN은 조인 중 가장 많이 사용됩니다. 따라서 보통 JOIN을 얘기할 때는 INNER JOIN을 말하는 것입니다. 예를 들어, 쇼핑몰 사이트에서 사용자가 물건을 구매하기 위해서는 구매 테이블에 물품, 수량 등을 입력할 것이며, 외래키인 ID와 함께 삽입될 것입니다. 물품을 구매하고 배송을 할 때는 그 구매 테이블에 있는 정보 뿐만 아니라 사용자 테이블에 있는 배송 주소, 전화번호 등을 함께 알아야 합니다. 이 때 사용하는 것이 INNER JOIN입니다.

SELECT <열 목록>
FROM <기준 테이블>
    INNER JOIN<참조할 테이블>
    ON <조인 조건>
[WHERE 검색조건]

위 형식에서 INNER JOIN이 아닌 그냥 JOIN으로 써도 INNER JOIN으로 인식합니다.

USE shopDB
SELECT *
FROM buyTBL
    INNER JOIN userTBL
    ON buyTBL.userID = userTBL.userID
WHERE buyTBL.userID = 'LEE';

이렇게 작성하면 buyTBL의 userID와 userTBL의 userID가 같은 테이블끼리 합쳐진 결과를 검색하게 됩니다. 그리고 제일 아랫줄에 WHERE에서 userID가 'LEE'라는 조건을 두었으니, buyTBL의 userID가 'LEE'인 행들만 검색할 것입니다. 

 

여기서 모든 열을 검색할 필요는 없기 때문에 SELECT *이 아니라 buyTBL.userID, prodID, amount, name, addr, phoneNumber 라던지 필요한 열만 검색하면 될 것입니다. 

 

JOIN은 두 개 이상의 테이블을 결합하기 때문에 결합하는 테이블들이 동일한 열을 가지고 있다면 '테이블이름.열이름' 형식으로 테이블명을 명시해줘야 에러가 발생하지 않습니다. 가장 안전한 방법은 모든 열이름에 테이블명을 붙혀주는 것입니다.

SELECT BuyTBL.userID, BuyTBL.prodID, BuyTBL.amount, UserTBL.name, UserTBL.addr, UserTBL.phoneNumber
FROM buyTBL B
INNER JOIN userTBL U
ON B.userID = U.userID

하지만 이렇게 하면 코드가 길어져 복잡해집니다. 검색할 열이 많다면 코드는 더 길어질 것입니다.

SELECT B.userID, B.prodID, B.amount, U.name, U.addr, U.phoneNumber
FROM buyTBL B
INNER JOIN userTBL U
ON B.userID = U.userID

간결하게 하기 위해서 각 테이블에 별칭(Alias)를 줄 수 있습니다. 가독성과 안전성을 위해 적극 권장하는 방식입니다.

SELECT *
FROM buyTBL, userTBL
WHERE buyTBL.userID = userTBL.userID;

단순히 FROM에 테이블을 여러 개 작성해서 조인하는 방법도 있지만 호환성 등의 문제로 권장되지 않습니다.

 

OUTER JOIN(외부 조인)

INNER JOIN은 양쪽 테이블에 모두 내용이 있는 경우에만 결과가 검색되고, OUTER JOIN은 한쪽 테이블에만 내용이 있어도 결과가 검색됩니다. 자주 사용되지는 않지만 가끔 유용하게 사용되는 방식입니다.

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT)>
    <LEFT | RIGHT | FULL> [OUTER] JOIN <두 번째 테이블(RIGHT)>
    ON <조인 조건>
[WHERE 검색조건];

OUTER JOIN은 기준 테이블 내용의 누락 없이 검색하면서도, 대상 테이블의 내용을 가져올 수 있습니다. 두 가지 테이블의 내용을 한 번에 가져올 수도 있습니다.

 

OUTER JOIN 앞에 LEFT를 쓰면 첫 번째 테이블의 내용은 두 번째 테이블과 연계되는 내용이 없더라도 모두 검색되어야 한다는 뜻입니다. RIGHT는 두 번째 테이블의 내용은 모두 검색되어야 한다는 뜻이고 FULL은 모든 테이블의 내용이 모두 검색되어야 한다는 뜻입니다. OUTER는 생략 가능합니다.

SELECT U.userID, U.addr, U.phoneNumber, B.prodID
FROM userTBL U
    LEFT OUTER JOIN buyTBL B
    ON U.userID = B.userID
WHERE B.prodID IS NULL
ORDER BY U.userID;

OUTER JOIN을 이용하면 구매내역이 없는 유저만을 검색할 수 있습니다. 위는 예시입니다.

 

CROSS JOIN(상호 조인) 

CROSS JOIN은 한쪽 테이블의 행 하나당 다른 쪽 테이블의 모든 행을 하나씩 모든 행들을 각각 조인합니다.

즉, A 테이블의 1번 행을 B 테이블의 1번 행에 조인 시키고, 다음은 A 테이블의 1번 행을 B 테이블의 2번 행에 조인시키고 ...생략... 이를 모든 A 테이블의 행에 각각 모든 B 테이블의 행들에 조인합니다. CROSS JOIN의 결과 행의 개수는 [A 테이블 행의 개수 X B 테이블 행의 개수]가 됩니다.

CROSS JOIN은 카티션 곱(Catesian Product)이라고도 부릅니다. 

SELECT * FROM ATable
CROSS JOIN BTable;

기본적인 CROSS JOIN 구문입니다. INNER과 OUTER 조인과 달리 ON 구문은 사용하지 않습니다. SELECT * FROM ATable, BTable; 형식으로 작성할 수도 있는데 호환성 등의 이유로 권장되지 않습니다.

 

SELF JOIN(자체 조인)

SELF JOIN은 자신에게 조인하는 것입니다. 같은 테이블에 두 번 참조해야 하는 경우도 있습니다.

곤충	천적	수명
거미	참새	1년
메뚜기	거미	6개월
...

 

곤충 도감 테이블이 있다고 가정합시다. 이 테이블에서 '메뚜기'의 천적 곤충의 이름, 천적, 수명을 검색하려면 어떻게 해야 할까요?

SELECT 이름, 천적, 수명
FROM 곤충테이블
WHERE 이름 = ( SELECT 천적
               FROM 곤충테이블
               WHERE 곤충 = '메뚜기');

위의 형식으로 서브 쿼리문을 이용할 수 있습니다.

SELECT B.곤충, B.천적, B.수명
FROM 곤충도감 A
    INNER JOIN 곤충도감 B
    ON A.천적 = B.곤충
WHERE A.곤충 = '메뚜기';

SELF JOIN 시켜서 정보를 확인할 수도 있습니다. 두 가지 다 결과는 [거미, 참새, 1년]이 나올 것입니다. SELF JOIN을 사용할 때는 반드시 별칭을 이용해서 논리적으로 두 개의 테이블을 분리시켜야 합니다.