SQL JOIN 알아보기 (inner join / full outer join / cross join / outer join / self join)

* Join

- 두 개 이상의 테이블을 연결해서 데이터를 검색하는 방법

- 우리 테이블에 없는 정보를 상대방 테이블에서 꺼내오는 과정

- 보통 두 개 이상의 행(row)들의 공통된 값의 기본키, 외래키를 사용해서 Join

- 연결고리가 있어야 함(부서번호, 지역번호)

- 기본 키(Primary key) : 테이블에서 중복이 되지 않는 키
- 외래 키(Foreign key) : 다른 테이블에서 PK(기본키), UK(유니크키)인 경우가 많다.

- ansi SQL(표준) 문법과 oracle문법 2가지로 작성할 수 있다.

JOIN 종류	중요도
inner join	☆☆☆☆☆
full outer join	☆
cross join
outer join   - left out join   - right out join	☆☆☆
self join	☆☆☆☆☆

 

-- inner Join

-- ansi SQL (표준) : INNER JOIN ~ ON

SELECT e.employee_id, e.first_name,         -- 기본키의 테이블 alias는 생략 가능(e., d.)
    e.department_id, d.department_id,       -- 양쪽 테이블에 있는 컬럼 alias를 생략하면 오류
    d.department_name
FROM employees e INNER JOIN departments d     -- 테이블의 alias 지정
    on e.department_id = d.department_id;

-- oracle

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id

 

-- cross Join

- 하나씩 다 Join 시킴

-- ansi

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

-- oracle

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d;

 

-- full outer join

-- ansi

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

-- oracle

- 원래는 없지만, 아래처럼 표현 가능 : left outer join + right outer join

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
UNION
SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;

- UNION(집합) 위의 쿼리와 아래의 쿼리를 합쳐라

- null 부분까지 모두 출력

 

-- outer join

-- left

--ansi

SELECT e.employee_id, e.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 바깥에 있는 부분도 출력 (중심 테이블이 왼쪽)

-- oracle

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);     -- 왼쪽이 중심

 

-- left outer join + 차집합

- 먼저 left outer join 한 다음에 null인 경우만 뽑아라

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
    AND e.department_id IS NULL;

 

-- right

--ansi

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e RIGHT OUTER JOIN departments d	-- departments 바깥에 있는 부분도 출력
ON e.department_id = d.department_id;

-- oracle

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;     -- 오른쪽이 중심

 

-- right outer join + 차집합

- 먼저 right outer join 한 다음에 null인 경우만 뽑아라

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
    AND e.department_id IS NULL;

 

-- 완전 차집합

-- ansi

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e FULL OUTER JOIN departments d
    on e.department_id = d.department_id
WHERE e.department_id IS NULL           -- e, d 둘다 null
    OR d.department_id IS NULL;         -- e만 null -> or로 묶어주기

 

-- self join

- 동일한 테이블을 join

SELECT a.employee_id, a.first_name,     -- 같은 테이블이기 때문에 alias 무조건 붙이기
    a.manager_id, b.employee_id,
    b.first_name
FROM employees a, employees b            -- a : 사원 / b : 상사(매니저)        다른 테이블로 생각하기
WHERE a.manager_id = b.employee_id;      -- 사원의 상사 = 상사의 정보  (두가지 다 사원번호임)

-- 조건 추가해보기

SELECT a.employee_id, a.first_name,
    a.manager_id, b.employee_id,
    b.first_name
FROM employees a, employees b
WHERE a.manager_id = b.employee_id
    AND a.employee_id = 168;