학원 day31. 조인

조인이란?

두 개 이상의 테이블을 연결지어서 데이터를 조회하는 것

 

- 오라클과 같은 관계형데이터베이스는 모든 정보를 하나의 테이블에 저장하는 것이 아니라, 데이터가 정규화되어 여러 테이블에 분산되어 있다.

- 데이터가 여러 테이블에 흩어져있기 때문에 사용자가 원하는 데이터를 찾기 위해서는 여러 테이블을 다 조회해야 한다.

 

조인은 여러 테이블에 흩어져 있는 정보 중에서 사용자가 원하는 정보만 가져와서 가상의 테이블처럼 만들어서 데이터를 조회하는 것이다.

* FROM EMPLOYEES, DEPARTMENTS -- 테이블 조인

* WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID -- 조인 조건

=> 조인조건을 작성하지 않으면 필요없는(부합하지 않는) 정보까지 전부 다 연결이 되어버리기 때문에 조인 조건을 작성해줘야 한다.

# 조인을 하기 위해서 두가지를 알아야 한다.

1. 내가 필요로 하는 정보가 어디 테이블에 있는지 알아야 한다.

2. 조인 조건을 작성할 수 있어야 한다. (어느 열끼리 같은 값인지 알아야 한다.)

 

* 조인 조건의 갯수 = 조인한 테이블의 갯수 -1

 

조인의 방법

- 조인 문법은 오라클에서만 사용하는 Oracle join과 모든 RDBMS(관계형 데이터베이스 관리시스템)에서 공통적으로 사용하는 ANSI(표준) JOIN이 있다.

- 조인이 수행될 때는 두개 이상의 테이블이 사용되는데, 둘 중에 먼저 읽는 테이블을 선행테이블이라 하고, 뒤에 오는 테이블을 후행 테이블이라고 한다.

- 선행 테이블은 조회할 데이터가 적은 테이블로 선택하는 것이 속도면에서 유리하다.

• Oracle Join

  SELECT a.col1, b.col2
  FROM table1 a, table2 b
  WHERE a.col2 = b.col2; -- Join 조건

• ANSI Join

  SELECT a.col1, b.col2
  FROM table1 a JOIN table2 b  -- 쉼표 없음
  ON a.col2 = b.col2

조인의 종류

카티션곱(Cartesian product)

- 조인에서 가장 중요한 부분은 조인조건을 지정해주는 부분이다.

- emp테이블과 dept테이블에서 서로 관련있는 데이터를 가져올 때, 조인조건을 보고 가져오게 되는데, 조인조건이 적절하지 않으면 틀린 데이터를 가져오거나 데이터를 가지고 오지 못할 수 있다.

- 카티션곱은 조인조건을 지정하지 않고, 두 개 이상의 테이블을 조인하는 것이다.

- 조인조건을 지정하지 않으면 조인된 테이블의 모든 데이터를 전부 가져오게 된다.

- ANSI SQL에서는 CROSS JOIN이라고 한다.

- 이런 경우 조인 작업에 참조되는 테이블의 행수를 모두 곱한 값만큼의 행이 조회결과가 된다.

 

SELECT *
FROM EMPLOYEES, DEPARTMENTS;

 

등가 조인 (Equi Join)

- 조인에서 가장 많이 사용되는 조인 방식

- 선행 테이블에서 데이터를 가져온 후 조인 조건절에서 검사해서 동일한 값을 가진 데이터를 후행 테이블에서 꺼내오는 방법이다.

- 조인조건을 작성할 때 테이블의 대표하는 컬럼(primary key)을 확인하고 그 컬럼의 값이 다른 테이블 어디에 있는지 확인하면 된다.

- 조인조건에서 EQUAL(=)연산자를 사용해서 EQUI JOIN이라고 한다.

* 주의할 점은 컬럼이름이 같다고 해서 조인하는 건 아니다. 같은 값을 갖고 있어야 조인할 수 있다. 

(따라서 같은 값을 갖고 있지만 테이블 조인 열의 이름이 다르면 SELECT절에 두개의 열이름을 모두 써주기도 한다.)

--------------------------------------------------------------------------------
-- 등가 조인 : 조인하는 테이블에서 같은 값을 가지는 행끼리 조인하는 것
--------------------------------------------------------------------------------

