【JOIN】ANSI JOIN (CROSS/INNER/OUTER/SELF JOIN)

ANSI ; American National Standard Institute 

여러가지 표준을 재정하는 단체

 

ANSI에서 지정해준 JOIN표준 문법이 있다.

다른 DB에서도 사용할 수 있다는 장점이 있다.

조인조건에 사용되는 두 컬럼의 이름이 같다면 USING을 이용해 쿼리문을 간소화할 수 있다.

조인조건을 ON절에 적고 쿼리문의 조건을 WHERE절에 적기 때문에 보기에도 좋다. 

 

 

 

 

ANSI CROSS JOIN

SELECT * FROM employees CROSS JOIN departments;

원래는 SELECT * FROM employees, departments;

 

ANSI INNER JOIN : 조건을 충족하는 행만 등장하는 조인 (EQUI JOIN)

SELECT * FROM employees e INNER JOIN departments d ON e.departments_id = d.departments_id;

SELECT * FROM employees e INNER JOIN departments d USING (department_id);

 

ANSI SELF JOIN

SELECT * FROM employees emp INNER JOIN employees mgr ON  emp.manager_id = mgr.employee_id;

 

 

ANSI OUTER JOIN 

조건을 충족하지 못한 행도 등장하는 조인

일단 보류 (다음 글에서 더 자세히 다룰 것)

 

 

---

 

ANSI INNER JOIN

 

 

다음을 ANSI JOIN으로 풀어보시오

 

 

연습 1 : 모든 사원들의 사원번호 / 이름 / 부서이름 을 조회하시오 

 

USING ver.

SELECT employee_id, first_name, department_name

FROM employees e INNER JOIN departments d USING (delartment_id)

ORDER BY employee_id;

 

ON ver.

SELECT employee_id, first_name, department_name

FROM employees e INNER JOIN departments d ON e.delartment_id = d.department_id

ORDER BY employee_id;

 

 

원래 EQUI JOIN 같았으면

SELECT employee_id, first_name, department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id;

이렇게 해줬어야 함. 

 

 

연습  2 :  job_id가 'IT_PROG'인 사원들의 이름 /부서명/ 도시이름 을 조회하시오
SELECT first_name, department_name, city
FROM employees e 
INNER JOIN departments d USING (department_id)        ON e.department_id = d.department_id
INNER JOIN locations l USING (location_id)      ON d.location_id = l.location_id
WHERE job_id = 'IT_PROG';

 

연습  3: Seattle에서 근무하는 모든 사원의 이름/직책(job_title) /급여 를 조회 하시오
SELECT first_name, job_title, salary
FROM employees e
INNER JOIN departments d USING (department_id)      ON e.department_id = d.department_id 
INNER JOIN locations l USING (location_id)          ON d.location_id = l.location_id
INNER JOIN jobs j USING (job_id)        e.job_id = j.job_id   (★꼭 바로 앞의 테이블과 조인 하는 건 아닌가봄)
WHERE city = 'Seattle';

 

 

 

---

 

 

 

ANSI OUTER JOIN

조건을 충족하지 못해도 등장하는 JOIN

(+)를 붙이는 방식과 방향이 반대인 것에 주의 하기

 

ANSI INNER JOIN 은 INNER JOIN 밖에 없고

ANSI OUTER JOIN 은 LEFT / RIGHT / FULL OUTER JOIN 이 있음

 

ANSI JOIN 은 EQUI JOIN

ANSI OUTER JOIN 은 OUTER JOIN (+) 쓰는 거 

와 대응한다는 것 

 

 

 

 

방향 tip. 내가 쿼리문 위에 누워서 방향 따진다고 생각하면 됨.  (오 大 왼)

 

LEFT OUTER JOIN
오른쪽에 (+)를 추가하는 효과 (오른쪽에 null을 추가해 왼쪽을 더 등장시킨다)

 

RIGHT OUTER JOIN  
왼쪽에 (+)를 추가하는 효과 (왼쪽에 null을 추가해 오른쪽을 더 등장시킨다) 

 

FULL OUTER JOIN
양쪽에 (+)를 추가하는 효과
(양쪽에 등장하지 못한 행들을 한번씩 출력을 해주는데 null을 붙여서 등장시키기)

 

 

예시. 

SELECT emp.first_name, mgr.first_name 
FROM employees emp LEFT OUTER JOIN employees mgr    mgr에 (+) 붙인 효과  ON emp.manager_id = mgr.employee_id;

---

다음을 ANSI JOIN으로 풀어보시오

-

 

연습 1: 모든 사원들의 이름/매니저이름을 조회하되 매니저가 없는 사원도 함께 조회

SELECT emp.first_name "사원", mgr.first_name "매니저" 
FROM employees emp LEFT OUTER JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

 

 

연습 2 : 부서명/부서번호/주소/도시명을 출력하되 소속된 부서가 없는 도시도 함꼐 출력
SELECT department_name, department_id, street_address, city  
FROM departments d RIGHT OUTER JOIN locations l 
ON d.location_id = l.location_id;

연습 3 : 소속된 부서가 없는 도시만 출력
SELECT department_name , city 
FROM departments d RIGHT OUTER JOIN locations l 
ON d.location_id = l.location_id 
WHERE d.location_id IS NULL;