JOIN

조인은 한 테이블의 행을 다른 테이블의 행에 연결하여 두 개 이상의 테이블을 결합하는 연산이다.

 

SELECT employee_id, first_name, department_id
FROM employees; --107명

SELECT department_id, department_name
FROM departments; --27건

--카티션곱

SELECT *
FROM Customer, Orders;

오라클 전용 조인, 우리는 ansi 표준 조인법 쓰는걸 권장

두 테이블을 아무런 조건을 주지 않고 SELECT 시키면 카티션 프로덕트 연산이 나옴.

사번 이름 부서번호 : employees
부서번호, 부서명 : departments
4개의 컬럼이 한 테이블에 존재하는지 다른 테이블에 존재하는지,
다른 테이블에 존재하면 join을 사용한다

--사원의 사번, 이름, 부서번호, 부서명을 출력하시오(107명*27건)
SELECT employee_id, first_name, e.department_id
	  ,d.department_id, department_name
FROM employees e, departments d;

사원이 속한 부서의 부서이름만 찾기

중복되지 않는 컬럼은 정확히 어느 테이블 컬럼인지 지칭안해도 되지만 department_id 컬럼값은 동일하다.
중복된 컬럼은 어느 테이블 컬럼인지 오라클에게 알려주는걸 권장한다.
테이블 명에 대한 별칭 부여가능하다. FROM절에서 선언한 별칭 부여하고 나면 별칭만 사용해야함.
원래 테이블 이름 사용 불가 departments.department_id 불가
아래 사진 헤딩에는 e. d. 빼고 컬럼명만 나온다
자동으로 department_id, department_id_1 번호 부여됨

헤딩에는 e. d. 빼고 컬럼명만 나온다

FROM 절에서 모두 찾았더니 사원 하나당 부서 27개 모두 매칭해버림 이걸 카티션곱이라고 함.

---

--사원의 사번, 이름, 부서번호, 부서명을 출력하시오(오라클 전용 조인법)
--(106명, 178번 사원 1명이 부서번호 null값이고 null값을 department_id 없어서, 이것도 조인 쓴것-
--이건 오라클에서만 쓰는 조인방식, 더 늘어나면 FROM절과 WHERE절이 지저분해져서)
SELECT employee_id, first_name, e.department_id
	  ,d.department_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

---

--표준화된 ANSI JOIN법으로 출력하기
--1) JOIN ON

SELECT employee_id, first_name, e.department_id
	  ,d.department_id, department_name
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
--조인에 대한 조건은 ON절에, 일반 조건은 WHERE에

DECODE는 오라클 전용 함수, case절로 대체해서 쓰는걸 더 권장함.

--사원의 사번, 이름, 부서번호, 부서명, 부서가 속한 도시명을 출력하시오
--스키마 보고 확인
SELECT department_id FROM employees WHERE employee_id=100; --90
SELECT department_name, location_id FROM departments WHERE department_id; -- Executive, 1700
SELECT city FROM locations WHERE location_id=1700; --Seattle

SELECT employee_id, first_name, e.department_id
	  ,d.department_id, department_name
	  ,city
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
				 JOIN locations l ON (d.location_id = l.location_id);

---

--2) NATURAL JOIN

SELECT employee_id, first_name, j.job_id, job_title
FROM employees e JOIN jobs j ON (e.job_id = j.job_id);

↓ 이걸 NATURAL JOIN으로 바꾸기
별칭 부여하지 않는다. ON 사용안한다.

SELECT employee_id, first_name, job_id, job_title
FROM employees NATURAL JOIN jobs;

employees 테이블의 컬럼 이름과 jobs 테이블의 컬럼 이름에서
같은 이름의 컬럼있으면 자동으로 = 조건 처리
유지보수하기에 좋지 않다. NATURAL JOIN 사용하면 위험함.

---

--3)JOIN USING

