오라클 SQL JOIN문 정리

 

 

JOIN

• select의 기능 세가지 중 하나 (열단위추출, 행단위추출, join)
• 두개 이상의 테이블에서 데이터를 가져오는 방법
• join을 하면 결과 맞는지 꼭 확인하자 / 카티시안(cartesian)곱 발생

 

1. cartesian product

  - 조인 조건이 생략된 경우

  - 조인 조건이 잘못만든 경우

  - 첫번째 테이블 모든 행이 두번째 테이블 모든 행에 조인된다.(첫번째 X 두번째 => 카티시안곱)

select employee_id, department_name

from employees, departments;

위를 실행하면 데이터가 계속 나옴(m쪽집합의 개수만큼만 나오면 됨)

 

 

2. equi join

      (= 등가조인, inner join, simple join)

 - 키값이 일치가 되는지 확인하고 조인함

 - 동일한 컬럼을 찾아서 조인하자!

 - 동일한 이름의 컬럼이더라도 쓰임이 다르다.

         - 부서테이블의 employee_id(매니저아이디는 부서장임)

           사원테이블의 employee_id(매니저아이디는 직속상관임)

- 키값이 일치하는 데이터만 연결한다.

- where절에 조인조건 술어를 표현

 

이렇게 하면 오류남(시맨틱오류/의미분석오류/컬럼의 정의가 모호함)

department_id 가 employees에도 있고 departments에도 있음

select employee_id, department_name

from employees, departments

where department_id = department_id;

 

모호성을 제거하려면 정확한테이블명과 컬럼을 같이 명시해준다. (테이블명을 컬럼앞에쓰기)

시맨틱체크 속도가 빨라짐

단, 쿼리문이 길어져서 메모리 사용량이 늘어남

SELECT employees.employee_id, departments.department_name

FROM employees, departments

WHERE employees.department_id = departments.department_id;

 

쿼리문을 줄이고 메모리 사용량을 늘리기 위해 테이블 별칭 사용한다.

SELECT e.employee_id, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id;

 

 

3. outer join

  - (+)로 표현 (조인조건술어에다가)

  - 조인 키값이 없는 데이터를 출력하고자 할때 사용 (equi join의 제약을 보완)

  - 키값이 일치되지 데이터 등등

  - (+)가 없는 테이블은 다 뿌림(양쪽에다가 못씀 지금은)

SELECT e.employee_id, e.last_name, d.department_name

FROM employees e, departments d

WHERE e.employee_id = d.manager_id(+);

 

left outer join : 왼쪽에 있는걸 뿌려주세요(MSSQL에서)

select e.employee_id, d.department_id, d.department_name

from employees e left outer join departments d

on e.department_id = d.department_id;

=

오라클 전용(ANSI표준)

select e.employee_id, d.department_id, d.department_name

from employees e, departments d

where e.department_id = d.department_id(+);

 

right outer join : 오른쪽에 있는걸 뿌려주세요(MSSQL에서)

select e.employee_id, d.department_id, d.department_name

from employees e right outer join departments d

on e.department_id = d.department_id;

=

오라클 전용(ANSI표준)

select e.employee_id, d.department_id, d.department_name

from employees e, departments d

on e.department_id(+) = d.department_id;

 

 

 

4. self join

  - 다른테이블이 아닌 자신의 테이블에서 연결

  - 동일한 테이블을 2개만들기

  - 테이블 이름이 같으면 모호성이 발생하기 때문에 별칭으로 분류

  - '나의 정보', '나의 관리자 정보'도 파악해보자

SELECT w.employee_id, w.last_name, m.employee_id, m.last_name

FROM employees w, employees m

WHERE w.manager_id = m.employee_id(+);

 

 

5. non equi join 비등가조인

  - job_grades테이블은 급여로 보는 등급임

  - 범위를 기준으로 조인하는 방법

  - 내급여가 어느 범주에 속하는지 찾을 때 non equi join사용

select e.employee_id, e.salary, j.grade_level

from employees e, job_grades j

where e.salary between j.lowest_sal and j.highest_sal;

=

(ANSI표준으로바꾸기)

select e.employee_id, e.salary, j.grade_level

from employees e

join job_grades j

on e.salary between j.lowest_sal and j.highest_sal;

 

 

6. natural join

  - 동일한 이름의 모든 컬럼을 기반으로 equi join 한다./

  - 단 동일한 이름이지만 데이터 타입이 틀리면 오류난다.

1) ANSI 표준

select department_name, city

from departments natural join locations;

 

2) 오라클전용

select d.department_name, l.city

from departments d, locations l

where d.location_id = l.location_id;

 

 

7. join using

: using(기준컬럼)

 - natural join의 단점을 보완

select employee_id, department_name

from employees join departments

using(department_id);  --> 조인 기준 컬럼을 지정

 

 - using절에 사용한 기준컬럼은 어느테이블이다(테이블별칭을 접두어로 사용하는거 e.employee_id) 라고 지정하면 안됨!!!!

select e.employee_id, department_id, d.department_name

from employees e join departments d

using(department_id);

 

 

8. join on

on절이 조인 조건 술어를 직접 만드는 거임

select e.employee_id, d.department_id, d.department_name

from employees e join departments d

on e.department_id = d.department_id;

 

3개 연결할때

select e.employee_id, d.department_id, d.department_name, l.city

from employees e join departments d

on e.department_id = d.department_id

join locations l

on d.location_id = l.location_id;

 

=

 

select e.employee_id, d.department_id, d.department_name, l.city

from employees e

join departments d

on e.department_id = d.department_id

join locations l

on d.location_id = l.location_id;

 

---

보너스!!!union 집합연산자

  - 각각 해당되지않는 컬럼만 뽑기 : 오라클 전용(ANSI표준)

  - 합하되 중복은 제거됨 / 정렬작업이 실행됨 /

     컬럼의 개수, 타입이 일치되어야함 (근데 성능이 떨어짐)

select e.employee_id, d.department_id, d.department_name

from employees e, departments d

on e.department_id(+) = d.department_id

union

select e.employee_id, d.department_id, d.department_name

from employees e, departments d

on e.department_id = d.department_id(+);