-- 직원아이디, 직원이름,   직종아이디, 직종제목, 직종 최저급여, 직종 최고급여, 급여를 조회하기
-- EMPLOYEES, EMPLOYEES, EMPLOYEES                                      EMPLOYEES
--                       JOBS       JOBS     JOBS          JOBS
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, J.JOB_ID, J.JOB_TITLE, J.MIN_SALARY, J.MAX_SALARY, E.SALARY
FROM EMPLOYEES E, JOBS J
WHERE E.JOB_ID = J.JOB_ID; 

-- ANSI조인
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, J.JOB_ID, J.JOB_TITLE, J.MIN_SALARY, J.MAX_SALARY, E.SALARY
FROM EMPLOYEES E JOIN JOBS J
ON E.JOB_ID = J.JOB_ID;

-- 직원아이디, 직원이름,   직종아이디, 직종제목, 급여,      소속부서아이디, 소속부서명을 조회하기
-- EMPLOYEES, EMPLOYEES,  EMPLOYEES            EMPLOYEES  EMPLOYEES
--                        JOBS       JOBS                 DEPARTMENTS    DEPARTMENTS  
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, J.JOB_ID, J.JOB_TITLE, E.SALARY, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, JOBS J, DEPARTMENTS D 
WHERE E.JOB_ID = J.JOB_ID 
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- ANSI조인은 한번에 조인을 2개밖에 못한다. 조인을 두번 해야 함.
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, J.JOB_ID, J.JOB_TITLE, E.SALARY, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN JOBS J
ON E.JOB_ID = J.JOB_ID 
JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- 부서관리자가 지정된 부서들의 부서 아이디,  부서이름,    부서관리자 아이디,       부서관리자 이름을 조회하기
--                            DEPARTMENTS  DEPARTMENTS DEPARTMENTS(MANAGER_ID)
--                                                     EMPLOYEES(EMPLOYEE_ID)   EMPLOYEES
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.MANAGER_ID, E.FIRST_NAME
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.MANAGER_ID IS NOT NULL       -- 필터링 조건
AND D.MANAGER_ID = E.EMPLOYEE_ID;    -- 조인 조건

-- ANSI조인은 필터링조건과 조인조건이 구분되는 장점이 있다.
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.MANAGER_ID, E.FIRST_NAME
FROM DEPARTMENTS D JOIN EMPLOYEES E
ON  D.MANAGER_ID = E.EMPLOYEE_ID   -- 조인 조건
WHERE D.MANAGER_ID IS NOT NULL;    -- 필터링 조건

 

비등가 조인(Non-Equi Join)

- 조인조건을 지정할 때 조인 대상테이블에서 같은 값을 가진 데이터를 가져오는 대신, 크거나 작은 경우의 조건으로 데이터를 조회하는 조인 방식이 비등가 조인이다.

- 비등가조인 사용하기

예시 : customer테이블과 gift테이블을 조인하여 고객별 마일리지 포인트를 조회한 후, 해당 마일리지 점수로 받을 수 있는 상품을 조회하여, 고객이름, 포인트, 받을 수 있는 선물 조회하기

포괄조인 (Outer Join)

- 등가조인, 비등가조인은 조회하려는 데이터들이 조인에 참여하는 모든 테이블에 데이터가 존재하는 경우에만 조회된다.

- 포괄조인은 한쪽 테이블에는 데이터가 있고, 다른쪽 테이블에는 데이터가 없는 경우에 데이터가 있는 쪽 테이블의 내용을 전부 조회하는 조인방법이다.

- 하지만, 포괄조인은 쿼리의 성능, 실행속도를 떨어뜨리기 때문에 포괄조인을 해야지만 되는 상황을 회피하는 것이 좋다.

- 포괄조인 사용하기

예시 : student테이블과 professor테이블을 조인하여 학생이름, 지도교수이름 조회하기. 단 지도교수가 정해지지 않는 학생도 조회하기

-- Oracle Join

SELECT s.name 학생명, p.name 교수명

FROM student s, professor p

WHERE s.profno = p.profno(+); -- 모자란쪽, NULL이 없는쪽 테이블에 (+)기호를 붙이면 된다. +기호가 없는쪽이 다 나와야 하는 곳이다. 

 

