[SQL] JOIN(INNER JOIN, OUTER JOIN, CROSS JOIN, SELF JOIN)

조인이란?

두 개의 테이블을 합쳐서 하나의 테이블로 만들어 내는 것을 말한다.

 

 

내부 조인

가장 많이 쓰이고 일반적인 조인 방법이다.

'내부'를 생략하고 그냥 조인이라고도 부른다.

 

조인을 알기 전에 일단 일대다 관계, 기본키와 외래키에 대해서 알아야한다.

쇼핑몰의 회원과 구매 테이블을 경우를 생각해보자,

 

기본키

테이블의 데이터를 식별할 수 있는 중복되지 않는, 즉 1개만 존재하는 값이다.

회원 테이블에서 회원의 아이디가 기본키의 역할을 할 수 있고

구매 테이블에선 AUTO_INCREMENT로 중복이 없는 숫자를 입력할 수 있다.

 

외래키

회원 테이블과 구매 테이블은 서로 관계를 가지고 있다. 

왜냐하면 구매 테이블에는 어떤 회원이 구매를 한 건지 회원 아이디가 있을 것이기 때문이다.

한 회원이 여러 개의 제품을 구매할 경우 구매 테이블에 같은 회원 아이디를 갖는 데이터가 존재할 것이다.

또한 구매 테이블의 회원 아이디는 회원 테이블의 회원 아이디에 있는 아이디만 존재할 것이다.

이렇게 두 테이블을 연결하는 역할을 하는 회원 아이디를 외래키라고 한다.

 

일대다 관계

위에서 설명했듯이 회원 테이블에선 유일한 회원이 구매 테이블에서 여러 개의 데이터를 가질 수 있다.

이렇게 한 테이블의 오직 하나의 데이터가 다른 테이블에서 여러개의 데이터와 관계를 맺는 것을 일대다 관계라고 한다.

보통 기본키를 갖는 테이블이 '일'이고 외래키를 갖는 테이블이 '다'의 위치이다.

 

 

이제 다시 조인으로 돌아가자.

 조인(내부 조인)의 기본 문법은 아래와 같다.

--WHERE절은 생략 가능
SELECT '열 목록'
FROM '첫 번째 테이블'
    INNER JOIN '두 번째 테이블'
    ON '조인될 조건'
WHERE '검색 조건'

 

위의 문법을 회원(member), 구매(buy) 테이블에 적용해보자.

--buy테이블과 member 테이블을 조인하는데 buy테이블의 mem_id와 member테이블의 mem_id가 같아야 한다
SELECT * 
   FROM buy
     INNER JOIN member
     ON buy.mem_id = member.mem_id ;

 

내부 조인은 두 테이블에 모두 존재하는 데이터를 기준으로 합치기 때문에 구매이력이 없는 회원은 조인 결과에 존재하지 않는다.

구매이력이 없는 회원도 함께 확인하고 싶은 경우 외부 조인을 사용하자.

 

 

 

외부 조인

두 테이블을 조인할 때 한쪽 테이블에만 있는 데이터도 결과로 보여준다는 점에서 내부 조인과 다르다.

기본 문법은 아래와 같다.

--첫 번째 테이블이 LEFT, 두 번째 테이블이 RIGHT이다. 
SELECT '열 목록'
FROM '첫 번째 테이블'
    [LEFT | RIGHT | FULL] OUTER JOIN '두 번째 테이블'
    ON '조인할 조건'
WHERE '검색 조건' ; --WHERE절은 생략 가능

 

LEFT OUTER JOIN은 첫 번째 테이블의 데이터를 모두 나오게 조인하는 방식이고

RIGHT OUTER JOIN은 두 번째 테이블의 데이터를 모두 나오게 조인하는 방식이다.

FULL OUTER JOIN은 양쪽 테이블의 데이터를 모두 나오게 조인하는 방식이다.

왼쪽과 오른쪽은 FROM절에 어느 테이블을 위치하게 하는지로 조정가능하므로 LEFT와 RIGHT 중 하나만 사용해도 된다.

 

예시

--member테이블 기준으로 외부 조인 했기 때문에 구매이력이 없는 회원은 prod_name이 null로 만들어진다.
SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
   FROM member M
     LEFT OUTER JOIN buy B
     ON M.mem_id = B.mem_id;

--WHERE절을 사용해 구매이력이 없는 회원만 추출할 수도 있다.
SELECT DISTINCT M.mem_id, B.prod_name, M.mem_name, M.addr
   FROM member M
     LEFT OUTER JOIN buy B
     ON M.mem_id = B.mem_id
   WHERE B.prod_name IS NULL

 

 

 

상호 조인(CROSS JOIN)

두 테이블의 모든 데이터를 조인하는 것이다.

첫 번째 테이블의 1번 행을 두 번째 테이블의 모든 행과 조인을 하는 방식을 반복한다.

그래서 조인 결과는 첫 번째 테이블의 행의 수(N) X 두 번째 테이블의 행의 수(M)이 나온다.

무의미하게 조인을 하는 것이기 때문에 결과에도 의미는 없지만 대용량의 테스트용 데이터를 만드는데 사용된다.

-- 상호 조인 기본 문법
SELECT * 
   FROM '첫 번째 테이블'
     CROSS JOIN '두 번째 테이블' ;
     

-- 두 테이블을 크로스 조인한 테스트 데이터용 테이블을 만들 수 있다.
CREATE TABLE mock_data
    SELECT * 
   		FROM '첫 번째 테이블'
     		CROSS JOIN '두 번째 테이블' ;

 

 

 

 

자체 조인(SELF JOIN)

하나의 테이블로 조인을 하는 특이한 방식이다.

테이블 안의 데이터끼리 관계가 있을 때 사용할 수 있다.

예를 들면 조직도의 경우 직원의 직속 선후임 관계를 나타나 있다면 자체 조인을 통해 직속 선후임의 정보를 나타낼 수 있다.

직원(기본키)	직속 선임	연락처
A	null	1111
B	A	2222
C	B	3333

--직원의 직속선임과 직속선임의 연락처를 갖는 테이블을 만들 수 있다.
SELECT A.emp "직원" , B.emp "직속선임", B.phone "직속선임연락처"
   FROM emp_table A
      INNER JOIN emp_table B
         ON A.manager = B.emp;