[혼공S] CH4-(2). 두 테이블을 묶는 조인

이제부터는 두 개의 테이블이 서로 관계되어 있는 상태를 고려해서 묶는 경우를 다뤄본다.

 

조인(join)란, 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어내는 것을 말한다.

예를 들어, 회원 테이블엔는 회원의 이름과 연락처가 있고, 구매 테이블에는 회원이 구매한 물건에 대한 정보가 있을 때 이 두 개를 함께 묶어서 배송에 필요한 테이블을 만드는 작업이 필요하다.

 

내부조인

일대다 관계의 이해 

일대다 관계(one to many)는 한쪽 테이블에는 하나의 값만 존재하지만, 연결된 다른 테이블에는 여러 개의 값이 존재하는 관계를 말한다. 

예를 들어, member 테이블에서 블랙핑크는 'BLK'로 하나의 값만 존재하여 PRIMARY KEY로 지정하였지만, buy 테이블에서는 BLK의 구매 내역은 여러 값이 존재한다. 그래서 buy 테이블에서 아이디는 PRIMARY KEY가 아닌 FOREIGN KEY로 설정하였다.

※ 일대다 관계는 주로 기본 키(PK)와 외래 키(FK) 관계로 이루어져 있어, PK-FK 관계라고 불리기도 함. 

 

내부조인의 기본

SELECT <열 목록>
   FROM <첫 번째 테이블>
      INNER JOIN <두 번째 테이블>
      ON <조인될 조건>
   [WHERE 검색 조건]

 buy 테이블에서 GRL 이라는 아이디를 가진 사람의 구매 내역과 휴대전화 및 주소 정보를 join해보도록 하자.

-- GRL이라는 아이디를 가진 사람의 구매 내역과 휴대전화 및 주소 정보
SELECT * 
   FROM buy
      INNER JOIN member
      ON buy.mem_id = member.mem_id
   WHERE buy.mem_id = 'GRL' ;

이때 where 조건이 없다면, buy 테이블에서의 모든 행이 member 테이블과 결합하게 된다.

-- 만약 where 조건이 없다면?
SELECT * 
   FROM buy 
      INNER JOIN member 
      ON buy.mem_id = member.mem_id ;

 

내부 조인의 간결한 표현

두 개의 테이블을 조인한 후에 필요한 열만 추출해보도록 하자. 이때 SELECT에 표시한 mem_id는 buy 테이블에도 있고 member 테이블에도 있기 때문에 둘 중 어떤 테이블의 것을 기준으로 할 건지 명시해줘야 한다. 안그러면 에러가 나게 된다. 

-- 필요한 열만 추출
SELECT buy.mem_id, mem_name, prod_name, addr, CONCAT(phone1, phone2) '연락처'
   FROM buy
      INNER JOIN member 
      ON buy.mem_id = member.mem_id ;

buy.mem_id 처럼 테이블 이름을 명시해야 할 때가 있는데, 이를 간결하게 표현하기 위해서 별칭(alias)를 줄 수 있다.

-- 데이터 테이블 별칭 주기
SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(M.phone1, M.phone2) '연락처'
   FROM buy B 
      INNER JOIN member M 
      ON B.mem_id = M.mem_id ;

 

내부조인의 활용

전체 회원의 구매내역과 휴대전화 및 주소 정보를 출력해보도록 하자. 여기서 '전체 회원'이라는 것에 주목하자.

-- 전체 회원의 구매 내역과 휴대전화 및 주소 정보
-- M.mem_id로 열을 지정한 것을 확인
SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
   FROM buy B 
      INNER JOIN member M
	  ON B.mem_id = M.mem_id 
   ORDER BY M.mem_id ;