-- ANSI Join

SELECT s.name 학생명, p.name 교수명

FROM students LEFT OUTER JOIN professor p -- 선행테이블의 모든 행이 조회된다.

ON s.profno = p.profno;

 

--------------------------------------------------------------------------------
-- 포괄조인 : 조인 과정에서 조인되지 못한 행도 조회할 수 있게 하는 조인
--------------------------------------------------------------------------------

-- 부서아이디,     부서명,      부서관리자 직원아이디,     직원이름 조회하기
-- 포괄조인을 사용해서 부서관리자 직원아이디가 NULL인 부서도 조회되게 하였음
-- DEPARTMENTS   DEPARTMENTS  DEPARTMENTS(MANAGER_ID)
--                            EMPLOYEES (EMPLOYEE_ID)  EMPLOYEES
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.MANAGER_ID, E.FIRST_NAME
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.MANAGER_ID = E.EMPLOYEE_ID(+)
ORDER BY DEPARTMENT_ID ASC;

-- ANSI Join
-- LEFT OUTER JOIN은 (FROM절에서) 왼쪽에 있는 테이블의 모든 행이 조회되게 하는 포괄조인 연산자다.
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.MANAGER_ID, E.FIRST_NAME 
FROM  DEPARTMENTS D LEFT OUTER JOIN EMPLOYEES E
ON D.MANAGER_ID = E.EMPLOYEE_ID;

-- 직원아이디, 직원이름, 소속부서 아이디, 소속부서명을 조회하기
-- 포괄조인을 사용해서 소속부서 아이디가 NULL인 직원도 조회되게 하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_NAME 
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
ORDER BY E.EMPLOYEE_ID ASC;

-- ANSI Join
-- RIGHT OUTER JOIN은 오른쪽에 있는 테이블의 모든 행이 조회되게 하는 포괄조인 연산자다.
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_NAME 
FROM DEPARTMENTS D RIGHT OUTER JOIN EMPLOYEES E
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY E.EMPLOYEE_ID ASC;

 

셀프조인

- 하나의 테이블을 이용해서 조인을 구성하는 것이다.

- 하나의 테이블에 안에 상위데이터, 하위데이터가 있는 경우 상위데이터와 하위데이터를 서로 연관지어서 조회할 때 셀프조인이 필요하다. (조인할 테이블이 자기 자신)

- 하나의 테이블을 역할을 각각 나누어서 조인에 참여시켜야 한다

- 셀프조인 사용하기

예시 : emp테이블에서 사원이름과 그 사원의 상사이름을 조회하기

  -- Oracle Join
  SELECT 사원.ename, 상사.ename
  FROM emp 사원, emp 상사
  WHERE 사원.mgr = 상사.empno;
  
  -- ANSI Join
  SELECT 사원.enme, 상사.ename
  FROM emp 사원 JOIN emp 상사
  ON 사원.mgr = 상사.empno

--------------------------------------------------------------------------------
-- 셀프 조인 : 하나의 테이블로 조인을 구성하는 것
--------------------------------------------------------------------------------

-- 직원의 아이디, 직원이름, 관리자 아이디, 관리자 이름을 조회하기
SELECT EMP.EMPLOYEE_ID emp_id, EMP.FIRST_NAME emp_name,
       MGR.EMPLOYEE_ID mgr_id, MGR.FIRST_NAME mgr_name
FROM EMPLOYEES EMP, EMPLOYEES MGR
WHERE EMP.MANAGER_ID = MGR.EMPLOYEE_ID(+)
ORDER BY EMP.EMPLOYEE_ID ASC;

-- 직원의 아이디, 직원이름, 직원의 소속부서아이디, 직원의 소속부서명, 관리자 이름, 관리자의 소속부서명
-- EMPLOYEE     EMPLOYEE  EMPLOYEE
--                        DEPARTMENTS         DEPARTMENTS
--                                                             EMPLOYEES
--                                                                         DEPARTMENTS
SELECT E.EMPLOYEE_ID 직원아이디, E.FIRST_NAME 직원이름, ED.DEPARTMENT_NAME "직원의 소속부서명",
       M.FIRST_NAME 관리자이름, MD.DEPARTMENT_NAME "관리자의 소속부서명"
