SQL - DAY 8

Correlate Subquery(상호관련 서브쿼리,상관서브쿼리)

• 처리순서
  1) main query(outer query) 먼저 수행, active set 결과 생성
  2) 첫번째행을 후보행으로 잡고 후보행 값을 서브쿼리에 전달
  3) 후보행 값을 사용해서 서브쿼리 수행한다.
  4) 서브쿼리 결과값을 사용해서 후보행과 비교해 참이면 그 행을 결과 집합에 저장, 거짓이면 버린다.
  5) 다음행을 후보행으로 잡고 2,3,4번 반복 수행로 잡고 2,3,4번 반복 수행

[문제]

자신의 부서 평균급여보다 더 많은 급여를 받는 사원들의 정보를 추출해주세요.

<Correlate Subquery 풀이>

SELECT
    *
FROM
    hr.employees e
WHERE
    salary > (
        SELECT
            AVG(salary)
        FROM
            hr.employees
        WHERE
            department_id = e.department_id
    );

Inline View

• 가상테이블
• FROM절에 괄호 안에 SELECT문(서브쿼리)을 Inline View라고 한다.

SELECT
    a.*,
    b.avg_sal
FROM
         hr.employees a
    JOIN (
        SELECT
            department_id,
            round(AVG(salary)) AS avg_sal
        FROM
            hr.employees
        GROUP BY
            department_id
    ) b ON a.department_id = b.department_id
WHERE
    a.salary > b.avg_sal;

EXISTS 연산자

• 후보행값이 서브쿼리에 존재하는지 여부를 판단하는 연산자
• 후보행값이 서브쿼리에 존재하면 True, 우리가 찾는 데이터 검색 종료
• 후보행값이 서브쿼리에 존재하면 않으면 False, 우리가 찾는 데이터가 아니다.
• EXISTS 앞에는 기준컬럼을 넣으면 안된다.
• 내부적으로 break기능이 있어 첫번째 존재를 찾으면 break하게 된다.

select * 
 from hr.employees a
 where exists (select 1
                from hr.employees 
                where manager_id = a.employee_id);

NOT EXISTS

• 후보행값이 서브쿼리에 존재하지 않는 데이터를 찾는 연산자
• 후보행값이 서브쿼리에 존재하지 않으면 True, 우리가 찾는 데이터 검색 종료
• 후보행값이 서브쿼리에 존재하면 False 우리가 찾는 데이터가 아니다.

[문제42]

소속사원이 있는 부서 정보를 출력해주세요

<IN 풀이>

SELECT
    *
FROM
    hr.departments a
WHERE
    department_id IN (
        SELECT 
            department_id
        FROM
            hr.employees
    );

<EXISTS 풀이>

SELECT
    *
FROM
    hr.departments a
WHERE
    EXISTS (
        SELECT
            1
        FROM
            hr.employees
        WHERE
            department_id = a.department_id
    );

[문제43]

소속사원이 없는 부서 정보를 출력해주세요

<NOT IN 풀이>

SELECT
    *
FROM
    hr.departments a
WHERE
    department_id NOT IN (
        SELECT 
            department_id
        FROM
            hr.employees
        WHERE
            department_id IS NOT NULL
    );

<NOT EXISTS 풀이>

SELECT
    *
FROM
    hr.departments a
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            hr.employees
        WHERE
            department_id = a.department_id
    );

[문제44]

Executive 부서이름의 소속된 모든 사원에 대한 employee_id, last_name, job_id 조인을 이용해서 출력해주세요.

<풀이>

SELECT
    a.employee_id,
    a.last_name,
    a.job_id
FROM
         hr.employees a
    JOIN hr.departments b ON a.department_id = b.department_id
WHERE
    b.department_name = 'Executive';

[문제45]

Executive 부서이름의 소속된 모든 사원에 대한 employee_id, last_name, job_id 서브쿼리을 이용해서 출력해주세요.

<풀이>

SELECT
    employee_id,
    last_name,
    job_id
FROM
    hr.employees 
WHERE
    department_id = (
        SELECT
            department_id
        FROM
            hr.departments
        WHERE
            department_name = 'Executive'
    );