여기서 '전체 회원'이라는 것에 주목해야 하는데, 해당 테이블에서 구매를 하지 않은 회원 아이디는 출력되지 않는다. 구매하지 않은 회원도 출력하고 싶을 때에는 내부조인을 사용하면 안된다. 내부조인은 두 테이블에 모두 있는 내용만 출력되기 때문이다. 구매하지 않은 회원의 목록도 출력하기 위해서는 외부조인을 사용해야 한다.

 

중복된 결과 1개만 출력하기

예를 들어, '우리 사이트에서 한번이라도 구매한 기록이 있는 회원들에게 감사인사를 보내자' 라고 할 때, 구매 내역이 있는 아이디 하나만 출력하면 되는데 이때 사용할 수 있는 것은 3장에서 배웠던 DISTINCT이다. 

-- 중복된 결과 1개만 출력하기
SELECT DISTINCT M.mem_id, M.mem_name, M.addr
   FROM buy B 
     INNER JOIN member M 
     ON B.mem_id = M.mem_id 
   ORDER BY M.mem_id ;

 

외부조인

외부 조인의 기본

외부조인(outer join)은 두 테이블을 조인할 때 필요한 내용이 한 쪽에만 있어도 결과를 추출할 수 있다. 

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

전체 회원의 구매기록(구매 기록이 없는 회원의 정보도 함께)을 출력해보도록 하자. 

-- 외부조인 
SELECT M.mem_id, M.mem_name, B.prod_name, M.addr 
   FROM member M 
     LEFT OUTER JOIN buy B -- 왼쪽에 있는 M 테이블을 기준으로 outer join
     ON M.mem_id = B.mem_id 
   ORDER BY M.mem_id ;
   
-- 같은 결과 w/ right outer join
SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
   FROM buy B 
      RIGHT OUTER JOIN member M 
      ON M.mem_id = B.mem_id 
   ORDER BY M.mem_id ;

ITZ의 경우는 회원의 정보는 있는데 구매이력이 없어서 prod_name에 NULL값이 들어간 것을 볼 수 있다.

같은 테이블을 RIGHT OUTER JOIN을 이용해서 하려면 테이블의 위치만 바꿔주면 된다.

 

외부 조인의 활용

방금 내부조인을 이용해서 구매한 기록이 있는 회원들만 추출할 수 있었다. 이번에는 반대로 회원으로 가입만 하고, 한 번도 구매한 적 없는 회원의 목록을 추출하고자 한다.

-- 회원 가입 후, 구매이력이 한번도 없는 회원 추출
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  -- prod name이 null인 경우만 출력 
     ORDER BY M.mem_id ;

추가로, FULL OUTER JOIN은 왼쪽 외부조인과 오른쪽 외부조인이 합쳐진 것이라고 생각하면 되고, 왼쪽이든 오른쪽이든 한 쪽에 들어있는 내용이면 출력하는 것이다.

 

기타조인

상호 조인(cross join)은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능이다. 

즉, 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 것과 같다. 

-- CROSS JOIN 
SELECT * 
   FROM buy 
      CROSS JOIN member ;

상호 조인은 ON구문을 사용할 수 없고, 랜덤으로 조인하기 때문에 결과의 내용은 의미가 없다. 상호조인은 테스트하기 위해 대용량의 데이터를 생성할 때 주로 사용한다. 

-- 상호조인을 통한 대용량테이블 생성
-- world DB의 city 테이블과 sakila DB의 inventory 테이블을 cross join
SELECT COUNT(*) "데이터 개수" 
   FROM sakila.inventory
      CROSS JOIN world.city ;

실제로 대용량의 테이블을 만들기 위해서는 CREATE TABLE ~ SELECT문을 사용한다.

-- 대용량 테이블 만들기
CREATE TABLE cross_table 
   SELECT * 
      FROM sakila.actor 
         CROSS JOIN world.country ; 
         
SELECT * FROM cross_table LIMIT 5 ;

자체 조인

자체 조인(self join)은 자신이 자신이 조인한다는 뜻이다. 

 

Reference

혼자 공부하는 SQL, 우재남 지음