FROM EMPLOYEES E, EMPLOYEES M, DEPARTMENTS ED, DEPARTMENTS MD
WHERE E.MANAGER_ID = M.EMPLOYEE_ID(+)
AND E.DEPARTMENT_ID = ED.DEPARTMENT_ID(+)
AND M.DEPARTMENT_ID = MD.DEPARTMENT_ID(+)
ORDER BY E.EMPLOYEE_ID ASC;

 

-----------------------------------------------------------------------------------------------
-- 퀴즈
-----------------------------------------------------------------------------------------------
-- E               E      E           J              J                    E      
-- 직원의 아이디, 이름, 직종아이디, 직종최저급여와 직종최고급여의 평균값, 급여를 조회하기
-- 급여에 대한 오름차순으로 정렬한다.
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, (J.MAX_SALARY+J.MIN_SALARY)/2 avg, E.SALARY
FROM EMPLOYEES E, JOBS J 
WHERE E.JOB_ID = J.JOB_ID
ORDER BY E.SALARY ASC;

-- 직원의 아이디, 이름, 직종아이디, 직종최저급여와 직종최고급여의 평균값, 급여를 조회하기
-- 단, 해당 직종의 평균 급여보다 급여를 많이 받은 사원을 조회한다.
-- 급여에 대한 오름차순으로 정렬한다.
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, (J.MAX_SALARY+J.MIN_SALARY)/2 avg, E.SALARY
FROM EMPLOYEES E, JOBS J
WHERE E.JOB_ID = J.JOB_ID
AND SALARY > (J.MIN_SALARY + J.MAX_SALARY)/2
ORDER BY E.SALARY ASC;

-- 100번 관리자에게 보고하는 직원들의 직원아이디, 이름, 직종아이디, 급여, 급여등급, 소속부서명을 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.SALARY, G.SALARY_GRADE, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D, SALARY_GRADES G
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.SALARY >= G.MIN_SALARY AND E.SALARY <= G.MAX_SALARY
AND E.MANAGER_ID = 100;

-- 급여등급이 'A', 'B'에 해당하는 직원들의 직원아이디, 이름, 급여, 급여등급, 직종아이디를 조회하기
-- 급여등급에 대한 오름차순으로 정렬하고, 급여 등급이 동일한 경우 급여에 대한 오름차순으로 정렬한다.
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, G.SALARY_GRADE, E.JOB_ID
FROM EMPLOYEES E, SALARY_GRADES G
WHERE E.SALARY >= G.MIN_SALARY AND E.SALARY <= G.MAX_SALARY
AND G.SALARY_GRADE IN ('A', 'B')
ORDER BY G.SALARY_GRADE ASC, E.SALARY ASC;   -- AND로 연결 아니고 ,로 연결하기, WHERE절에서만 AND

-- 'Toronto'에서 근무중인 직원의 아이디, 이름을 조회하기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME 
FROM EMPLOYEES E, LOCATIONS L, DEPARTMENTS D
WHERE L.CITY = 'Toronto'
AND D.LOCATION_ID = L.LOCATION_ID
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- 모든 직원들의 직원아이디, 이름, 직종아이디, 급여, 급여등급, 관리자이름, 소속부서아이디, 소속부서명, 소재도시명을 조회하기
-- 직원아이디에 대한 오름차순으로 정렬한다.
SELECT E.EMPLOYEE_ID EMP_ID, E.FIRST_NAME EMP_NAME, E.JOB_ID EMP_JOB_ID, E.SALARY EMP_SALARY, G.SALARY_GRADE EMP_SALARY_GRADE, 
M.FIRST_NAME MGR_NAME, E.DEPARTMENT_ID EMP_DEPT_NAME, D.DEPARTMENT_NAME EMP_DEPT_NAME, L.CITY EMP_CITY
FROM EMPLOYEES E, SALARY_GRADES G, LOCATIONS L, DEPARTMENTS D, EMPLOYEES M
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
AND E.MANAGER_ID = M.EMPLOYEE_ID(+) 
AND E.SALARY >= G.MIN_SALARY AND E.SALARY <= G.MAX_SALARY
AND D.LOCATION_ID = L.LOCATION_ID(+)   -- 와.. 여기에도 +를 붙여야 하는구나.. 
ORDER BY E.EMPLOYEE_ID ASC;