[Oracle SQL] JOIN(카티션 곱, 동등 조인, 비동등 조인, 외부 조인, 자체 조인)

본 게시글은 이수안컴퓨터연구소의 데이터베이스 유튜브 동영상을 개인적으로 정리하는 글입니다. 

---

Join

• 두 개 이상의 테이블을 서로 연결하는데 사용되는 기법
• 테이블들은 특정 규칙에 따라 서로 상호 관계를 가짐

조인 기법	설명
카티션 곱(Cartesian Product)	모든 행에 대해서 조인
동등 조인(Equi Join)	조인 조건이 정확히 일치할 때 조회
비동등 조인(Non Equi Join)	조인 조건이 정확히 일치하지 않는 경우 조회
외부 조인(Outer Join)	조인 조건이 정확히 일치하지 않아도 모두 조회
자체 조인(Self Join)	자체 테이블에서 조인하여 조회

 

카티션 곱(Cartesian Product)

• 공통되는 컬럼 없이 조인 조건이 없어서 모든 데이터가 조회
• 발생가능한 모든 경우의 수의 행이 출력되는 것을 의미
• N 개의 행을 가진 테이블과 M 개의 행을 가진 테이블의 카티시안 곱은 N*M

SELECT * FROM employees;

 

SELECT * FROM departments;

 

SELECT * FROM employees, departments;

 

내부 조인

동등 조인

• 조인하는 테이블에서 조인 조건이 일치하는 것만 조회

SELECT * FROM jobs, job_history
WHERE jobs.job_id = job_history.job_id;

 

SELECT * FROM countries C, locations L
WHERE C.country_id = L.country_id;

 

SELECT * FROM employees E, departments D
WHERE E.department_id = D.department_id;

 

비동등 조인

• 테이블의 동일한 컬럼 없이 다른 조건으로 조인하는 방법

--j테이블의 최소 월급과 j테이블의 최대 월급 사이의 값을 가지는 E테이블의 월급이 있다면
--E테이블과 J테이블의 모든 컬럼 값을 출력
SELECT * FROM employees E, jobs J
WHERE E.salary BETWEEN J.min_salary AND J.max_salary;

 

--E 테이블의 고용날짜가 H테이블의 시작 날짜와 H테이블의 끝 날짜사이를 만족하는 데이터가 있다면
--E.first_name, E.hire_date, H.start_date, H.end_date를 출력
SELECT E.first_name, E.hire_date, H.start_date, H.end_date
FROM employees E, job_history H
WHERE E.hire_date BETWEEN H.start_date AND H.end_date;

 

외부 조인

• 외부 조인은 동등 조인 조건을 만족하지 못해 누락되는 행을 출력하기 위해 사용
• 외부조인은 (+)기호를 사용
  (+) 기호는 데이터 값이 부족한 테이블의 열 이름 뒤에 기술
  즉, (+)는 위 그림에서 색이 칠해져 있지 않은 테이블을 뜻함, 따라서 두 테이블의 교집합 부분만 null값이 아닌 값이 출력되고, 교집합이 없는 부분은 null값이 출력됨
• 예를 들어, 회원 테이블과 구매 테이블이 있다고 가정하면, 구매를 하지 않은 회원이 충분히 존재할 수 있다.
  따라서 회원 테이블과 구매 테이블을 동시에 보고 싶다면 구매 테이블에(+)를 붙이면 구매를 하지 않은 회원은 null로 보여진다.

SELECT * FROM jobs J, job_history H
WHERE J.job_id = H.job_id(+);

 

SELECT * FROM countries C, locations L
WHERE C.country_id = L.country_id(+);

 

SELECT * FROM employees E, departments D
WHERE E.department_id(+) = D.department_id;

 

자체 조인

• 자기 자신의 테이블과 조인하는 방법
• 자체 조인은 자신이 자신과 조인한다는 의미이다.
  그래서 자체 조인은 1개의 테이블을 사용한다.

직원 테이블을 조회한다고 해보자.

직원은 상급자일 수도 있고, 하급자일 수도 있다.

상급자는 여러 하급자를 가질 수 있다.

따라서 직원 테이블 내에서 특정 상급자 밑에 어떤 하급자들이 있는지 알아볼 때 등의 경우에 자체 조인을 사용한다.

SELECT E.first_name, E.last_name, M.first_name, M.last_name
FROM employees E, employees M
WHERE E.manager_id = M.employee_id;

 

연습 문제

--countries 테이블과 locations 테이블을 country_id를 기준으로 조인하여 country_name, state_province, street_address를 조회
SELECT C.country_name, L.state_province, L.street_address
FROM countries C, locations L
WHERE c.country_id = L.country_id;

 

--jobs 테이블과 job_history 테이블을 job_id를 기준으로 조인하여 job_id, job_title, start_date, end_date를 조회
SELECT J.job_id, J.job_title, H.start_date, H.end_date
FROM jobs J, job_history H
WHERE j.job_id = H.job_id;

 

--employees 테이블과 departments 테이블을 department_id 기준으로 조인하고, employees 테이블과 jobs 테이블을 job_id 기준으로
--조인하여 first_name, last_name, department_name, job_title을 조회
SELECT E.first_name, E.last_name, D.department_name, J.job_title
FROM employees E, departments D, jobs J
WHERE E.department_id = D.department_id
AND E.job_id = J.job_id;

 

--countries 테이블과 locations 테이블을 country_id를 기준으로 조인하여 country_name, state_province, street_address를 조회
SELECT C.country_id, C.country_name, L.city
FROM countries C,locations L
WHERE C.country_id = L.country_id(+);

 

--jobs 테이블과 job_history 테이블을 job_id를 기준으로 조인하여 job_id, job_title, start_date, end_date를 조회
SELECT E.employee_id, E.first_name, E.last_name, D.department_name
FROM employees E, departments D
WHERE E.department_id = D.department_id(+)
ORDER BY E.employee_id;

 

--employees 테이블과 departments 테이블을 department_id 기준으로 조인하고, employees 테이블과 jobs 테이블을 job_id 기준으로 조인하여
--first_name, last_name, department_name, job_title을 조회
SELECT E.first_name || ' ' || E.last_name employee, M.first_name || ' ' || M.last_name manager
FROM employees E, employees M
WHERE E.manager_id = M.employee_id
ORDER BY E.employee_id;