조인(Join) - 내부(inner), 외부(outer), 상호(cross), 자체(self)

• 정의
  - 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 의미함.
  
  
• 종류
  - 내부 조인
  - 외부 조인
  
  

내부 조인(Inner join)

- 두 테이블을 연결할 때 가장 많이 사용되는 것이 내부 조인으로, 일반적으로 조인이라 하면 내부 조인을 의미하는 것임.

- 두 테이블의 조인(join)을 위해서는 테이블이 일대다(one to may) 관계로 연결되어야 함.

 

cf. 조인(join)은 3개 이상의 테이블로도 할 수 있으나, 보통 2개의 테이블로 하는 경우가 일반적임.

• 일대다 관계 (one to many) 
  - 일대다 관계는 주로 기본 키(Primary Key; PK)와 외래 키(Foreign Key; FK)의 관계로 맺어져 있음. (= PK-FK 관계)
  Ex. 인터넷 쇼핑몰에서 각 회원들은 여러 개의 상품을 구매할 수 있음에 따라 회원 테이블에서의 사용자 아이디(ID)는 오직 하나만(one) 존재할 수 있기에 PK로 지정되지만, 구매 테이블에서의 사용자 아이디(ID)는 여러 개(many) 있을 수 있어서 FK로 지정됨.
  - 예외적으로 one to many 관계(= PK-FK 관계)가 아니어도 되는 조인도 있음. (→ 대표적으로 상호 조인이 이에 해당됨.)
   
• 내부 조인(inner join)의 형식
  - 참고로 INNER JOIN을 그냥 JOIN 이라고만 써도 INNER JOIN으로 인식함. 

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

 

▼ 구매 테이블(buy)에는 물건을 구매한 회원의 아이디와 물건의 정보가 저장되어 있고, 물건을 배송하기 위하여 구매한 회원의 주소 및 연락처를 확인해야 함. 이와 같은 상황에서 회원들의 주소 및 연락처와 같은 정보가 저장되어 있는 회원 테이블(member)과 구매 테이블(buy)을 결합하는 내부 조인(inner join)을 활용할 수 있음.

USE market_db;
SELECT *
   FROM buy
   INNER JOIN member
   ON buy.mem_id = member.mem_id
WHERE buy.mem_id = 'GRL';

- USE market_db; ← market 데이터베이스에 접근함.

- SELECT * FROM buy ← buy 테이블을 조회하여 모든 열(column)의 정보를 추출함.

- INNER JOIN member  ON buy.mem_id = member.mem_id ← buy 테이블의 회원 아이디와 member 테이블의 회원 아이디가 동일하다는 조건에 따라 데이터를 추려서 member 테이블과 결합함.

- WHERE buy.mem_id = 'GRL'; ← GRL 이라는 회원 아이디를 가진 사용자의 구매 목록 뿐만 아니라, member 테이블에 저장되어 있었던 이름, 주소, 연락처 등의 정보 또한 조회할 수 있음.  

 

• 내부 조인(inner join)을 간결하게 표현하는 방법
  - 테이블(table) 이름에 별칭(alias)을 붙여 비교적 간결하게 표현할 수 있음.

SELECT buy.mem_id, member.mem_id, member.addr, CONCAT(member.phone1, member.phone2) '연락처'
   FROM buy
     INNER JOIN member
     ON buy.mem_id = member.mem_id;
     
-- 별칭(alias) 붙여서 다음과 같이 간략하게 표현할 수 있음.
SELECT B.mem_id, M.mem_id, M.addr, CONCAT(M.phone1, M.phone2) '연락처'
   FROM buy B -- 테이블명(buy)에 별칭(B) 붙여줌
     INNER JOIN member M -- 테이블명(member)에 별칭(M) 붙여줌
     ON B.mem_id = M.mem_id;

 

• 내부 조인(inner join)의 특징
  - 두 테이블에 모두 있는 내용만 출력할 수 있음.
  - 예를 들어, 온라인 쇼핑몰에서 고객 인적사항이 저장된 member 테이블과 고객 구매 현황이 저장된 buy 테이블이 있다고 한다면 내부 조인을 활용하여 회원의 구매 기록을 조회 및 출력할 수 있음.
  → 이는 고객 아이디가 member 테이블 및 buy 테이블 모두에 저장되어 있기에 내부 조인(inner join)을 활용하는 것이 가능한 것임. 
  - 만약, 두 개의 테이블 중에서 한 곳의 테이블에만 있는 내용을 조인하기 위해서는 외부 조인을 사용해야 함.  

 

외부 조인(Outer join)

