[ORACLE] 여러 테이블 조회를 위한 테이블 조인
![[ORACLE] 여러 테이블 조회를 위한 테이블 조인](/assets/img/study_Oracle/2022-10-11-%5BORACLE%5D_%EC%97%AC%EB%9F%AC%20%ED%85%8C%EC%9D%B4%EB%B8%94%20%EC%A1%B0%ED%9A%8C%EB%A5%BC%20%EC%9C%84%ED%95%9C%20%ED%85%8C%EC%9D%B4%EB%B8%94%20%EC%A1%B0%EC%9D%B8/logo.png){kind=logo}
여러 테이블 조회를 위한 테이블 조인
TABLE JOIN
개념
- 조인을 사용하여 둘 이상의 테이블에서 데이터를 한 번에 쿼리할 수 있다.
- N개의 테이블 JOIN 시, 반드시 N-1 개의 조인 조건이 요구된다.
- 다음은 표준 SQL:1999에서 제공하는 조인 기법이다.
SELECT table1.column, table2.column
FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2 ON (table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)] |
[CROSS JOIN table2];
아래 예시를 보자.
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d -- 'e'와 'd'는 ALIAS 이다.
ON (e.department_id = d.department_id); -- ON 은 조인조건 절이다.
-- e 테이블의 department_id 와 d 테이블의 department_id 가 같은 값만 JOIN 한다.
| EMPLOYEE_ID | LAST_NAME | DEPARTMENT_ID | DEPARTMENT_ID_1 | LOCATION_ID | |
|---|---|---|---|---|---|
| 1 | 200 | Whalen | 10 | 10 | 1700 |
| 2 | 201 | Hartstein | 20 | 20 | 1800 |
| 3 | 202 | Fay | 20 | 20 | 1800 |
| 4 | 124 | Mourgos | 50 | 50 | 1500 |
| 5 | 141 | Rajs | 50 | 50 | 1500 |
| … | |||||
| 19 | 206 | Gietz | 110 | 110 | 1700 |
`employees’ 테이블에는 20명의 사원이 있고, ‘departments’ 테이블에는 8개의 부서가 존재한다.
위 쿼리에서 부서 번호가 같은 값만 요청했기 때문에, 그에 해당하는 값만 출력된다.
위 쿼리 출력 결과 19개의 데이터 행이 출력되었는데, 한 명의 사원이 쿼리 결과에서 제외된 것을 알 수 있다.
해당 사원은 ‘대기발령자’로, 부서 번호가 NULL 이기 때문에 조인 조건에서 걸러져 제외되었다.
만약에 해당 부서가 어떤 부서인지 알려면 어떻게 해야 할까?
위 쿼리문의 SELECT 절에서 ‘d.department_name’ 을 추가하면 될 것이다.
원래 각 부서에 대한 자세한 정보는 ‘employees’ 테이블에서는 조회가 불가능하다.
하지만 JOIN 을 통해 ‘departments’ 테이블을 연결해서 출력하여 마치 하나의 테이블에서 조회한 것 처럼 할 수 있다.
ON 을 쓰지 않을 경우, 모든 ‘e’테이블의 행이 모든 ‘d’테이블의 행과 연결된다.
이 때 그냥 ON 절을 생략하면 오류가 발생한다.
표준 SQL에서는 JOIN 을 사용할 때 ON 이 필수적으로 있어야 하기 때문인데, JOIN 을 , 로 바꾸면 된다.
아니면 CROSS JOIN 이라는 ORACLE 전용 SQL로 바꾸어도 된다.
결과로는 20명의 사원 X 8개의 부서 = 총 160건의 행이 출력된다.
다시 위 표준 SQL JOIN 기법을 보자.
NATURAL JOIN 시 ‘table1’과 ‘table2’에 동일한 이름의 칼럼이 존재한다면, 이 칼럼들로 “동등 조인”이라는 것을 수행한다.
이는 조인 조건을 필요로 하지 않는다.
설령 데이터가 같더라도, 칼럼명이 다르면 안된다.
OUTER JOIN 은 동등 조인에서 누락되는 데이터, 즉 왼쪽에는 있는데 오른쪽에는 없거나, 오른쪽에는 있는데 왼쪽에는 없는 데이터를 포함시킨다. 왼쪽, 오른쪽, 모두는 옵션으로 지정할 수 있다.
기본적으로 NATURAL JOIN 과 마찬가지로 동등 조인을 수행한다.
Oracle 전용 SQL
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;
JOIN 대신에 , 가, 조인 조건으로 ON 대신에 WHERE 가 쓰였다.
Join 처리 시 Alias 사용
- 테이블 접두어를 사용하여 여러 테이블에 있는 열 이름을 한정
- 테이블 접두어를 사용하여 성능 향상
- 테이블 alias로 테이블에 짧은 이름을 지정
- SQL 코드 크기를 줄여 메모리를 적게 사용
- 유의사항
- 테이블 alias는 30자까지 사용할 수 있으면 길이는 짧을수록 좋다
- FROM 절의 특정 테이블 이름에 대해 테이블 alias가 사용될 경우 SELECT 문 전체에서 테이블 이름 대신 해당 테이블 alias를 사용해야 한다
- 테이블 alias는 의미 있는 단어 선택
- 테이블 alias는 현재 SELECT 문에 대해서만 유효
Natural Join
- NATURAL JOIN 절은 이름이 같은 두 테이블의 모든 행을 기반으로 한다
- 이 절은 두 테이블에서 대응되는 모든 열의 값이 동일한 행을 선택
- 동일한 이름을 가진 열이 서로 다른 데이터 유형을 가지면 오류 발생
SELECT department_id, department_name, location_id, city
FROM departments NATURAL JOIN locations;
| DEPARTMENT_ID | DEPARTMENT_NAME | LOCATION_ID | CITY | |
|---|---|---|---|---|
| 1 | 60 | IT | 1400 | Southlake |
| 2 | 50 | Shipping | 1500 | South San Francisco |
| 3 | 10 | Administration | 1700 | Seatle |
| 4 | 90 | Executive | 1700 | Seatle |
NATURAL JOIN 은 ‘departments’ 테이블과 locations 테이블에서 공통 칼럼인 ‘location_id’를 찾아서 이를 기준으로 동등 조인을 해준다.
사전에 테이블의 관계와 구조를 알고 있다면, NATURAL JOIN 을 통해 쉽게 조인을 할 수 있다.
Using절을 이용한 Join
- 여러 열이 이름은 같지만 데이터 유형은 다를 경우 USING 절을 사용하여 Equijoin에 대한 열을 지정할 수 있다.
- USING 절을 사용하면 두 개 이상의 열이 일치하는 경우 하나의 열만 일치하도록 할 수 있다.
SELECT employee_id, last_name,
location_id, department_id
FROM employees JOIN departments
USING (department_id);
| EMPLOYEE_ID | LAST_NAME | LOCATION_ID | DEPARTMENT_ID | |
|---|---|---|---|---|
| 1 | 200 | Whalen | 1700 | 10 |
| 2 | 201 | Hartstein | 1800 | 20 |
| 3 | 202 | Fay | 1800 | 20 |
| 4 | 144 | Vargas | 1500 | 50 |
| 5 | 143 | Matos | 1500 | 50 |
USING 절에는 두 테이블의 공통 칼럼을 넣어준다.
이 때 주의할 점이 있다.
다음과 같이 USING 절에 사용되는 열은 alias 지정 시 오류가 발생한다.
USING (e.department_id)
두 테이블에 걸쳐 공통된 칼럼이기 때문에, 특정 alias 를 사용하면 에러가 난다고 이해하면 되겠다.
Natural VS Using
- NATURAL JOIN은 공통된 칼럼이 있으면 자연스럽게 동등 조인을 해주는 것이고, USING을 사용한 JOIN은 두 개의 테이블에서 공통된 칼럼을 직접 명시해주는 것이다.
- 만약에 두 테이블에 이름이 같은 칼럼이 두 개 이상 있을 경우, NATURAL JOIN에서는 성립하지 않는다. 이 때는 USING을 사용하여 JOIN을 해야 한다.
- 이 경우 USING 에 공통된 칼럼들을 다 명시하는 방법도 있지만 특정 칼럼만을 명시할 수도 있을 것이다.
On 절을 이용한 Join
- ON 절을 사용하여 임의 조건을 지정하거나 조인할 열을 지정
- ON 절을 사용하여 조인 조건을 지정. 이렇게 하면 WHERE 절의 검색 또는 필터 조건과 별도로 조인 조건을 지정할 수 있다.
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);
ON 절을 이용한 JOIN 은 동등 조인의 표준이라고 할 수 있다.
추가로 조건을 넣고 싶다면, AND 를 사용하거나 WHERE 을 넣어도 상관없다.
On 절로 3-way Join 생성
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;
| EMPLOYEE_ID | CITY | DEPARTMENT_NAME | |
|---|---|---|---|
| 1 | 100 | Seatle | Executive |
| 2 | 101 | Seatle | Executive |
| 3 | 102 | Seatle | Executive |
| 4 | 103 | Southlake | IT |
| 5 | 104 | Southlake | IT |
3개의 테이블을 조인하기 때문에, 2개의 조인 조건 요구된다.
Oracle 전용 SQL
SELECT employee_id, city, department_name
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id;
AND d.location_id = l.location_id
Oracle 전용 SQL에서는 3개의 테이블을 모두 FROM 절에 나열한다.
그리고 조인 조건은 WHERE 절에 기술한다.
Using 을 사용한 SQL
SELECT e.employee_id, l.city, d.department_name
FROM employees e
JOIN departments d
USING (department_id)
JOIN locations l
USING (location_id)
Self Join
EMPLOYEES (WORKER)
EMPLOYEE_ID LAST_NAME MANAGER_ID 200 Whalen 101 201 Hartstein 100 205 Fay 201 206 Higgins 105 EMPLOYEES (MANAGER)
EMPLOYEE_ID LAST_NAME 200 Whalen 201 Hartstein 202 Fay 205 Higgins
WORKER 테이블의 MANAGER_ID는 MANAGER 테이블의 EMPLOYEE_ID와 같다.
즉 EMPLOYEES 테이블은 자기 자신을 참조하는 테이블이다.
SELECT worker.last_name emp, manager.last_name mgr
FROM employees worker JOIN employees manager
ON (worker.manager_id = manager.employee_id);
| EMP | MGR | |
|---|---|---|
| 1 | Hunold | De Haan |
| 2 | Fay | Hartstein |
| 3 | Gietz | Higgins |
| 4 | Lorentz | Hunold |
| 5 | Ernst | Hunold |
이를 재귀, 순환 모델을 가진다고 한다.
이는 다음과 같이 활용할 수 있다.
-- 누가 누구에게 보고하는지 출력
SELECT e.last_name || ' report to ' || m.last_name
FROM employees e JOIN employees m
ON (e.manager_id = m.employee_id);
-- 자신의 상사보다 먼저 입사한 사원을 출력
SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w, employees m
WHERE w. manager_id = m.employee_id
AND w.hire_date < m.hire_date;
비 동등 조인
동등(=)이 아닌, 비 동등에서의 조인이다.
EMPLOYEES
LAST_NAME SALARY 1 Whalen 4400 2 Hartstein 13000 3 Fay 6000 4 Higgins 12000 5 Gietz 8300 JOB_GRADES
GRADE_LEVEL LOWEST_SAL HIGHEST_SAL 1 A 1000 2999 2 B 3000 5999 3 C 6000 9999 4 D 10000 14999 5 E 15000 24999 6 F 25000 40000
‘JOB_GRADES’ 테이블은 각 ‘GRADE_LEVEL’에 대해 ‘LOWEST_SAL’ 및 ‘HIGHEST_SAL’ 값의 범위를 정의한다.
따라서 ‘GRADE_LEVEL’ 열을 사용하여 각 사원에 등급을 지정할 수 있다.
이 때 ‘EMPLOYEES’ 테이블의 ‘SALARY’ 칼럼과 ‘JOB_GRADES’ 테이블의 ‘LOWEST_SAL’, ‘HIGHEST_SAL’ 칼럼이 비 동등 조인된다.
SELECT e.last_name, e.salary, j.grade_level
FROM employees e JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;
| LAST_NAME | SALARY | GRADE_LEVEL | |
|---|---|---|---|
| 1 | Vargas | 2500 | A |
| 2 | Matos | 2600 | A |
| 3 | Davies | 3100 | B |
| 4 | Rajs | 3500 | B |
| 5 | Lorentz | 4200 | B |
Outer Join으로 직접 일치하지 않는 행 반환
DEPARTMENTS
DEPARTMENT_NAME DEPARTMENT_ID 1 Administration 10 2 Marketing 20 3 Shipping 50 4 IT 60 … 8 Contracting 190 부서 190에는 사원이 없다.
Equijoin with EMPLOYEES
DEPARTMENT_ID LAST_NAME 1 10 Whalen 2 20 Hartstein 3 20 Fay 4 110 Higgins … 19 80 Taylor “Grant” 사원에게는 부서 ID가 할당되지 않았다.
즉 동등 조인에서 ‘DEPARTMENTS’ 테이블의 “Contracting”과, ‘EMPLOYEES’ 테이블의 “Grant”가 누락되었다.
그 누락된 데이터를 포함시키는 것이 OUTER JOIN 이다.
Left Outer 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);
| LAST_NAME | DEPARTMENT_ID | DEPARTMENT_NAME | |
|---|---|---|---|
| 1 | Whalen | 10 | Administration |
| 2 | Fay | 20 | Marketing |
| 3 | Hartstein | 20 | Marketing |
| 4 | Vargas | 50 | Shipping |
| … | |||
| 20 | Grant | (null) | (null) |
LEFT OUTER JOIN 의 경우 왼쪽에 있는 테이블에는 존재하고, 오른쪽에 있는 테이블에는 존재하지 않는 데이터를 포함한다.
즉, 20번 행에서 ‘employees’ 테이블에 존재하는 “Grant”가 포함된다.
Oracle 전용 SQL
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);
Oracle 전용 SQL에서는 반대 방향에 (+) 기호를 넣는다.
Right Outer Join
SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
| LAST_NAME | DEPARTMENT_ID | DEPARTMENT_NAME | |
|---|---|---|---|
| 1 | Whalen | 10 | Administration |
| 2 | Fay | 20 | Marketing |
| 3 | Hartstein | 20 | Marketing |
| 4 | Vargas | 50 | Shipping |
| … | |||
| 20 | (null) | 190 | Contracting |
RIGHT OUTER JOIN 의 경우 오른쪽에 있는 테이블에는 존재하고, 왼쪽에 있는 테이블에는 존재하지 않는 데이터를 포함한다.
즉, 20번 행에서 ‘departments’ 테이블에 존재하는 “Contracting”이 포함된다.
Oracle 전용 SQL
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;
마찬가지로 반대 방향에 (+) 기호를 넣는다.
Full Outer Join
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);
| LAST_NAME | DEPARTMENT_ID | DEPARTMENT_NAME | |
|---|---|---|---|
| 1 | Whalen | 10 | Administration |
| 2 | Fay | 20 | Marketing |
| 3 | Hartstein | 20 | Marketing |
| 4 | Vargas | 50 | Shipping |
| … | |||
| 20 | Grant | (null) | (null) |
| 21 | (null) | 190 | Contracting |
FULL OUTER JOIN 은 양쪽에 매칭되지 않는(Both Not Match) 데이터를 포함한다.
따라서 21번 행까지 출력된다.
Oracle 전용 SQL
위와 마찬가지로 생각했을때 양쪽에 (+) 기호를 넣으면 될 것 같지만, 이는 불가능하다.
Oracle에서는 애시당초 FULL OUTER JOIN 이 없었기 때문에, 다음과 같이 표현해야한다.
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
union -- 중복된 값을 배제한 합집합
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);
Cartesian Product = Cross Join
CROSS JOIN 을 사용하여 카티션 프로덕트를 생성할 수 있다.
Oracle Syntax, ANSI 모두 카티션 프로덕트 생성이 가능하다.
- 다음과 같은 경우 Cartesian Product 생성
- 조인 조건이 생략된 경우
- 조인 조건이 잘못된 경우
- 첫 번째 테이블의 모든 행이 두 번째 테이블의 모든 행에 조인되는 경우
- Cartesian Product가 생성되지 않게 하려면 반드시 유효한 조인 조건을 포함해야 한다.
