MySQL) JOIN 정리 : inner, left, right, full, cross, self

JOIN 종류	설명
INNER JOIN	공통된 값이 있는 행만 반환 (교집합)
LEFT OUTER JOIN	왼쪽 테이블은 유지, 오른쪽이 없으면 NULL
RIGHT OUTER JOIN	오른쪽 테이블은 유지, 왼쪽이 없으면 NULL
FULL OUTER JOIN	LEFT + RIGHT 합집합 (NULL 포함, MySQL은 UNION 사용)
CROSS JOIN	모든 행을 조합 (곱집합, Cartesian Product)
SELF JOIN	같은 테이블을 자기 자신과 조인

 

기본키(Primary Key)

: 중복을 허용하지 않고 고유한 값을 가지는 컬럼

: null 값을 허용하지 않음(ex 주민번호 등)

외래키(Forign Key)

: 다른 테이블의 기본키(Primary Key)를 참조하는 컬럼

: 테이블 간 **연결(관계, 관계형 데이터베이스의 핵심 개념)**을 담당

: 무결성 제약(참조 무결성)을 가짐 → 외래키에 입력되는 값은 반드시 참조하는 테이블의 기본키에 존재해야 함

1. INNER JOIN (교집합)

: 두 테이블에서 공통된 값이 있는 데이터만 가져옴

: ON 조건을 만족하는 행만 반환

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e INNER JOIN departments d 
    ON e.department_id = d.department_id;

: 공통된 department_id가 있는 데이터만 출력

2. LEFT OUTER JOIN (왼쪽 기준, 오른쪽은 NULL)

: 왼쪽 테이블은 전부 유지하고, 오른쪽 테이블에 없는 데이터는 NULL

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e  LEFT OUTER JOIN departments d 
    ON e.department_id = d.department_id;

: 직원이 속한 부서가 없으면 NULL로 표시됨

3. RIGHT OUTER JOIN (오른쪽 기준, 왼쪽은 NULL)

: 오른쪽 테이블은 전부 유지하고, 왼쪽 테이블에 없는 데이터는 NULL

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e RIGHT OUTER JOIN departments d 
    ON e.department_id = d.department_id;

: 부서는 유지되지만, 부서에 속한 직원이 없으면 NULL로 표시됨

4. FULL OUTER JOIN (전체 합집합, NULL 포함)

: LEFT + RIGHT JOIN을 합친 것

: 일치하는 값이 없는 경우 NULL 포함

: MySQL에서는 FULL JOIN이 없어서 UNION으로 구현 가능

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e LEFT OUTER JOIN departments d 
    ON e.department_id = d.department_id

UNION

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e RIGHT OUTER JOIN departments d 
    ON e.department_id = d.department_id;

: 모든 직원과 모든 부서 정보가 표시됨 (일치하지 않는 데이터는 NULL)

5. CROSS JOIN (모든 조합)

: 두 테이블의 모든 행을 조합

: JOIN 조건 없이 조인하면 자동으로 CROSS JOIN 수행

SELECT e.first_name, d.department_name
FROM employees e CROSS JOIN departments d;

: 직원 수 × 부서 수 만큼의 행이 생성됨 (모든 조합)

6. SELF JOIN (자기 자신과 JOIN)

: 같은 테이블을 두 번 사용하여 조인

: 예: 사원과 그 사원의 관리자 정보를 가져오기

SELECT e1.first_name AS '사원', e2.first_name AS '관리자'
FROM employees e1 JOIN employees e2 
    ON e1.manager_id = e2.employee_id;

: 사원과 그 사원의 관리자 정보를 한 테이블에서 조회

---

JOIN 사용 예제 : inner, left, right, full, cross, self

INNER JOIN ( ansi 문법 )

select e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name, d.location_id
from employees e inner join departments d
	on e.department_id = d.department_id;

INNER JOIN ( ansi 문법이 아닐 때 )

select e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name, d.location_id
from employees e, departments d
where e.department_id = d.department_id;

INNER JOIN ( 3개의 테이블 조인 )

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id, d.department_name,
    e.job_id, j.job_id, j.job_title
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
  AND e.job_id = j.job_id;

LEFT OUTER JOIN

select employee_id, first_name,
    e.department_id, d.department_id,
    d.department_name
from employees e left outer join departments d
	on e.department_id = d.department_id;

: employees 테이블을 기준으로 departments 테이블과 LEFT OUTER JOIN 수행

employees 테이블에서 department_id가 NULL인 행을 제외하고 조회

select employee_id, first_name,
	e.department_id, d.department_id,
    d.department_name
from employees e left outer join departments d
	on e.department_id = d.department_id
where e.department_id is not null;

RIGHT OUTER JOIN

select employee_id, first_name,
    e.department_id, d.department_id,
    d.department_name
from employees e right outer join departments d
	on e.department_id = d.department_id;

: employees 테이블을 기준으로 departments 테이블과 RIGHT OUTER JOIN 수행

employees 테이블에서 department_id가 NULL인 행을 제외하고 조회

select employee_id, first_name,
    e.department_id, d.department_id,
    d.department_name
from employees e right outer join departments d
	on e.department_id = d.department_id
where e.department_id is not null;

FULL OUTER JOIN ( MySQL에서는 full join을 지원 안함 )

select employee_id, first_name,
    e.department_id, d.department_id,
    d.department_name
from employees e left outer join departments d
	on e.department_id = d.department_id

union

select employee_id, first_name,
    e.department_id, d.department_id,
    d.department_name
from employees e right outer join departments d
	on e.department_id = d.department_id;

CROSS JOIN

select employee_id, first_name,
    e.department_id, d.department_id,
    d.department_name
from employees e cross join departments d;

SELF JOIN : 같은 테이블을 join ( ansi 문법 )

select emp.employee_id as '사원번호', emp.first_name as '사원이름',
    emp.manager_id, mgr.employee_id,
    mgr.first_name as '상사의 이름'
from employees emp join employees mgr
	on emp.manager_id = mgr.employee_id;

SELF JOIN ( ansi 문법 아닐 때 )

select emp.first_name as '사원명',
    emp.manager_id, mgr.employee_id,
    mgr.first_name as '상사명'
from employees emp, employees mgr
where emp.manager_id = mgr.employee_id
	and emp.first_name = 'David';