240126 DB - JOIN 2, 고급 JOIN 1

DB를 켜고 끄는 것은 터미널에서 해줘야함

터미널에서

lnsrctl start -- -> 리스너켜기
sqlplus /nolog
conn sys/oracle as sysdba
startup

 

조인의 경우는 SQL Developer로 확인하는 것이 편함

※SQL Developer 설정하는 작업([study/DB] - 240110 DB)

 

Join

그림 1.

 

여러 테이블의 컬럼을 함께 출력하는 구문

select 컬럼명1, 컬럼명2, 컬럼명3
from 테이블A join 테이블B
on 테이블A.컬럼명 = 테이블B.컬럼명
[where]
[group by]
[having]
[order by]

 

공통된 데이터의 컬럼을 찾아줘서 on에 작성해줘야한다.

공통된 데이터 컬럼에서 접두어로 정확히 출처 테이블명을 밝혀줘야함

 

ex3) 모호한 컬럼을 명확하게 작성하는 방법

select employee_id, last_name, salary, employees.manger_id,
         employees.department_id, department_name
from employees join departments
on employees.department_id = departments.department_id;

(==)

select employees.employee_id, employees. last_name, employees. salary, employees.manger_id,
         employees.department_id, departments.department_name
from employees join departments
on employees.department_id = departments.department_id;

아래가 속도가 빠름

(==) 테이블 alias, 쿼리 구문안에서 테이블 alias를 사용하면  위보다 짧고 가시성 좋음. 모든 조인 구문은 아래와 같이 사용

select e.employee_id, e.last_name, e.salary, e.manger_id,
         e.department_id, d.department_name
from employees e join departments d
on e.department_id = d.department_id;

 

ex4) employees 테이블과 departments 테이블을 사용해서 부서 정보(department_id, department_name, manager_id[부서장])와 부서의 매니저[부서장] 이름(last_name)을 함께 출력

출력하고자 하는 컬럼이 무엇이냐에 따라서 컬럼이 달라짐

select d.department_id, d.department_name, d.manager_id, e.last_name
from departments d join employees e -- 가독성이 좋도록 테이블 순서를 맞춰주는 것이 좋음
on d.manager_id = e.employee_id;

 

ex5) employees 테이블과 jobs 테이블을 사용해서 직원 정보(employee_id, last_name, salary, job_id)와

담당 업무 내용(job_title)을 함께 출력

select e.employee_id, e.last_name, e.salary, e.job_id, j.job_title
from employees e join jobs j
on e.job_id = j.job_id;

 

ex6) self-join(자체 조인) -> table alias가 필수

무엇을 출력할 것인가?

employees 테이블에서 직원의 정보(employee_id, last_name, salary, job_id, manager_id)와

직원의 매니저 이름(last_name)을 함께 출력

그림 2.

실제로는 테이블이 하나

select e1.employee_id, e1.last_name, e1.salary, e1.job_id, e1.manager_id, e2.last_name
from employees e1 join employees e2
on e1.manager_id = e2.employee_id;

 

※컬럼 alias 적용

select e1.employee_id, e1.last_name emp_name, e1.salary, e1.job_id, e1.manager_id, e2.last_name mgr_name
from employees e1 join employees e2
on e1.manager_id = e2.employee_id;

 

ex7) 3개 테이블 조인 -> 먼저 두 테이블을 조인 시킨뒤 남은 하나의 테이블을 조인 시켜준다.

employees, departments, locations 테이블을 사용해서

직원의 정보(employee_id, last_name, salary)와

직원이 소속된 부서 정보(department_id, department_name)와

부서의 위치 정보(location_id, city, street_address)를 함께 출력

select e.employee_id, e.last_name, e.salary,
           d.department_id, d.department_name,
           l.location_id, l.city, l.street_address
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id

join ....

 

이런식으로 N개 테이블을 조인하는 할 수 있다.

먼저 두 테이블을 조인한 후 join 테이블명 테이블별칭 on, join 테이블명 테이블별칭 on,.....

 

연습문제1

select e.employee_id, e.last_name, e.salary, e.department_id, d.department_name
from employees e join departments d
on e.department_id = d.department_id;

 

연습문제2

select e1.last_name "Employee", e1.employee_id "Emp#",
           e2.last_name "Manager", e1.manager_id "Mgr#"
from employee e1 join employees e2
on e1.manager_id = e2.employee_id;

==

select e1.last_name "Employee", e1.employee_id "Emp#",
           e2.last_name "Manager", e2.manager_id "Mgr#"
from employee e1 join employees e2
on e1.manager_id = e2.employee_id;

---

고급 Join

1. Natural join(자연 조인)

