Join

/*
join -> 두 개 이상의 테이블을 연결 (join) 해서 데이터를 취득하는 방법이다 
현재 테이블에서 취득할 수 없는 컬럼의 데이터를 
상대 테이블에서 취득하기 위한 처리이다 

기본키(Primary ): 중복을 허용하지 않고 빈칸도 허용하지 않는다 
외래키 (Forign key): 조인을 하기 위한 컬럼 . null을 허용한다 

ERD: Entity / 개체 Relationship / 관계 Diagram / 도표 -> 즉, 관계도이다 

Join 의 종류 : 
             inner join <- 중요 
 full outer join 
 right outer join 
 cross join 
 self join <- 중요 

*/
--inner join(교집합 ) 

---- ansi sql, -- postgreSQl의 결과는 같다 
-- ansi sql
SELECT e.employee_id, e.first_name,
e.department_id, d.department_id,
d.department_name
from employees e INNER join departments d  
     on e.department_id = d.department_id; -- 조건 e 테이블에 있는 department_id 와 d 테이블에 있는
  --department_id 가 같을 때 
-- postgreSQl
--한쪽 테이블에만 있고 양쪽테이블에 둘 다 있는 컬럼이 아니라면 생략을 해도 된다
-- 조인이 된 객체 수가 궁금하다면 ? total rows 를 살펴 보기 
SELECT e.employee_id, e.first_name,
e.department_id, d.department_id,
d.department_name
from employees e, departments d 
where e.department_id = d.department_id;



--cross join
--ansi sql
SELECT e.employee_id, e.first_name,
e.department_id, d.department_id,
d.department_name
from employees e cross join departments d;

-- postgreSQl
SELECT e.employee_id, e.first_name,
e.department_id, d.department_id,
d.department_name
from employees e, departments d;


--outer join 
--ansi 문법 left
SELECT  e.employee_id, e.first_name,
e.department_id, d.department_id,
d.department_name
FROM employees e left  outer join departments d
on e.department_id = d.department_id;

--RIGHT OUTER JOIN 사용하기 

--순수 RIGHT JOIN
--얘는 겹치는 커플(교집합) 데이터를 지우는 거름망(WHERE 절)이 없기 때문에" 혼자 결과가 다르게 나온다
SELECT  e.employee_id, e.first_name,
e.department_id, d.department_id,
d.department_name
FROM employees e right  outer join departments d
on e.department_id = d.department_id;

--오른쪽 차집합

--"차집합 조인" 또는 "Pure Outer Join (순수 외부 조인)"
--사원 테이블의 부서 번호가 비어있는가? (where e.department_id is null)" 👉 참(True)!
SELECT  e.employee_id, e.first_name,
e.department_id, d.department_id,
d.department_name
FROM employees e right  outer join departments d
on e.department_id = d.department_id
where e.department_id is null;

-- right  outer join 
--사원의 고유 번호가 비어있는가? (where e.employee_id is null)" 👉 참(True)!
SELECT e.employee_id, e.first_name,
       e.department_id, d.department_id,
       d.department_name
FROM employees e RIGHT OUTER JOIN departments d
     ON e.department_id = d.department_id 
WHERE e.employee_id IS NULL;

--왼쪽 차집합
--WHERE d.department_id IS NULL -> 왼쪽 차집합에  부서의 고유 번호가 비어있는가(NULL인가)?
--부서 없는 신입 사원 찾기
SELECT  e.employee_id, e.first_name,
e.department_id, d.department_id,
d.department_name
FROM employees e LEFT OUTER JOIN departments d  
on e.department_id = d.department_id
where d.department_id is null;


--full(full  outer join)

--FULL OUTER JOIN만 쓴 것
--"우리가 가진 데이터 전부 다 싹 긁어모아! 누락은 절대 없어!"
SELECT  e.employee_id, e.first_name,
e.department_id, d.department_id,
d.department_name
FROM employees e full  outer join departments d
on e.department_id = d.department_id;

--WHERE ... IS NULL 조건을 붙인 것
--"양쪽에 다 들어있는 완벽한 커플(정상 데이터)들은 다 저리 가! 짝 없는 애들만 남겨!"
SELECT  e.employee_id, e.first_name,
e.department_id, d.department_id,
d.department_name
FROM employees e full  outer join departments d
on e.department_id = d.department_id
where e.department_id is null 
 or d.department_id is null;

--self join : 동일한 테이블 join 
--SELF JOIN (자체 조인)

select emp.employee_id as "사원번호",emp.first_name as "사원이름",
emp.manager_id as "상사 id" , mgr.employee_id as "상사의 사원번호",
mgr.first_name as "상사 이름"
from employees emp join employees mgr --emp사원 mgr 상사 
on emp.manager_id = mgr.employee_id;


