조인 Join

join

📍 조인(JOIN) 이란

: 2개 이상의 테이블에 있는 컬럼들을 합쳐서 가상의 테이블을 만들어 조회하는 방식

• 소스 테이블 : 내가 먼저 읽어야 한다고 생각하는 테이블. 조회 해야 하는 주 정보(main information) 테이블
• 타겟 테이블 : 소스를 읽은 후 소스에 조인할 대상이 되는 테이블. 조회하는 주 정보의 부가 정보(sub information) 제공 테이블

조인 연산 : 각 테이블을 어떻게 합칠 지를 표현하는 것

• 조인 연산에 따른 조인종류
  → Equi join , non-equi join
• 조인 종류
  Inner Join
  → 양쪽 테이블에서 조인 조건을 만족하는 행들만 합친다.
  Outer Join
  → 한쪽 테이블의 행들을 모두 사용하고 다른 쪽 테이블은 조인 조건을 만족하는 행만 합친다. 조인조건을 만족하는 행이 없는 경우 NULL을 합친다.
  → 종류 : Left Outer Join, Right Outer Join, Full Outer Join
  Cross Join
  → 두 테이블의 곱집합을 반환한다.

<데이터 간의 다중성>

• 그래프 간의 연결 표시

A: 자식 table/ B: 부모 table

→ 이 방향일 때

A—-0|— B : A 의 것이 B 의 한 행 과만 관계가 있거나 아무것과도 관계가 없다

A———0|<-B : 0 또는 1 또는 n 개와의 관계를 가질 수 있다.

A——-|<- B : A에서의 데이터 한개가B의 데이터1개 또는 n개과 관계를 가질 수 있음

A——0— B : A 의 데이터 한 개와B 의 데이터 아무것도와 관계맺지 않음

← 이 방향일 때

A ->——-B : 는 불가 ( ->— 이 모양은 어떤 것이 같이 오든 불가하다 - 부모 table이 자식table을 참조 할 수는 없기 때문에)

A —|——-B : fk가 not null

A —|——-B : nullable column → null을 허용하는 column

→ 선은 맨 처음 설계할 때 설정 하는 것으로 물리적으로는 막을 수 없다.

→ - - - -는 fk 가 pk에 포함되지 않는 경우

→ ——-는 fk가 pk에 포함되는 경우

부모table(PK) 과 자식 table(FK)은 관계가 0(자식 table 의 FK 가 null을 허용) 아니면 1(자식 table의 FK가 not null)이다.

• | : 관계성 1
• 0| : 관계성 0 또는 관계성 1

  {도착하는 지점에다가 다중성 표시}

---

Foreign key 제약 조건

Foreign key (외래키)
: 한 테이블이 다른 테이블의 column 값을 참조하는 것
▪ 부모 table - 참조 되는 table (PK)
▪ 자식 table - 참조 하는 table (FK column )

화살표 출발 지점이 자식 table 받는 애가 부모 table
=> (위와 같은 경우에는 직원 table이 자식 table 이고 부서 table이 부모table 이라고 할 수 있음)

→ 직원 테이블의 데이터(row) 는 부서 테이블 의 데이터를 참조
→ 부서 테이블의 row 는 직원 테이블 의 데이터 0 개 ~N 개가 참조 할 수 있음
→ 직원 테이블의 데이터 하나는 직원 테이블 의 데이터 하나를 상사_ID 로 참조

primary key : 각 행을 구분 할 수 있는 고유한 key

• 기본 구문
  : CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼) REFERECES부모테이블(PK 컬럼) [ON 설정]

  → 참조컬럼은 PK 거나 Index 설정된 컬럼이어야 한다

자식 테이블로부터 참조 당하는 부모 테이블의 row 는 삭제 할 수 없음

CASCADE DELETE

• FOREIGN KEY 설정 시 ON DELETE CASCADE 설정
  → 부모 테이블의 참조 ROW 삭제 시 자식 테이블의 참조 ROW 삭제
  CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼) REFERECES 부모테이블(PK 컬럼) ON DELETE CASCADE

