수업내용/SQL
[2022.10.19.수] 조인
주니어주니
2022. 10. 19. 20:26
1. 조인
1-1. 조인
- 조인은 두 개 이상의 테이블을 연결지어서 데이터를 조회하는 것이다.
- 오라클과 같은 관계형데이터베이스는 모든 정보를 하나의 테이블에 저장하는 것이 아니라, 데이터가 정규화되어 여러 테이블에 분산되어 있다.
- 데이터가 여러 테이블에 흩어져 있기 때문에 사용자가 원하는 데이터를 찾기위해서는 여러 테이블을 다 조회해야 한다.
- 조인은 여러 테이블에 흩어져 있는 정보중에서 사용자가 원하는 정보만 가져와서 가상의 테이블처럼 만들어서 데이터를 조회하는 것이다.
- 조인을 위해 알아야 할 것
1. 내가 필요로 하는 정보가 어느 테이블에 있는지 알아야 함
2. 조인조건 작성
예시)
SELECT EMP.ENAME, DEPT.DNAME -- 나타내고싶은 정보
FROM EMP, DEPT -- 테이블 조인
WHERE EMP.DEPTNO = DEPT.DEPT -- 조인 조건
SELECT EMPLOYEES.EMPLOYEE_ID, EMPLOYEES.FIRST_NAME, DEPARTMENTS.DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS ( 원하는 정보가 있는 테이블들 )
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID; ( 조인조건 )
1-2. 조인의 종류
참고) 카티션곱(Cartesian product)
- 카티션곱은 조인조건을 지정하지 않고, 두 개이상의 테이블을 조인하는 것이다.
- 조인조건을 지정하지 않으면 조인된 테이블의 모든 데이터를 전부 가져오게된다.
- 조인 작업에 참여하는 테이블의 행수를 모두 곱한 값만큼의 행이 조회되는 행의 개수가 된다.
-- employees 테이블과 departments 테이블 조인하기 (조인조건 없이 조인하기)
-- employees 테이블의 모든 행과 departments 테이블의 모든 행이 각각 한번씩 조인된다.
select *
from employees, departments;
1) 등가조인 (Eui Join)
- 조인에서 가장 많이 사용되는 조인이다.
- 등가조인은 선행 테이블에서 데이터를 가져온 후 조인 조건절을 검사해서 동일한 값을 가진 데이터를 후행 테이블에서 꺼내오는 방법이다.
- 조인하는 테이블에서 같은 값을 가지는 행끼리 조인하는 것
- 조인조건에서 Equal(=) 연산자를 사용해서 EQUI Join이라고 한다.
--------------------------------------------------------------------------------
-- 등가조인
--------------------------------------------------------------------------------
-- 직원아이디, 직원이름, 직종아이디, 급여, 소속부서아이디, 소속부서명 조회하기
-- employees, employees, employees, employees, employees,
-- dapartments, dapartments
select e.employee_id, e.first_name, e.job_id, e.salary, d.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id
order by employee_id asc;
-- 직원아이디, 직원이름, 직종아이디, 직종제목, 직종 최저급여, 직종 최고급여, 급여 조회하기
-- employees, employees, employees, employees
-- jobs jobs jobs jobs
select e.employee_id, e.first_name, e.job_id, j.job_title, j.max_salary, j.min_salary, e.salary
from employees e, jobs j
where e.job_id = j.job_id
order by employee_id asc;
select e.employee_id, e.first_name, e.job_id, j.job_title, j.max_salary, j.min_salary, e.salary
from employees e join jobs j
on e.job_id = j.job_id;
-- 직원아이디, 직원이름, 직종아이디, 직종제목, 급여, 소속부서아이디, 소속부서명 조회하기
-- employees employees employees employees employees
-- jobs jobs
-- dapartments 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;
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; --조인 조건
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;
-- 부서아이디, 부서명, 소재지 우편번호, 소재지 주소, 소재지 도시명, 소재지 나라이름 조회하기
-- departments departments
-- locations locations locations
-- countries
-- 공통적으로 갖고있는 부분을 찾기
select d.department_id, d.department_name, l.postal_code, l.street_address, l.city, c.country_name
from departments d, locations l, countries c
where d.location_id = l.location_id and l.country_id = c.country_id;
select d.department_id, d.department_name, l.postal_code, l.street_address, l.city, c.country_name
from departments d join locations l
on d.location_id = l.location_id
join countries c
on l.country_id = c.country_id;
* 열 이름 같은 것끼리 조인하는 것 아님 !
* 등가조인은 같은 열을 찾아서 equal로 비교
비등가조인은 범위
2) 비등가조인 (Non-Eui Join)
- 조인조건을 지정할 때 조인 대상테이블에서 같은 값을 가진 데이터를 가져오는 대신, 크거나 작은 경우의 조건으로 데이터를 조회하는 조인 방식이 비등가 조인이다.
--------------------------------------------------------------------------------
-- 비등가조인
--------------------------------------------------------------------------------
create table salary_grades(
salary_grade char(1) primary key,
min_salary number(8) not null,
max_salary NUMBER(8) not null
);
insert into salary_grades(salary_grade, min_salary, max_salary) values('A', 0, 2499);
insert into salary_grades(salary_grade, min_salary, max_salary) values('B', 2500, 4999);
insert into salary_grades(salary_grade, min_salary, max_salary) values('C', 5000, 9999);
insert into salary_grades(salary_grade, min_salary, max_salary) values('D', 10000, 19999);
insert into salary_grades(salary_grade, min_salary, max_salary) values('E', 20000, 40000);
COMMIT;
-- 직원 아이디, 직원이름, 급여, 급여등급 조회하기
-- employees employees employees
-- salary_grades
select e.employee_id, e.first_name, e.salary, g.salary_grade
from employees e, salary_grades g
where e.salary >= g.min_salary and e.salary <= g.max_salary -- 비등가 조인 조건
order by e.employee_id ;
select e.employee_id, e.first_name, e.salary, g.salary_grade
from employees e join salary_grades g
on e.salary >= g.min_salary and e.salary <= g.max_salary -- 비등가 조인 조건
order by e.employee_id;
-- 직원아이디, 직원이름, 직종아이디, 직종제목, 급여, 급여등급, 소속부서명 조회하기
-- employees employees employees employees
-- jobs jobs
-- salary_grades
-- departments
select e.employee_id, e.first_name, j.job_id, j.job_title, e.salary, g.salary_grade, d.department_name
from employees e, jobs j, salary_grades g, departments d
where e.job_id = j.job_id and e.department_id = d.department_id
and e.salary >= g.min_salary and e.salary <= g.max_salary
order by employee_id;
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, J.JOB_ID, J.JOB_TITLE, E.SALARY, G.SALARY_GRADE, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN JOBS J
ON E.JOB_ID = J.JOB_ID
JOIN SALARY_GRADES G
ON E.SALARY >= G.MIN_SALARY AND E.SALARY >= G.MAX_SALARY
JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY E.EMPLOYEE_ID ASC;
-- 직원중에서 급여등급이 'B'등급인 직원의 아이디, 이름, 직종아이디, 급여 조회하기
-- salary_grades employees employees employees employees
select e.employee_id, e.first_name, e.job_id, e.salary
from employees e, salary_grades g
where e.salary >= g.min_salary and e.salary <= g.max_salary
and g.salary_grade = 'B';
select e.employee_id, e.first_name, e.job_id, e.salary
from employees e join salary_grades g
on e.salary >= g.min_salary and e.salary <= g.max_salary
where g.salary_grade = 'B';
3) 포괄조인 (Outer Join)
- 등가조인, 비등가조인은 조회하려는 데이터들이 조인에 참여하는 모든 테이블에 데이터가 존재하는 경우에만 조회된다.
- 포괄조인은 한쪽 테이블에는 데이터가 있고, 다른쪽 테이블에는 데이터가 없는 경우에 데이터가 있는 쪽 테이블의 내용을 전부 조회하는 조인방법이다.
* departments 테이블의 manager_id가 null인 것들은 employees 테이블의 employee_id와 일치하는게 없어서 조인이 안돼 (-> 값 출력이 안됨)
-> employees 테이블에 null값을 가지는 행을 추가시켜서 depalrtments 테이블에서 manager_id가 null인 것과 조인되게 함
-> where d.manager_id = e.employee_id(+) ---> oracle join
-> null이 없는 쪽에 (+)를 붙이는 것
--------------------------------------------------------------------------------
-- 포괄조인 : 테이블에서 조인
--------------------------------------------------------------------------------
-- 부서아이디, 부서명, 부서관리자 직원아이디, 직원이름 조회하기
-- dapartments departments departments(manager_id)
-- employees (employee_id) employees
-- 포괄조인을 사용해서 부서관리자 직원아이디가 null인 부서도 조회되게 하기
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;
-- left outer join은 왼쪽에 있는 테이블의 모든 행이 조회되게 하는 포괄조인 연산자 (ANSI join)
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;
-- right outer join은 오른쪽에 있는 테이블의 모든 행이 조회되게 하는 포괄조인 연산자 (ANSI 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;
4) 셀프조인 (Self Join)
- 하나의 테이블을 이용해서 조인을 구성하는 것이다.
- 하나의 테이블에 안에 상위데이터, 하위데이터가 있는 경우 상위데이터와 하위데이터를 서로 연관지어서 조회할 때 셀프조인이 필요하다.
- 하나의 테이블을 역할을 각각 나누어서 조인에 참여시켜야 한다
--------------------------------------------------------------------------------
-- 셀프조인 : 하나의 테이블로 조인을 구성하는 것
--------------------------------------------------------------------------------
-- 직원의 아이디, 직원이름, 관리자 아이디, 관리자 이름 조회하기
select emp.employee_id emp_id, emp.first_name emp_name, emp.job_id job_id,
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;
-- 직원의 아이디, 직원이름, 직원의 소속부서아이디, 직원의 소속부서명, 관리자 이름, 관리자의 소속부서명
-- employees employees employees
-- 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;
* where e.department_id = ed.department_id(+)
and e.manager_id = m.employee_id(+)
and m.department_id = md.department_id(+)
--employees 테이블에 department_id가 null인게 있음 -> departments 테이블의 department_id에도 null행을 추가 (+)
--employees 테이블에 manager_id가 null인게 있음 -> manager 테이블의 employee_id에도 null행을 추가 (+)
--직원테이블의 매니저아이디가 null -> 매니저테이블의 직원아이디의 null을 가리킴
-> 이 매니저테이블과 매니저부서테이블을 연결할 때, 매니저테이블의 null행이 가리키는 것이 있어야 완전한 조인
-> 마지막 문장에도 (+)를 해줘야 함
--------------------------------------------------------------------------------
-- 퀴즈
--------------------------------------------------------------------------------
-- 급여를 5000이상 10000이하로 받는 직원들의 직원아이디, 이름, 직종아이디, 직종제목, 급여 조회하기
-- 급여에 대한 오름차순 정렬
select e.employee_id, e.first_name, j.job_id, j.job_title, e.salary
from employees e, jobs j
where salary >= 5000 and salary <= 10000
order by salary asc;
-- 직원의 아이디, 이름, 직종아이디, 직종최저급여와 직종최고급여의 평균값, 급여 조회하기
-- employees employees employees salary
-- jobs jobs
-- 급여에 대한 오름차순 정렬
select e.employee_id, e.first_name, j.job_id, j.min_salary, j.max_salary, (j.min_salary + j.max_salary)/2 average, e.salary
from employees e, jobs j
where e.job_id = j.job_id
order by salary asc;
-- 2007년에 입사한 직원들의 직원아이디, 이름, 입사일, 소속부서명 조회하기
-- 직원아이디에 대한 오름차순 정렬
select e.employee_id, e.first_name, e.hire_date, d.department_name
from employees e, departments d
where e.department_id = d.department_id(+)
and e.hire_date >= '2007-01-01' and e.hire_date < '2008-01-01'
order by e.employee_id asc;
-- 소속부서 아이디가 50이나 80이고, 급여가 5000이상 10000이하인 직원들의 직원아이디, 이름, 직종아이디, 소속부서명 조회하기
-- 직원아이디에 대한 오름차순 정렬
select e.employee_id, e.first_name, e.job_id, e.salary, d.department_name
from employees e, departments d
where e.department_id = d.department_id
and e.department_id in (50, 80)
and e.salary >= 5000 and e.salary <= 10000
order by e.employee_id;
-- 100번 관리자에게 보고하는 직원들의 직원아이디, 이름, 직종아이디, 급여, 급여등급, 소속부서명을 조회하기
select e.employee_id, e.first_name, e.job_id, e.salary, g.salary_grade, d.department_name
from employees e, salary_grades g, departments d
where e.manager_id = 100
and e.salary >= g.min_salary and e.salary <= g.max_salary
and e.department_id = d.department_id
order by employee_id;
-- 급여등급이 '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;
-- 커미션을 받은 직원들의 아이디, 이름, 급여, 커미션, 급여등급을 조회하기
-- 급여에 대한 오름차순으로 정렬한다.
select e.employee_id, e.first_name, e.salary, e.commission_pct, g.salary_grade
from employees e, salary_grades g
where e.commission_pct is not null
and e.salary >= g.min_salary and e.salary <= g.max_salary
order by salary asc;
-- 'Toronto'에서 근무중인 직원의 아이디, 이름을 조회하기
select e.employee_id, e.first_name
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.city = 'Toronto';
-- 직원들 중에서 자신의 관리자보다 먼저 입사한 직원의 아이디, 이름, 입사일, 관리자이름, 관리자의 입사일으로 조회하기
-- 직원입사일에 대한 오름차순으로 정렬하기
select e.employee_id e_id,
e.first_name e_name,
e.hire_date e_hire_date,
m.first_name m_name,
m.hire_date m_hire_date
from employees e, employees m
where e.manager_id = m.employee_id
and e.hire_date < m.hire_date
order by e.hire_date;
-- 모든 부서의 부서아이디, 부서명, 부서 관리자 이름, 소재지 우편번호, 소재지 주소, 소재지 도시명을 조회하기
-- 부서관리자가 지정되지 않은 부서도 조회하고, 부서아이디에 대한 오름차순으로 정렬하기
select d.department_id, d.department_name, e.first_name, l.postal_code, l.street_address, l.city
from departments d, employees e, locations l
where d.manager_id = e.employee_id(+)
and d.location_id = l.location_id -- departments테이블의 location_id에는 null이 없으니까 locations테이블과 모두 연결 가능
order by d.department_id; -- ( d-> e (null값 있음) d-> l (연결해야 할 null값이 없음) )
-- 모든 직원들의 직원아이디, 이름, 직종아이디, 급여, 급여등급, 관리자이름, 소속부서아이디, 소속부서명, 소재도시명을 조회하기
-- 직원아이디에 대한 오름차순으로 정렬한다.
select e.employee_id e_id,
e.first_name e_name,
e.job_id e_job_id,
e.salary e_salary,
g.salary_grade e_salary_grade,
m.first_name m_name,
d.department_id e_dept_id,
d.department_name e_dept_name,
l.city e_city
from employees e, salary_grades g, employees m, departments d, locations l
where e.salary >= g.min_salary and e.salary <= g.max_salary
and e.manager_id = m.employee_id(+)
and e.department_id = d.department_id(+) -- 178의 department_id가 null, departments테이블에 null행을 추가
and d.location_id = l.location_id(+) -- departments의 null행도 locations를 조인해야 완전한 조인이기 때문에 locations에도 null행 추가
order by e.employee_id asc; -- ( e -> d -> l 로 null값도 연결 )
