하나 이상의 테이블로부터 자료를 검색하기 위해 조인(JOIN)을 사용합니다.
일반적으로 PRIMARY KEY(PK)와 FOREIGN KEY(FK)를 사용하여 JOIN하는 경우가 대부분이지만, 논리적인 값들의 연관으로 JOIN하는 경우도 있음
조인의 종류
|
구분
|
조인의 종류
|
내용
|
|
결과집합의 구성 유형
|
Inner Join
|
결과집합 구성에 필수적인 테이블과 조인
|
|
Outer Join
|
결과집합 구성에 선택적인 테이블과 조인
|
|
조인연산자의 유형
|
Equi Join
|
= (Equal) 연산자를 이용한 조인
|
|
Non_Equi Join
|
= (Equal) 이외의 연산자를 이용한 조인
카티젼 조인 : 조인조건이 없거나 일부 누락된 조인
|
1. 카티젼 조인(Cartesian Join)
두 테이블으 모든 행들의 Join이다.
M:M Join으로써 Join되는 집합 간에 연결고리 조건이 전혀 없는 경우 발생한다. Join에 사용된 테이블의 곱(M*M)으로 두 개의 테이블의 모든 데이터를 리턴한다.
SELECT JOB_TITLE, DEPARTMENT_NAME FROM JOBS, DEPARTMENTS;
SELECT COUNT(JOB_ID) FROM JOBS;
결과 : 19
SELECT COUNT(DEPARTMENT_ID) FROM DEPARTMENTS;
결과 : 27
SELECT 19* 27 FROM DUAL;
결과 : 513
2. 조인 기본 원리 이해
요건) Last_name 이 ‘King'인 사원의 사원번호와 부서이름을 출력하시오
- 사원번호는 EMPLOYEES 테이블에 EMPLOYEE_ID
- 부서이름은 DEPARTMENTS 테이블에 DEPARTMENT_NAME임
1) 사원테이블에서 Last_Name이 ‘King' 인 사원을 검색하여 ‘King' 사원의 부서번호를 확인한다.
질의문)
SELECT EMPLOYEE_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE LAST_NAME = 'King'
결과)
2) 부서번호로 부서테이블에서 부서 이름을 확인한다.
질의문)
SELECT DEPARTMENT_ID
, DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID IN ('80','90')
;
결과)
3) 조인을 이용한 질의문 작성
위 두 번의 질의를 통해 결과를 얻어 온 결과, 처음 질의에서 사원 'King'에 대한 부서번호를 얻고, 그 부서번호를 이용하여 부서 테이블에 접근하여 부서이름을 확인하였음
두 개의 테이블을 조인을 통해 연결하여 한번에 결과를 얻는 방법을 Join이라 함
질의문)
SELECT E.EMPLOYEE_ID
, E.LAST_NAME
, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE LAST_NAME = 'King'
AND EMPLOYEE_ID = 156
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
;
결과)
Join절을 사용한 명시적 조인
문법)
SELECT 컬럼이름1, 컬럼이름2,...
FROM 테이블이름1
JOIN 테이블이름 2
ON 테이블이름1.컬럼이름1 = 테이블이름2.컬럼이름;
예)
SELECT EMPLOYEES.EMPLOYEE_ID
, DEPARTMENTS.DEPARTMENT_NAME
FROM EMPLOYEES
JOIN DEPARTMENTS
ON EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
AND LAST_NAME = 'King'
;
위 경우 FROM절에 JOIN임을 명시하였고, ON절에 두 테이블 JOIN하기 위한 조건을 정의하였습니다. (권장)
문제) 사원이름이 ‘King' 인 사원의 사원번호, 업무명, 부서명을 출력하시오
방법1)
|
|
SELECT E.EMPLOYEE_ID
, J.JOB_TITLE
, 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
AND E.LAST_NAME = 'King'
;
|
방법2)
|
|
SELECT E.EMPLOYEE_ID
, J.JOB_TITLE
, D.DEPARTMENT_NAME
FROM EMPLOYEES E
, JOBS J
, DEPARTMENTS D
WHERE E.JOB_ID = J.JOB_ID
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.LAST_NAME = 'King'
;
|
결과)
[중요]
하나 이상의 테이블로부터 질의문을 작성할 경우
1) 요구하는 정보가 무엇인가?
2) 요구하는 정보를 어느 테이블에서 가져올 것인가?
3) 어떤 컬럼이 해당하는 정보를 가지고 있는가?
해설) 위 질의문으로 정리하면,
1) 요구하는 정보는 무엇인가?
사원번호, 업무이름, 부서이름
2) 요구하는 정보를 어느 테이블에서 가져 올 것인가?
사원(EMPLOYEES), 업무(JOBS), 부서(DEPARTMENTS)
3) 어떤 컬럼이 해당하는 정보를 가지고 있는가?
사원번호 : EMPLOYEES.EMPLOYEE_ID
업무명 : JOBS.JOB_TITLE
부서명 : DEPARTMENTS.DEPARTMENT_NAME
3. OUTER JOIN
문제1) 2007년 상반기 입사한 사원의 사원번호, 이름, 입사일을 출력하시오.
SELECT
EMPLOYEE_ID
, LAST_NAME
, HIRE_DATE
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYYMM')
BETWEEN '200701' AND '200706'
;
결과)
문제1-1) 2007년 상반기 입사한 사원의 사원번호, 이름, 입사일, 부서명을 출력하시오
SELECT
E.EMPLOYEE_ID
, E.LAST_NAME
, E.HIRE_DATE
, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE TO_CHAR(HIRE_DATE, 'YYYYMM')
BETWEEN '200701' AND '200706'
;
결과)
문제점 : 1번 질의문 결과 사원번호 178번 사원의 정보가 1.1번 질의문 결과에서 제외됨
SELECT
E.EMPLOYEE_ID
, E.LAST_NAME
, E.HIRE_DATE
, E.DEPARTMENT_ID
FROM EMPLOYEES E
WHERE TO_CHAR(HIRE_DATE, 'YYYYMM')
BETWEEN '200701' AND '200706'
AND E.EMPLOYEE_ID = 178
;
위와 같이 부서번호에 NULL값이 있음을 확인할 수 있습니다.
그러나 이런 경우 사원 'Grant'는 2007년 상반기에 입사했지만, 부서 발령이 없는 상태이기 때문에 부서 이름에 대한 정보가 없습니다. 하지만, 부서 이름을 함께 얻는 질의문의 수행 결과에서 제외된다면 사용자는 2007년 입사자가 11명으로 인지할 것입니다. 즉 잘못된 정보를 제공하게 됩니다.
이를 해결하기 위해 'Grant'는 질의 결과에 포함되어야 합니다. 다만, 부서이름이 없는 상태로 출력되어야 합니다.
해결방법 -> OUTER JOIN 사용
결과화면
명시적 JOIN 질의문)
SELECT
E.EMPLOYEE_ID
, E.LAST_NAME
, E.HIRE_DATE
, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE TO_CHAR(HIRE_DATE, 'YYYYMM')
BETWEEN '200701' AND '200706'
;
설명 : OUTER JOIN 은 조건(ON절)을 만족하는 행뿐만 아니라 특정 테이블을 지정하여 만족하지 않는 경우도 질의의 결과로 포함하고자 할 때 사용합니다.
위의 경우 JOIN 조건을 만족하지 않는 사원 'Grant'의 정보가 사원 테이블(EMPLOYEES)에 있으므로 JOIN을 중심으로 LEFT, 즉 왼쪽에 위치한 사원 테이블(EMPLOYEES)을 지정하였음
일반 질의문)
SELECT
E.EMPLOYEE_ID
, E.LAST_NAME
, E.HIRE_DATE
, D.DEPARTMENT_NAME
FROM EMPLOYEES E
, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
AND TO_CHAR(HIRE_DATE, 'YYYYMM')
BETWEEN '200701' AND '200706'
;
(+)로 지정된 테이블의 컬럼 값은 JOIN조건을 만족하지 않더라도 반환합니다.
즉 부서테이블의 부서 아이디 컬럼의 값에 대해서는 JOIN조건을 만족하지 않는 경우에도 포함됩니다.
4. SELF JOIN
사원테이블에서 사원과 해당 사원의 사원번호, 업무이름, 부서명, 관리자 이름을 출력하세요
단, 부서 이름은 Executive 부서만 출력하시오
1단계 질의문 확인)
사원테이블에서 관리자 아이디를 구함니다.
SELECT E.LAST_NAME, E.MANAGER_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.DEPARTMENT_NAME = 'Executive'
;
결과)
사원 'Kochar', 'DeHaan'는 100번 사원번호를 가진 사원을 자신의 관리자로 하고 있으며, 'King' 은 관리자를 두고 있지 않습니다. 100번 사원을 구해 보겠습니다.
2단계 질의 확인)
SELECT employee_id, last_name
FROM EMPLOYEES
WHERE employee_id = 100
;
결과)
이제 위 두개의 질의문을 통해 원하는 결과를 얻어보도록 하겠습니다.
"관리자 아이디(MANAGER_ID) 100번은 사원번호(EMPLOYEE_ID) 100과 같은 의미"입니다.
즉 관리자 또한 사원이므로, 사원테이블에서 관리되고 있음을 알수 있습니다., 따라서 하나의 테이블인 사원테이블에서 관리자 아이디와 사원번호가 같다면 관리자 이름을 얻을 수 있습니다.
질의문 확인)
SELECT
A.EMPLOYEE_ID, A.LAST_NAME || ' ' || A.FIRST_NAME AS "EMPLOYEE NAME", B.LAST_NAME AS "MANAGER NAME", J.JOB_TITLE, D.DEPARTMENT_NAME
FROM EMPLOYEES A JOIN EMPLOYEES B
ON A.MANAGER_ID = B.EMPLOYEE_ID
JOIN JOBS J
ON A.JOB_ID = J.JOB_ID
JOIN DEPARTMENTS D
ON A.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY A.EMPLOYEE_ID ASC
;
SELECT
A.EMPLOYEE_ID, A.LAST_NAME || ' ' || A.FIRST_NAME AS "EMPLOYEE NAME", B.LAST_NAME AS "MANAGER NAME", J.JOB_TITLE, D.DEPARTMENT_NAME
FROM EMPLOYEES A LEFT OUTER JOIN EMPLOYEES B
ON A.MANAGER_ID = B.EMPLOYEE_ID
JOIN JOBS J
ON A.JOB_ID = J.JOB_ID
LEFT OUTER JOIN DEPARTMENTS D
ON A.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY A.EMPLOYEE_ID ASC
;