--사원의 사번, 이름, 직무번호, 직무명을 출력하시오
SELECT employee_id, first_name
	  ,department_id, department_name
FROM employees NATURAL JOIN departments; --(X)

: 32명만 나옴, 나의 관리자가 부서장인 사람들만 추출된 것
스키마를 보면 두 테이블에 공통으로 쓰이는 컬럼이 department_id, manager_id 이름 같은 컬럼이 2개
둘 다 ON절에 표기된 것과 같은 효과
manager_id : 부서장(사원을 관리하는 관리자번호)

SELECT employee_id, first_name, department_id, department_name
FROM employees JOIN departments USING(department_id);

ON절의 축약이면서 NATURAL JOIN의 문제를 해결한 방법
employees 테이블의 department_id와 departments 테이블의 department_id가
같은 행을 찾아서 출력해라. 권장은 ON절 쓰는걸 권장

이때까지 쓴 JOIN은 (INNER) JOIN, INNER가 생략되었을 뿐. 두 테이블을 모두 만족해야 출력

---

이때까지 쓴 JOIN은 (INNER) JOIN, INNER가 생략되었을 뿐. 두 테이블을 모두 만족해야 출력

---

부서가 없어도 모든 사원을 다 출력, 사원을 다 출력하니 기준은 사원

--4) OUTER JOIN  기준되는 테이블은 무조건 다 출력됨.

--사원의 사번, 이름, 부서번호, 부서명을 출력하시오.
--단, 부서없는 사원도 출력하시오 = 사원이 기준이구나 (107명)
SELECT employee_id, first_name, d.department_id, department_name
FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

OUTER는 생략 가능. 왼쪽 테이블 기준.
기준되는 테이블이 오른쪽에 있어도 상관없다. 대신 RIGHT OUTER JOIN.
기준되는 테이블 왼쪽에 주는걸 좀 더 권장.
ON절을 만족하지 않아도 기준 테이블 무조건 다 출력

---

--3) ~5)는  ANSI JOIN

--오라클전용outer join
--기준이 되지 않는 쪽에 (+);

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

--사원의 사번, 이름, 부서번호, 부서명을 출력하시오.
--단, 사원없는 부서도 출력하시오(122건)
SELECT employee_id, first_name, d.department_id, department_name
FROM employees e RIGHT JOIN departments d ON (e.department_id = d.department_id);

---

--FULL JOIN

양쪽 테이블 자료 모두 출력

--사원의 사번, 이름, 부서번호, 부서명을 출력하시오.
--단, 부서없는 사원도, 사원없는 부서도 출력하시오(123건) 부서없는 사원 한건 추가
SELECT employee_id, first_name
	  ,d.department_id, department_name
FROM employees e FULL (OUTER) JOIN departments d ON (e.department_id = d.department_id);

---

자기참조관계

--5) SELF JOIN

한 테이블을 서로 다른 테이블인것처럼, 반드시 별칭부여해야함

--사원의 사번, 이름, 관리자번호, 관리자이름을 출력하시오
SELECT e.employee_id "사번", e.first_name "이름"
	  --,m.employee_id "관리자번호"
	   ,e.manager_id  "관리자번호"
	   ,m.first_name  "관리자이름"
FROM employees e JOIN employees m ON (e.manager_id = m.employee_id);

--사원의 사번, 이름, 관리자번호, 관리자이름을 출력하시오.
--관리자가 없는 사원도 출력하시오(107명)
--사원 테이블이 기준
SELECT e.employee_id "사번", e.first_name "이름"
	   ,e.manager_id  "관리자번호"
	   ,m.first_name  "관리자이름"
FROM employees e LEFT JOIN employees m ON (e.manager_id = m.employee_id);

 

사원 입장에서 바라보는 employees, 관리자입장에서 바라보는 employees

사원입장에서의 manager_id = 관리자 입장에서의 employee_id

self join 사람 파랑이나 검정처럼 찾아야함