--posetgreSQl
--PostgreSQL(그리고 오라클 등)의 전통적인 과거 문법 방식
--SELF JOIN과 결과가 100% 똑같은 쿼리

SELECT emp.employee_id as "사원번호",emp.first_name as "사원이름",
emp.manager_id as "상사 id" , mgr.employee_id as "상사의 사원번호",
mgr.first_name as "상사 이름"
from employees emp, employees mgr
where emp.manager_id = mgr.employee_id;

 

 

--문제1) 사원들의 이름, 부서번호, 부서명을 출력하라
SELECT e.first_name, d.department_id, d.department_name
from employees e, departments d
WHERE e.department_id = d.department_id;

SELECT e.first_name, d.department_id, d.department_name
from employees e INNER join departments d  
     on e.department_id = d.department_id;

-- 문제2) 30번 부서의 사원들의 이름, 직업, 부서명을 출력하라
SELECT e.last_name, d.department_id, d.department_name
from employees e INNER join departments d  
     on e.department_id = d.department_id
where d.department_id = 30;

-- 문제3) 커미션을 받는 사원의 이름, 직업, 부서번호, 부서명을 출력하라

SELECT  e.last_name, d.department_id, d.department_name --,e.commission_pct
from employees e INNER join departments d  
     on e.department_id = d.department_id
WHERE e.commission_pct is not null;

-- 문제4) 지역번호 2500 에서 근무하는 사원의 이름, 직업, 부서번호, 부서명을 출력하라

SELECT e.last_name , e.job_id , d.department_id , d.department_name --, d.location_id 
from  employees e INNER join departments d  
on e.department_id = d.department_id
where d.location_id = 2500;

-- 문제5) 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라
SELECT e.employee_id, d.department_name --,e.first_name
from  employees e INNER join departments d  
on e.department_id = d.department_id
where e.first_name like '%A%';


-- 문제6) 사원이름과 그 사원의 관리자 이름을 출력하라
SELECT  e.first_name as "사원이름",m.first_name as "관리자"
from employees e join employees m
      on e.manager_id = m.employee_id;

-- 문제7) 사원이름과 부서명과 월급을 출력하는데 월급이 6000 이상인 사원을 출력하라
SELECT e.last_name as "사원 이름" , d.department_name , e.salary as "월급"
FROM employees e left outer join departments d
       on e.department_id = d.department_id
WHERE e.salary >=6000;

-- 문제8) first_name 이 TJ 이란 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하라
SELECT   e.first_name , e.hire_date , m.first_name,m.hire_date
from employees e, employees m 
where  e.first_name= 'TJ' and e.hire_date < m.hire_date;

 
-- 문제9) 급여가 3000에서 5000사이인 사원의 이름과 소속부서명 출력하라
SELECT e.last_name , d.department_name
from employees e left outer join departments d
       on e.department_id = d.department_id
where  3000<= e.salary and  5000 >= e.salary;

-- 문제10) ACCOUNTING 부서 소속 사원의 이름과 입사일 출력하라
SELECT e.last_name , e.hire_date , d.department_name
from employees e inner join departments d 
on e.department_id = d.department_id
where d.department_name like 'Accounting';

-- 문제11) 급여가 3000이하인 사원의 이름과 급여, 근무지를 출력하라
SELECT e.last_name, e.salary,   d.location_id
from employees e inner join departments d 
on e.department_id = d.department_id
where 3000>=e.salary;


-- 문제12) 관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을 관리자의 이름 및 입사일과 
-- 함께 표시하고 레이블명을 각각 emp name, emp hired, mgr name, mgr hired 로 지정하라.
SELECT emp.first_name , emp.hire_date , mgr.first_name , mgr.hire_date
from employees emp, employees mgr
where emp.manager_id =mgr.manager_id
  and emp.hire_date < mgr.hire_date;

-- 문제13) 지역별로 근무하는 사원의 수가 5명 이하인 경우, 사원이 적은 도시순으로 정렬하고 city와 사원수를 출력하시오.
select d.location_id, l.city, count(*)
 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
group by d.location_id, l.city
having count(*) <=5
order by count(*) asc;


-- 문제14) 지정한 부서번호, 사원이름 및 지정한 사원과 동일한 부서에서 근무하는 모든 사원을 표시하도록 하고 부서번호는 deptno, 사원이름은 사원, 동일한 부서에서 근무하는 사원은 동료로 표시하시오.
-- (부서번호, 사원이름, 동료 순으로 오름차순 정렬)
--self join, cross join 둘 다 사용가능 
--self join
select emp.first_name as 사원명, emp.department_id as deptno,
fri.department_id, fri.first_name as 동료
from employees emp, employees fri
where emp.department_id = fri.department_id
and emp.employee_id <> fri.employee_id
order by deptno asc, 사원명 asc, 동료 asc;