조인을 하고자 하는 두 테이블에서 컬럼명과 데이터타입이 같은 컬럼을지준으로 조인 

select * from 테이블명1 natural join 테이블명2;

 

natural join 사용하는 이유는 on을 사용하는 것보다 훨씬 간결해져 가독성이 높아짐.

 

-- natural join ex1)
select employee_id, first_name, job_id, job_title
from employees natural join jobs;

==

-- ※on절을 이용한 조인으로 나타내기
select e.employee_id, e.first_name, j.job_id, j.job_title
from employees e join jobs j
on e.job_id = j.job_id;

 

natural join이 훨씬 더 짧고 간결하여 가독성이 높음.

natural join은 DBMS에 맡기는 의미. natural join을 사용할 때 컬럼의 출처를 쓰면 오류가 발생함

 

-- natural join ex2)
select department_id, department_name, location_id, city
from departments natural join locations;

==

-- ※on절을 이용한 조인으로 나타내기
select d.department_id, d.department_name, l.location_id, l.city
from departments d join locations l
on d.location_id=l.location_id;

 

두 테이블에서 컬럼명과 데이터타입이 같은 컬럼이 두 개 이상일 경우 두 컬럼의 조합으로 조인함.

원하는 결과가 나오지 않음

 

x테이블의 a 컬럼 숫자 데이터타입, y테이블의 a 컬럼 문자 데이터타입

on절은 가능, 자동형변환이 이루어짐. natural join은 불가능.

 

x테이블의 a 컬럼 숫자 데이터타입 b 컬럼 숫자 데이터 타입,

y테이블의 a 컬럼 문자 데이터타입 b 컬럼 숫자 데이터 타입 일 경우에는 natural join 불가능, using절을 사용하면 됨

---

2. using절을 사용한 join

조인을 하고자 하는 두 테이블에서 컬럼명이 동일하고 데이터 타입이 다른 경우 사용할 수 있는 조인 유형

 

-- using ex1)
select employee_id, last_name, location_id, department_id
from employees join departments
using (department_id);

 

※using 절에 괄호가 없으면 오류가 발생함

※natural join과 마찬가지로 컬럼 alias를 접두어로 붙이면 오류가 남.

 

-- using ex2)
select l.city, d.department_name
from location l join departments d
using (location_id)
where d.location_id = 1400;

-- 오류가 발생

-- 수정
select city, department_name
from locations join departments
using (location_id)
where location_id = 1400;

 

---

3. ON절을 사용한 join(일반 조인)

-- on ex1)
select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
from employees e join departments d
on (e.department_id = d.department_id)
where e.manager_id = 149;

 

---

조인 유형

	natural join	using join	on join
컬럼명	동일	동일	상관없음
데이터 타입	동일	상관없음	상관없음
문법	select department_id, department_name, city from departments natural join location	select department_id, department_name, city from departments join locations using (location_id);	select d.department_id, d.department_name, l.city from departments d join locations l on d.location_id = l.location_id;

 

natural join이 가능하면 using join, on join이 가능

using join이 가능하면 on join이 가능

---

4. self-join

self-join ex)

select worker.last_name emp, manager.last_name mgr
from employees worker join employees manager
on worker.manager_id = manager.employee_id;

 

---

5. non-equi join

equi join	non-equi-join
조긴구문에 동등연산자(=)가 포함된 조인 유형 -natural join -using join -on(=) join	조인구문에 동등연산자가 아닌 그 외 비교 연산자가 포함된 조인 유형 -on(=이 아닌 비교 연산자) join

 

-- non-equi join ex)
select e.last_name, e.salary, j.grade_level
from employees e join jobgrade j
on e.salary between j.lower_sal and j.highest_sal;

 

---

6. outer join

조인 조건이 만족되는 행은 물론 조인 조건이 만족되지 않는 행까지 모두 반환해 주는 조인 유형

inner join	outer join
-내부조인 -조인 조건을 만족하는 행만 반환하는 조인 유형 -natural join, using join, on join	-외부조인 -조인 조건을 만족하는 행과  만족하지 않는 행까지 반환하는 조인 유형 -left outer join, rigth outer join, full outer join

 

-- outer join ex1) left out join
select e.last_name, e.department_id, d.department_name
from employees e left outer join departments d
on e.department_id=d.department_id;

부서가 없는 직원이 포함되어 나옴

 

-- outer join ex2)
select e.last_name, e.department_id, d.department_name
from departments d left outer join employees e
on d.department_id=e.department_id;

빈 부서가 포함되어 나옴

 

-- outer join ex3)
select e.last_name, e.department_id, d.department_name
from employees e full outer join departments d
on e.department_id=d.department_id;

부서가 없는 직원과 빈 부서가 포함되어 나옴