[DB] 6. JOIN (ANSI JOIN) / Inner Join, Outer Join, Cross Join, Self Join

• 두 개의 table을 서로 연관해서 조회하는 것을 조인이라고 한다.

join은 2가지가 있다. 1. 오라클 join 2. Ansi join (모든 database 공용)

 

info table과 auth table이 두개를 사용하여 join 해보겠다.

join의 예시 두가지 table을 원하는 대로 붙여서 조회 (PK와 FK의 관계)

Inner Join

 

select * from info Inner join auth on info.auth_id = auth.auth_id;

INNER JOIN 결과값 (NULL값 출력 안됨)

Outer Join

 

1️⃣ LEFT OUTER JOIN

select * from info left outer join auth on info.auth_id = auth.auth_id;

LEFT OUTER JOIN 결과값

2️⃣ RIGHT OUTER JOIN

select * from info right outer join auth on info.auth_id = auth.auth_id;

RIGHT OUTER JOIN 결과값

3️⃣ FULL OUTER JOIN

select * from info full outer join auth on info.auth_id = auth.auth_id;

FULL OUTER JOIN 결과값

 

Cross JOIN

잘 몰라도 되지만 일단 이런것이다 알고 가는 것이 좋을 것같다.

잘못된 JOIN이다.

select * from info cross join auth;
select * from auth cross join info;

select * from info cross join auth의 결과

주가 되는 info에 auth의 라인이 1줄씩 계속 반복되어 붙는다.

 

 

Table 이름에 alias 주기

 

Table이름에 alias를 줘서 join시에 구분하기 쉽게 활용할 수 있다. (+ 긴 table이름을 적지 않아도 된다)

select * from info inner join auth on info.auth_id = auth.auth_id;

-- auth_id는 양쪽 테이블에 존재하기 때문에 테이블.컬럼명으로 지정해 주어야 한다.
select id, title, info.auth_id as auth_id, name, job
from info inner join auth on info.auth_id = auth.auth_id;

-- 테이블 alias를 이용한 조인
-- alias를을 지정해주고 on 뒤에서 alias를을 사용해 줄 수 있다.
-- select 뒤에 어떤것을 보여줄지 정하는 컬럼명 앞에 table alias를 적어주면 구분하기가 편하다.
select i.title, i.regdate, i.auth_id, a.name
from info i inner join auth a on i.auth_id = a.auth_id;

-- 조건 넣기도 talbe alias 사용 가능
select *
from info i inner join auth a on i.auth_id = a.auth_id
where i.auth_id = 1;

 

USING (KEY)

-- using(키) - 조인에 조건을 적어줄 수 있다.
--using (join으로 묶을 키값을 준다)
select * from info inner join auth using(auth_id);

• key값을 using()안에 넣어주어 key값으로 join을 할 수 있다. 그냥 join - on을 활용하는 것이 좋다.
  
  
  

여러개의 table을 join

-- 여러테이블 조인
select * from employees;
select * from departments;
select * from locations;

select * from employees e
left outer join departments d on e.department_id = d.department_id
left outer join locations l on d.location_id = l.location_id;

 

SELF JOIN ⭐중요⭐

• 자기자신을 붙여서 조회하는 것으로 순서에 주의하자.

-- 특별한 조인의 종류 self join ** 중요 **
select * from employees e1 left outer join employees e2 on e1.manager_id = e2.employee_id order by e1.employee_id asc;

select e2.employee_id,  
        e2.first_name, -- 상사
        e1.employee_id,
        e1.first_name  -- 직원 리스트
from employees e1 left outer join employees e2 on e1.manager_id = e2.employee_id order by e2.manager_id desc;

employees table 하나로 사원 명단과 각각의 매니저가 누구인지 manager_id를 통해 join 으로 matching 시켜줄 수 있다.

--연습문제

--문제 1.
---EMPLOYEES 테이블과, DEPARTMENTS 테이블은 DEPARTMENT_ID로 연결되어 있습니다.
---EMPLOYEES, DEPARTMENTS 테이블을 엘리어스를 이용해서
--각각 INNER , LEFT OUTER, RIGHT OUTER, FULL OUTER 조인 하세요. (달라지는 행의 개수 확인)
select * from employees e inner join departments d on e.department_id = d.department_id; -- 106
select * from employees e left outer join departments d on e.department_id = d.department_id; --107
select * from employees e right outer join departments d on e.department_id = d.department_id; --122
select * from employees e full join departments d on e.department_id = d.department_id; --123

--문제 2.
---EMPLOYEES, DEPARTMENTS 테이블을 INNER JOIN하세요
--조건)employee_id가 200인 사람의 이름, department_id를 출력하세요
--조건)이름 컬럼은 first_name과 last_name을 합쳐서 출력합니다
select * from employees;
select * from departments;

select e.employee_id, concat(e.first_name||' ', e.last_name), d.department_id
from employees e inner join departments d on e.department_id = d.department_id
where employee_id >= 200;