두 테이블에 모두 데이터가 존재해야만 결과를 추출해낼 수 있는 내부 조인(inner join)과는 달리, 여러 테이블 중에서 한 곳의 테이블에만 있는 데이터라도 결과를 추출해낼 수 있음.

 

• 외부 조인(outer join)의 형식
  - LEFT OUTER JOIN: LEFT 테이블 목록은 모두 출력되어야 함.
  - RIGHT OUTER JOIN: RIGHT 테이블 목록은 모두 출력되어야 함.
  - FULL OUTER JOIN: 양쪽 테이블 목록 모두 출력되어야 함.

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

 

 

▼ 구매 기록이 없는 회원의 정보를 포함한, 전체 회원의 구매 기록을 추출하기 위한 코드 예시

- 만약 구매 기록이 없는 회원이라면, 구매한 상품의 이름이나 개수는 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
   ORDER BY M.mem_id;

- SELECT M.mem_id, M.mem_name, B.prod_name, M.addr  FROM member M ← member 테이블에 접근하여 회원의 아이디, 이름, 구매한 제품명, 주소에 대한 정보를 추출함.
- LEFT OUTER JOIN buy B  ON M.mem_id = B.mem_id ← 왼쪽에 있는 회원 테이블인 member 테이블을 기준으로, buy 테이블이 외부 조인(결합)됨. 이 때, memeber 테이블의 회원 아이디와 buy 테이블의 회원 아이디가 동일한 경우를 기준으로 결합 및 추출함.
 cf. 이 때, Left (왼쪽)에 해당하는 member 테이블 내용은 모두 출력되어야 한다는 의미를 가지고 있음을 참고해야 함. 

- ORDER BY M.mem_id; ← member 테이블의 회원 아이디를 기준으로 정렬됨. (영어는 ABC... 알파벳 순으로 정렬)

 

▼ 구매 기록이 없는 회원의 목록을 추출하기 위한 코드 예시

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 B.prod_name IS NULL -- 상품명이 NULL인 데이터 조회
   ORDER BY M.mem_id;

 

상호 조인(Cross join)

• 정의
  - 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능을 말함. 
  - 그 결과, 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 개수와 동일함. (= cartesian product; 카디션 곱)
   Ex. member 테이블의 행 개수가 12개이고 buy 테이블의 행 개수가 10개라면, member 테이블과 buy 테이블이 상호 조인한 결과의 행 개수는 총 12 * 10 = 120 개가 됨.
  
  
• 상호 조인(cross join)의 형태

SELECT *
   FROM buy
     CROSS JOIN member ;

- buy 테이블의 전체 열(column)에 접근하여 buy 테이블의 각 행이 member 테이블의 모든 행과 결합하도록 함.

• 상호 조인(cross join)의 특징
  - ON 구문을 사용할 수 없음.
  - 랜덤으로 조인하는 것이기 때문에, 결과의 내용이 의미가 없음.
   Ex. 회원 이름과 아이디를 구분하지 않고 모두 다 조인해버림.
  - 상호 조인의 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때임.  (즉, 대용량의 테스트용 테이블을 만들 때 사용함.)
   cf. 대용량의 테이블을 만들고자 한다면, SELECT의 결과가 테이블로 생성되는 CREATE TABLE ~ SELECT 문을 사용할 수 있음.

 

자체 조인(Self join)

- 자신이 자신과 조인한다는 의미를 가지고 있음. 

- 여러 개의 테이블을 조인하는 것이 아닌, 오직 1개의 테이블만 사용하여 조인함.
- 하나의 테이블에 서로 다른 별칭을 붙여서 조인함.

 

• 자체 조인(self join)의 형식

SELECT <열 목록>
FROM <테이블> 별칭A
    INNER JOIN <테이블> 별칭B
    ON <조인될 조건>
[WHERE 검색 조건]

 

▼ 경리부장의 직속상관의 연락처를 찾기 위한 코드 예시는 다음과 같음.

SELECT A.emp "직원", B.emp "직속상관", B.phone "직속상관연락처"
FROM emp_table A -- emp_table 테이블을 별칭 A로 지정
    INNER JOIN emp_table B -- emp_table 테이블을 별칭 B로 지정
    ON A.manager = B.emp
WHERE A.emp = '경리부장';

- 같은 테이블(table) 내에서 서로 다른 별칭을 정하여 두 가지의 테이블(직원/직속상관)로 구분하여 원하는 정보를 찾아냄.

- 위와 같은 코드에서는 직원들이 저장된 열과 직속상관들이 저장된 열을 각각의 별칭(A, B)으로 지정하여 조인함.