201221 SQL

-exists, not exists / in, not in(not in은 널값 체크해주고 있으면 조건절 추가)

 

-메인쿼리 테이블 값을 하나씩 뽑아서 서브쿼리 값과 비교, 조건에 부합하면 출력 

select *
from departments o 
where exists (select 'x'
         from employees
         where department_id == o.department_id);

-메인쿼리 테이블 값을 하나씩 뽑아서 서브쿼리 값과 비교, 조건에 부합하지 않으면 출력 
select *
from departments o 
where not exists (select 'x'
         from employees
         where department_id = o.department_id);

 

-subquery, inline view

from employees o
where salary > (select avg(salary)
from employees
where department_id = o.department_id);

select e2.last_name, e2.salary, e1.avg_sal
from (select department_id, round(avg(salary)) avg_sal
from employees
group by department_id) e1, employees e2
where e1.department_id = e2.department_id
and e2.salary > e1.avg_sal;

 

서브쿼리는 한 번 돌릴 때마다 부서 평균 계속 내고, 계속 비교. 비효율 인라인뷰는 테이블로 고정해두고 꺼내서 비교만 하는 것이므로 효율, 에너지 절약

 

-group by, having(그룹함수와 세트)

 

1)

select e.*
from(select employee_id, count(*)
from job_history
group by employee_id
having count(*) >=2) j, employees e <-먼저 제한하고
where j.employee_id = e.employee_id; <-조인 세 개

★★★★★ 이것이 효율적이다.

2)
select *
from (select employee_id, count(employee_id) as twice
from job_history
group by employee_id) e1, employees e2
where e1.employee_id = e2.employee_id <-조인 다 하고
and e1.twice >= 2; <-제한 나중에

 

-max(decode) -> pivot

 

select max(decode(d, '10', cn)) "10",

max(decode(d, '20', cn)) "20",

max(decode(d, '30', cn)) "30",

.

.40~90 생략

.

max(decode(d, '100', cn)) "100",

max(decode(d, '110', cn)) "110",

max(decode(d, null, cn)) "null"

from (select department_id d, count(*) cn from employees group by department_id);

 

-> 피벗을 쓰면 세 줄로 요약 가능

select *

from (select department_id from employees)

pivot(count(*) for department_id in (10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, null));

 

-피벗을 해 줄때는 인라인뷰에 그룹함수를 굳이 넣어줄 필요 없다. 피벗 안에만 넣어주면 된다. 

-->pivot과 인라인뷰 짝으로 정리해두기.