JOIN/EQUI-JOIN) INNER JOIN, OUTER JOIN, NATURAL JOIN(left outer join, right outer join, full outer join, using)

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

JOIN

- 두 개 이상의 테이블 레코드(entity)를 연결해서 가상 테이블 relation을 생성하는 문법

- 기준 컬럼을 가지고 두 테이블의 레코드를 연결

 

relation 생성하기

① join

② set operator

 

송종기 사원의 부서명을 조회

-- 송종기 사원의 부서명을 조회
-- employee.emp_name
-- department.dept_title
select dept_code from employee where emp_name = '송종기';
select dept_title from department where dept_id = 'D9';

 

select
    *
from
    (employee e join department d
        on e.dept_code = d.dept_id)

employee 테이블의 dept_code와 department 테이블의 dept_id가 같은 department 테이블이 join된 것을 알 수 있습니다.

 

-- employee - department가 연결된 relation 생성
select
    e.emp_name,
    d.dept_title
from
    (employee e join department d
        on e.dept_code = d.dept_id)
where
    e.emp_name = '송종기';

join을 이용하여 '송종기'사원의 부서명을 가져왔습니다.

 

---

조인 분류

① EQUI-JOIN

- 조인 기준절(on)에 = 동등 비교를 사용하는 조인

② NON-EQUI-JOIN
- 조인 기준절(on)에 = 동등 비교를 사용하지 않는 조인 (!=, between and, in, is null ..등 연산자 사용)

 

조인 문법 분류

① ansi 표준 문법 : join, on 키워드

② oracle 전용 분법 : , (콤마), where절 이용

---

EQUI-JOIN

 

INNER JOIN

- 내부 조인

- 두 테이블 간의 교집합을 의미

- 각 테이블에서 기준 컬럼이 null인 행, 상대 테이블에서 매칭 되는 행이 없는 행도 제외

- inner 키워드 생략 가능 (inner) join으로 작성

select emp_name, dept_code from employee;

select * from department;

 

select
    e.emp_name,
    d.dept_title
from    
    employee e inner join department d
        on e.dept_code = d.dept_id;

employee 테이블에서 제외된 행 : dept_code가 null인 행

department 테이블에서 제외된 행 : 상대 테이블 employee.dept_code에서 사용되지 않은 D3, D4, D7 3행 제외

 

[오라클 전용문법 버전]

- 테이블 간 순서가 중요하지 않음

select
    e.emp_name,
    d.dept_title
from    
    employee e, department d
where
    e.dept_code = d.dept_id;

 

 

OUTER JOIN

- 외부 조인

- 좌/우측 테이블 기준으로 조인

- 기준이 된 테이블은 누락되는 행이 없음

- outer 키워드 생략 가능 (outer) join / right (outer) join

 

☞ left outer join

select
    e.emp_name,
    d.dept_title
from
    employee e left outer join department d
        on e.dept_code = d.dept_id;

employee 테이블 기준이므로 employee 테이블의 모든 행을 포함하고 있습니다.

24행이 출력된 것을 확인할 수 있습니다.

24행 = 22행 (inner join) + 2행 (인턴/null값)

[오라클 전용문법 버전]

- (+) 기준 테이블의 반대 컬럼에 추가 (from절의 순서는 중요하지 않고, where절의 (+)가 중요)

select
    e.emp_name,
    d.dept_title
from
    employee e, department d
where
    e.dept_code = d.dept_id(+);

 

☞ right outer join

select
    d.dept_title,
    e.emp_name
from
    employee e right outer join department d
        on e.dept_code = d.dept_id;

department 테이블이 기준이므로 department 테이블의 모든 행을 포함하고 있습니다.

25행이 출력된 것을 볼 수 있습니다.

25행 = 22행 (inner join) + 3행 (사원이 없는 부서 D3, D4, D7)

 

[오라클 전용문법 버전]

select
    d.dept_title,
    e.emp_name
from
    employee e, department d
where
    e.dept_code(+) = d.dept_id;

 

☞ full outer join

- 좌/우측 테이블이 모두 기준

- 좌/우측 테이블에서 제외되는 행이 없음

select
    e.emp_name,
    d.dept_title
from
    employee e full outer join department d
        on e.dept_code = d.dept_id;

employee 테이블과 department 테이블이 모두 기준이 되어 두 테이블의 모든 행을 포함하고 있습니다.

27행이 출력된 것을 볼 수 있습니다.

27행 = 22행(inner join) + 2행(인턴/null값) + 3행(사원이 없는 부서 D3, D4, D7)

 

[오라클 전용문법]

- full outer join 문법 없음

 

 

※ 유의 사항

기준 컬럼명이 다른 경우 : 별칭을 사용하지 않아도 정상처리 (하지만 사용 권장)

employee e full outer join department d 
	on e.dept_code = d.dept_id;

 

기준 컬럼명이 같은 경우 : 별칭을 생략할 수 없음

employee e join job j
	on job_code = job_code; --ORA-00918: column ambiguously defined (별칭 생략 시 오류 발생)

이처럼 동일한 기준 컬럼명을 가진 테이블을 join할 경우,

테이블 별칭을 생략하면 "ORA-00918: column ambiguously defined" 오류가 발생됩니다.

 

---

NATURAL JOIN

- 컬럼명이 동일한 경우 모두 기준 컬럼을 사용

 

☞using(컬럼명)

- 지정한 컬럼만 기준 컬럼으로 사용

select
    *
from
    employee e join job j
        using(job_code);

지정한 컬럼 'job_code'를 기준 컬럼으로 사용하여 employee 테이블의 job_code와 job 테이블의 job_code 컬럼이 맨 앞에 한 번 출력된 것을 확인할 수 있습니다.