• FOREIGN KEY 설정 시 ON DELETE SET NULL 설정
  → 부모 테이블의 참조 ROW 삭제 시 참조 하는 자식 테이블의 컬럼 값을 NULL 로 설정
  CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼) REFERECES 부모테이블(PK 컬럼) ON DELETE SET NULL

ON 설정

기본적으로 자식 테이블에서 참조하고 있으면 부모 테이블의 데이터를 삭제 수정하지 못함

→ Foreign key 컬럼 설정할 때 이것을 변경할 수 있는데 이때 on 설정을 사용

ON DELETE|UPDATE 처리방식

ON DELETE:
참조하는 부모테이블의 행이 삭제 되었을 때 어떻게 처리할 것인지 설정
ON UPDATE:
참조하는 부모테이블의 참조 컬럼값이 변경되면 어떻게 처리할 것인지 설정

처리방식

• CASCADE
  : 부모 테이블에서 데이터를 삭제 수정하면 , 그 행을 참조하는 자식테이블의 행들도 같이 삭제 수정됨

• SET NULL
  : 부모 테이블에서 데이터를 삭제 수정하면 , 그 행을 참조하는 자식테이블의 foreign key 컬럼의 값을 NULL 로 변경

Table drop시 foreign key 제약조건 제거

• 부모 테이블 DROP 전에 MySQL 의 foreign key 적용이 안되도록 설정해야 한다

  set foreign_key_ckecks = 0; -- foreign key 해제 설정
  DROP table 삭제할테이블
  set foreign_key_ckecks = 1; -- foreign key 적용 설정

  → 나중에 다시 1로 바꿔도 없어졌던 foreign key 설정이 다시 생기지는 않음

---

JOIN 개념

JOIN: 관계있는 두 개의 테이블을 합쳐서 (JOIN) 조회 (SELECT) 하는 것
▪조인할 두 테이블의 특정 column들의 값들이 같은 행끼리 합침

JOIN연산

: 어떤 행들의 값이 같은 것끼리 합칠 것인지 설정하는 구문

→ 일반적으로 부모 table과 자식 table을 연결해서 조회하는 경우가 많으며 이 경우 부모 tabled 의 PK(primary key) 와 자식 table의 FK(foreign key) column 의 값이 같은 행들을 JOIN 한다

ex)

카티션 곱 (Cartesian Product)

: 두 테이블의 전체 행들을 합칠 수 있는 모든 경우의 조합이 만들어지는 경우
▪ N행 , M 행 의 두 테이블 조인시 N x M 개의 행이 생성됨

• 이유
  → 조인 연산을 생략한 경우 (CROSS JOIN)
  → 조인 연산이 잘못된 경우

조인 종류

INNER JOIN
▪ 조인 연산 조건을 만족하는 행들을 합친다

OUTER JOIN
▪ 조인 연산 조건을 만족하지 않는 행들도 포함해서 합친다
→ LEFT OUTER JOIN
→ RIGHT OUTER JOIN
→ FULL OUTER JOIN

INNER JOIN

inner join이란...

: 조인 연산 조건을 만족하는 행끼리 만 합치는 JOIN

inner는 생략 가능

→ 부서_ID 가 300 인 부서는 직원 테이블의 어떤 행 과도 관계가 없으므로 조인 대상 포함되지 않음

• 구문

SELECT 컬럼선택
FROM 테이블 _1 INNER JOIN 테이블 _2 ON 조인연산   -- INNER 생략가능
     		 [INNER JOIN 테이블 _3 ON 조인연산

→위의 경우에는

SELECT b.부서 _ID, 부서이름, e.직원이름
FROM 부서 b INNER JOIN 직원 e ON b. 소속부서 e. 부서 _ID

ex)

