Join

• 한개 이상의 테이블에서 데이터 조회에 사용
• join on
• join using
• join은 from과 연결됨.
  

--1)where를 쓴 것은 벤더 문법
select first_name,employees.department_id, departments.department_id
from employees, departments
where employees.department_id = departments.department_id

--2)표준문법 사용
select first_name,department_id, departments.department_name
from employees join departments using(department_id);

--2.1) 다른 컬럼 이름으로 join
select first_name,department_id, departments.department_name
from employees join departments on(employees.department_id = departments.department_id);

-- 아우터 조인-- 한쪽은 있고, 다른쪽은 없는 경우
-- 아우터 조인 활용, 부서명은 존재하나 null이어서 emp쪽에는 자료가 없음. 부족한 곳에서 (+)
select dep.*, emp.employee_id, first_name, emp.salary
from departments dep, employees emp
where dep.manager_id = emp.employee_id(+); --벤더문법

--표준문법
select dep.*, emp.employee_id, first_name, emp.salary
from departments dep
left outer join employees emp on(dep.manager_id = emp.employee_id); 

-- groupby 활용
select 맨.first_name||'++'||맨.last_name 이름, count(*) 관리숫자, max(직.first_name||' '||직.last_name)
from employees 직 left outer join employees 맨 on(직.manager_id = 맨.employee_id)
group by 맨.first_name||'++'||맨.last_name
having count(*) >=3
order by 1;

--join 여러개
select first_name, country_name
from employees join departments on (employees.department_id = departments.department_id )
                join locations on (departments.location_id = locations.location_id)
                join countries on (locations.country_id = countries.country_id);

• no equi join 에는 범위로 between and 활용 가능
• self join : 자기 자신과 조인을 맺는 것.
• natural join : 칼럼이름이 같으면 무조건 조인. (잘안씀)
• 서브쿼리

select department_id, department_name
from employees
join departments using (department_id)
where department_id = 
(
select department_id
from employees
where last_name = 'Ellen'
);

• 다중행 서브쿼리 : 서브쿼리가 여러개일때 연산자 사용
  - >ALL 은 서브쿼리의 최대혹은 결과 보다 크다 라는 다중행 서브쿼리
  - >ANY 는 서브쿼리의 최소보다 크다라는 다중행 서브쿼리

--여러개중에 하나라도 일치하면 in
--여러개중 하나이상이 일치하면 any 또는 some
select *
from employees
where department_id in    -- =any 와 동일한 결과
(
select distinct department_id
from employees
where salary > 13000
);

• where에 집계함수가 있으면, 서브쿼리 이용. 아니면 join 써도됨.

--join
select first_name, salary, department_name, department_id
from employees join departments using (department_id) 
where salary > (select avg(salary) from employees where department_id = 50)
and salary > (
    select min(salary) 
    from employees join departments using(department_id) 
        join locations using(location_id)
        where city = 'South San Francisco');

--서브쿼리
select first_name, salary, department_name, department_id
from employees join departments using (department_id) 
where salary > 
    (select avg(salary)
    from employees
    where department_id = 50)
and salary > (
    select min(salary) from employees
    where department_id = (
        select department_id
        from departments
        where location_id = (
            select location_id
            from locations
            where city = 'South San Francisco')
    )
);