--문제 3.
---EMPLOYEES, JOBS테이블을 INNER JOIN하세요
--조건) 모든 사원의 이름과 직무아이디, 직무 타이틀을 출력하고, 이름 기준으로 오름차순 정렬
--HINT) 어떤 컬럼으로 서로 연결되 있는지 확인
select * from employees;
select * from jobs;

select concat(e.first_name||' ', e.last_name), j.job_id, j.job_title 
from employees e inner join jobs j on e.job_id = j.job_id 
order by j.job_id asc;

--문제 4.
----JOBS테이블과 JOB_HISTORY테이블을 LEFT_OUTER JOIN 하세요.
select * from jobs;
select * from job_history;

select * from jobs j left outer join job_history jh on j.job_id = jh.job_id;

--문제 5.
----Steven King의 부서명을 출력하세요.
select * from departments;

select concat(e.first_name||' ', e.last_name), d.department_id, d.department_name 
from employees e inner join departments d on e.department_id = d.department_id
where e.first_name = 'Steven' and e.last_name = 'King';

--문제 6.
----EMPLOYEES 테이블과 DEPARTMENTS 테이블을 Cartesian Product(Cross join)처리하세요

select * from employees cross join departments;

--문제
--문제 7.
----EMPLOYEES 테이블과 DEPARTMENTS 테이블의 부서번호를 조인하고 
-- SA_MAN 사원만의 사원번호, 이름, 급여, 부서명, 근무지를 출력하세요. (Alias를 사용)
select * from departments;
select * from locations;

select e.employee_id , concat(e.first_name||' ', e.last_name), e.salary, d.department_name, l.street_address
from employees e 
inner join departments d on e.department_id = d.department_id
inner join locations l on d.location_id = l.location_id
where e.job_id = 'SA_MAN';

--문제 8.
---- employees, jobs 테이블을 조인 지정하고 job_title이 'Stock Manager', 'Stock Clerk'인 직원 정보만
--출력하세요.
select * from jobs;

select * from employees e inner join jobs j on e.job_id = j.job_id
where lower(j.job_title) = 'stock manager' or lower(j.job_title) = 'stock clerk';

select * from employees e inner join jobs j on e.job_id = j.job_id
where lower(j.job_title) like 'stock%';

-- 강사님 풀이
select * from employees e inner join jobs j on e.job_id = j.job_id
where j.job_title IN('Stock Manager', 'Stock Clerk');

--문제 9.
---- departments 테이블에서 직원이 없는 부서를 찾아 출력하세요. LEFT OUTER JOIN 사용
select * from departments;
select * from employees;

select * from departments d left outer join employees e on d.department_id = e.department_id
where e.employee_id is null;

--문제 10. 
---join을 이용해서 사원의 이름과 그 사원의 매니저 이름을 출력하세요
--힌트) EMPLOYEES 테이블과 EMPLOYEES 테이블을 조인하세요.
select * from employees;

select e1.first_name as employee, e2.first_name as manager -- 상사
from employees e1 left outer join employees e2 on e1.manager_id = e2.employee_id order by e1.employee_id asc;

--문제 11. 
----6. EMPLOYEES 테이블에서 left join하여 관리자(매니저)와, 매니저의 이름, 매니저의 급여 까지 출력하세요
----매니저 아이디가 없는 사람은 배제하고 급여는 역순으로 출력하세요
select * from employees order by department_id asc;
select * from employees e1 left join employees e2 on e1.manager_id = e2.manager_id;

select e1.employee_id, e1.first_name, -- 직원
        e2.first_name, e2.salary -- 상사
from employees e1 left outer join employees e2 on e1.manager_id = e2.employee_id
where e1.manager_id is not null
order by e2.salary desc;

----------------------------------------------------------------------------------------------
-- 추가 : 사원관계도 구성하기.
select * from employees;

select e1.employee_id,
        e1.first_name as 사원이름,  -- 직원 리스트
        --e2.employee_id,  
        e2.first_name as 매니저이름 -- 상사
from employees e1 left outer join employees e2 on e1.manager_id = e2.employee_id order by e1.employee_id asc;

--select e2.first_name, e1.first_name, e3.first_name
--from employees e1 
--left outer join employees e2 on e1.manager_id = e2.employee_id 
--left outer join employees e3 on e2.manager_id = e3.employee_id
--order by e2.first_name desc;

select 
    case when e2.first_name is null then e1.first_name
         when e3.first_name is null then concat(e2.first_name ||' > ', e1.first_name)
    else concat(concat(e3.first_name||' > ', e2.first_name ||' > '), e1.first_name)
    end as 사원관계도
from employees e1 
left outer join employees e2 on e1.manager_id = e2.employee_id 
left outer join employees e3 on e2.manager_id = e3.employee_id
order by e3.first_name desc;


---오라클 조인
--inner join
select * from employees e, departments d --조인테이블
where e.department_id = d.department_id; --조인키

-- outer join (+) 가 붙음 left join
select * from employees e, departments d 
where e.department_id = d.department_id(+);

--right outer
select * from employees e, departments d --조인테이블
where e.department_id(+) = d.department_id; --조인키