-- 직원의 ID(emp.emp_id)가 100인 직원의 직원_ID(emp.emp_id), 이름(emp.emp_name), 입사년도(emp.hire_date), 소속부서이름(dept.dept_name)을 조회.

select e.emp_name as "직원이름", 
		e.hire_date as "입사일", 
        d.dept_name as "부서명"
from emp e join dept d on e.dept_id = d.dept_id
where e.emp_id = 100;

ex)

-- 부서_ID(dept.dept_id)가 30인 부서의 이름(dept.dept_name), 위치(dept.loc), 그 부서에 소속된 직원의 이름(emp.emp_name)을 조회.
select d.dept_id, 
	   d.dept_name, 
       d.loc, 
       e.emp_name
from dept d join emp e on d.dept_id = e.dept_id
where d.dept_id = 30;

Self 조인

: 물리적으로 하나의 테이블을 두개의 테이블처럼 조인하는 것.

ex)

-- 직원 ID가 101인 직원의 직원의 ID(emp.emp_id), 이름(emp.emp_name), 상사이름(emp.emp_name)을 조회

select e.emp_id,
	   e.emp_name as "직원 이름",
       m.emp_name as "상사 이름"
from emp e join emp m on e.mgr_id = m.mgr_id
where e.emp_id = 101;

OUTER JOIN

outer join이란...

= 불충분 조인

OUTER 는 생략 가능

• 조인연산 조건을 만족하지 않는 행도 포함해서 합침

Source Table

– 조인시 조회 기준이 되는 테이블로 조회하려는 주 (main) 정보를 가지고 있는 테이블

Target Table

– 조인시 Source table 데이터의 추가 정보를 가지고 있는 테이블로 보조 (sub) 정보를 가지고 있는 테이블

▪ 조인 시 Source 테이블의 데이터 행은 모두 사용하고 Target table 의 데이터 행은 조인 조건을 만족하는 행만 나오도록 함

< Outer Join 종류 >

• LEFT OUTER JOIN
  : Source 테이블이 왼쪽에 선언한 테이블인 경우의 outer join

  구문상 소스 테이블이 왼쪽

→ 구문:

SELECT 컬럼선택
FROM 테이블 _1 LEFT [OUTER] JOIN 테이블 _2 ON 조인연산 -- OUTER 생략가능
-> Source table: 테이블 _1, Target table: 테이블 _2

따라서 위 그림의 경우

SELECT b. 부서 _ID, 부서이름, 
		e.직원이름
FROM 부서 b LEFT OUTER JOIN 직원 e ON b. 소속부서 e. 부서 _ID

• RIGHT OUTER JOIN
  : Source 테이블이 오른쪽에 선언한 테이블인 경우의 outer join

  구문상 소스 테이블이 오른쪽

→구문:
SELECT 컬럼선택
FROM 테이블 _1 RIGHT OUTER JOIN 테이블 _2 ON 조인연산 -- OUTER 생략가능
-> Source table: 테이블 _2, Target table: 테이블 _1

따라서 위 그림의 경우

SELECT b. 부서 _ID, 부서이름,
		e.직원이름
FROM 부서 b RIGHT OUTER JOIN 직원 e ON b. 소속부서 e. 부서 _ID

• FULL OUTER JOIN
  : 양쪽에 선언한 테이블이 모두 Source table 인 경우의 outer join

  > left join, right join select 문을 union 하여 구현
  >  MySQL 은 지원 안함

  둘다 소스 테이블

이 그림의 경우

SELECT b. 부서 _ID, 부서이름 , 직원이름
FROM 부서 b LEFT OUTER JOIN 직원 e ON b. 소속부서 e. 부서 _ID
UNION
SELECT b. 부서 _ID, 부서이름 , 직원이름
FROM 부서 b RIGHT OUTER JOIN 직원 e ON b. 소속부서 e. 부서 _

📍 명심

inner join, outer join중 무었을 사용해야 할 지 모를 경우 outer join사용
→ inner 는 안 나올 경우도 있기 때문에 차라리 많이 보는것이 났다