[문제46]

사원들의 급여 등급에 포함된 등급정보를 출력해주세요.

<풀이>

SELECT
    a.*
FROM
    hr.job_grades a
WHERE
    EXISTS (
        SELECT
            1
        FROM
            hr.employees
        WHERE
            salary BETWEEN a.lowest_sal AND a.highest_sal
    );

<안좋은 풀이>

SELECT
    *
FROM
    hr.job_grades
WHERE
    grade_level IN (
        SELECT
            j.grade_level
        FROM
            hr.employees     e,
            hr.job_grades    j
        WHERE
            e.salary BETWEEN j.lowest_sal AND j.highest_sal
    );

• 해당 풀이는 hr.job_grades가 중복으로 엑세스 되었고, 필요없는 JOIN이 사용되었기 때문에 좋지 않은 쿼리문이다.

[문제47]

사원들의 급여 등급에 포함되지 않은 등급정보를 출력해주세요.

<풀이>

SELECT
    a.*
FROM
    hr.job_grades a
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            hr.employees
        WHERE
            salary BETWEEN a.lowest_sal AND a.highest_sal
    );

[문제48]

부서별 인원수를 출력해주세요

<세로방향 출력>

SELECT department_id, count(*)
FROM hr.employees
GROUP BY department_id;

<가로방향 출력>
악성코드
decode, case 을 from절에 있는 테이블의 행의 수 만큼 수행해야 한다.
107 * 11 = 1177 만큼 수행한다.

select 
    sum(decode(department_id,10,1)) as "10",
    sum(decode(department_id,20,1)) as "20",
    sum(decode(department_id,30,1)) as "30",
    sum(decode(department_id,40,1)) as "40",
    sum(decode(department_id,50,1)) as "50",
    sum(decode(department_id,60,1)) as "60",
    sum(decode(department_id,70,1)) as "70",
    sum(decode(department_id,80,1)) as "80",
    sum(decode(department_id,90,1)) as "90",
    sum(decode(department_id,100,1)) as "100",
    sum(decode(department_id,110,1)) as "110",
    sum(decode(department_id,null,1)) as "null"
from hr.employees;

<수정한 가로방향 출력>

• FROM 절 안에 인라인뷰를 넣어 세로방향을 가로방향으로 변환

select 
    sum(decode(department_id,10,cnt)) as "10",
    sum(decode(department_id,20,cnt)) as "20",
    sum(decode(department_id,30,cnt)) as "30",
    sum(decode(department_id,40,cnt)) as "40",
    sum(decode(department_id,50,cnt)) as "50",
    sum(decode(department_id,60,cnt)) as "60",
    sum(decode(department_id,70,cnt)) as "70",
    sum(decode(department_id,80,cnt)) as "80",
    sum(decode(department_id,90,cnt)) as "90",
    sum(decode(department_id,100,cnt)) as "100",
    sum(decode(department_id,110,cnt)) as "110",
    sum(decode(department_id,null,cnt)) as "부서가 없는 사원"
from (SELECT department_id, count(*) as cnt
    FROM hr.employees
    GROUP BY department_id);

---

[8일차 후기]

우선 수업이 끝나고 맨 처음 든 생각은 그동안 내가 쿼리문을 작성할때 전체적으로 성능부분은 생각하지 않고, 직관적으로만 짜려고 했다는 느낌이 들었다. 오늘 배웠던 것중 가장 기억에 남는 건 correlate subquery(상관서브쿼리) 를 통한 EXISTS 기법이었다. 상관서브쿼리가 모든 업무에 적용한다고 좋은건 아니지만 성능상 이슈로 인해 업무 특성에 맞춰 EXISTS와 함께 사용 한다면 좋은 퍼포먼스를 나타낼수 있었다. 그리고 이제 점점 쿼리문을 작성 할때 이렇게 작성하는게 다른 방법으로 작성하는 것보다 좋은 성능을 나타낼수 있는지도 생각 할 단계가 오는 것 같다. 그런 단계가 될라면 좀더 신중하게 작성하는 습관을 가져야 겠다고 생각이 든다.