240131 DB - 고급 JOIN 2

고급 Join

7. Cross join

조인을 하고자 하는 두 테이블의 모든 행을 기준으로 조인하는 유형

카테시안곱, 상호곱

모든 경우의 수를 만들고자 할 때 사용

의미 있는 결과가 아니며, 모든 조합을 만들어보고 싶을 때 간혹 사용

SQL> select 컬럼1, 컬럼2,... from 테이블1 cross join 테이블2;

 

---

연습문제 1

select location_id, street_address, city, state_province, country_name
from locations natural join countries;

 

연습문제 2

select e.last_name, e.job_id, d.department_id, d.department_name
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where l.city = 'Toronto';

 

연습문제 3

select e1.last_name "Employee", e1.employee_id "EMP#", e2.last_name "Manager", e2.last_name "Mgr#"
from employees e1 left outer join employees e2
on e1.manager_id = e2.employee_id
order by e1.employee_id;

 

연습문제 4

select e1.last_name, e1.hire_date, e2.last_name, e2.hire_date
from employees e1 join employees e2
on e1.manager_id = e2.employee_id
where e2.hire_date>e1.hire_date;

---

퀴즈1 2개 테이블 조인
employees 테이블을 사용하시오.
사원의 정보(employee_id, last_name, salary)와 사원의 매니저 정보(manager_id, last_name)를 함께 출력하시오.

select e1.employee_id, e1.last_name, e1.salary, e2.manager_id, e2.last_name
from employees e1 join employees e2
on e1.manager_id = e2.employee_id

퀴즈2 3개 테이블 조인
employees, departments 테이블을 사용하시오.

사원의 정보(employee_id, last_name, salary)와 사원의 매니저 정보(manager_id, last_name)와 사원이 소속된 부서 정보(department_id, department_name)를 함께 출력하시오.

select e1.employee_id, e1.last_name, e1.salary, e2.manager_id, e2.last_name, d.department_id, d.department_name
from employees e1 join employees e2
on e1.manager_id = e2.employee_id
join departments d
on e1.department_id = d.department_id;

 

퀴즈3 4개 테이블 조인
employees, departments 테이블을 사용하시오.
사원의 정보(employee_id, last_name, salary)와 사원의 매니저 정보(employees.manager_id, last_name)와 사원이 소속된 부서 정보(department_id, department_name)와 부서의 매니저 정보(departments.manager_id, last_name)를 함께 출력하시오.

select e1.employee_id, e1.last_name, e1.salary, e2.manager_id, e2.last_name, d.department_id, d.department_name,
          e3.manager_id, e3.last_name
from employees e1 join employees e2
on e1.manager_id = e2.employee_id
join departments d
on e1.department_id = d.department_id
join employees e3
on e3.employee_id = d.manager_id;

퀴즈4 5개 테이블 조인
employees, departments, locations 테이블을 사용하시오.
사원의 정보(employee_id, last_name, salary)와 사원의 매니저 정보(employees.manager_id, last_name)와 사원이 소속된 부서 정보(department_id, department_name)와 부서의 매니저 정보(departments.manager_id, last_name)와 부서의 위치 정보(city)를 함께 출력하시오.

select e1.employee_id, e1.last_name, e1.salary, e2.manager_id, e2.last_name, d.department_id, d.department_name,
          e3.manager_id, e3.last_name, l.city
from employees e1 join employees e2
on e1.manager_id = e2.employee_id
join departments d
on e1.department_id = d.department_id
join employees e3
on e3.employee_id = d.manager_id
join location l
on d.location_id = l.location_id;