조인(join) [inner join, outer join]

■ 조인(join) 이란?

: 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것이다.

 

 

 

---

■ 내부 조인(inner join)

· 일대다 관계(one to many)

: 기본 키와 외래 키의 관계

  - 회원 테이블의 아이디 (하나의 값만 존재)

    : 기본 키 지정(Primary Key, PK)

  - 구매 테이블의 아이디 (여러개의 값이 존재 가능)

    : 외래 키 지정(Foreign Key, FK)

 

· 내부 조인 형식

☞ inner 생략 가능 (기본값)

select [열목록]
from [첫 번째 테이블명]
   inner join [두 번째 테이블명]
   on [조인될 조건]
where [검색 조건]

 

· 실습 예제

# 구매 테이블에서 GRL 아이디를 가진 사람이 구매한 물건을 발송하기 위해 조인을 통해 이름, 주소, 연락처를 연결

select *
from buy
	inner join member
    on buy.mem_id = member.mem_id
where buy.mem_id = 'GRL';

☞ 동일한 열 이름이 존재할 때는 반드시, 테이블명.열이름 형식표기

 

# 내부 조인 사용 시 테이블 표현

☞ from절과 join문 뒤에 나오는 테이블명 뒤에 별칭을 첨부하여 간결하게 작성하는 것을 적극 권장

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;

 

# 내부 조인 활용

'전체 회원'의 아이디, 이름, 구매한 제품, 주소 출력 결과는 회원 아이디 순으로 정렬

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;

 

 

+ 추가

: 내부 조인의 경우 두 테이블에 데이터(열 목록)가 모두 있는 내용만 조인되는 방식이다.

: 양쪽 테이블 중에서 한곳이라도 내용이 있을때 조인을 하기 위해서는 '외부 조인'을 사용한다.

 

 

 

---

■ 외부 조인(outer join)

: 두 테이블을 조인할 때 필요한 내용이 한쪽 테이블에만 있어도 결과 추출이 가능하다.

 

· 외부 조인 형식

select 열목록

form 첫 번째 테이블 (left 테이블)
   <left | right | full>outer join 두 번째 테이블 (right 테이블)
   on 조인될 조건
(where 검색 조건);

 

☞ left | right outer join : 왼쪽 | 오른쪽 테이블의 내용은 모두 출력되어야 한다.

☞ full outer join: 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것이다.

 

· 실습 예제

# '전체 회원의 구매 기록 출력'

: 구매 기록이 없는 회원의 정보도 같이 출력

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;

☞ left outer join을 줄여서 left join으로 축약 가능

 

# 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;

 

# 회원으로 가입만 하고, 한 번도 구매한 적 없는 목록 추출

select distinct 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
order by M.mem_id;

 

 

 

 

---

■ 기타 조인

· 상호 조인(cross join)

: 한쪽 테이블의 모든 행과 다른쪽 테이블의 모든 행을 조인한다.
: 상호 조인의 결과의 전체 행 개수 -> 두 테이블의 각 행의 개수를 곱한 개수

 

ex. 회원 테이블(member)과 구매 테이블(buy)의 상호 조인

select * from buy
    cross join member;

☞ on 구문을 사용할 수 x

☞ 결과의 내용은 의미가 x -> 랜덤으로 조인하기 때문

☞ 주 용도는 테스트하기 위한 대용량 데이터 생성 시 사용

 

· 자체 조인(self join)
: 자신이 자신과 조인

 

- 자체 조인 형식
select 열목록
from 테이블 별칭
   inner join 테이블 별칭
   on 조인될 조건

(where 검색 조건);

 

ex. 경리 부장의 직속 상관의 연락처를 추출

select A.emp "직원", B.emp "직속 상관", B.phone "직속상관연락처"
from emp_table A
	inner join emp_table B
    on A.manager = B.emp
where A.emp =  '경리부장';