251024 TIL

2025. 10. 24. 18:04ㆍCourses/아이티윌 오라클 DBA 과정

서브쿼리

중첩 서브 쿼리(Cont.)

단일행 비교 연산자 사용시 주의점

서브쿼리에서 여러 개 값이 나올 경우 오류 발생

SELECT *
FROM hr.employees
WHERE salary = (SELECT min(salary)
                FROM hr.employees
                GROUP BY department_id);

다중행 서브 쿼리

서브 쿼리의 결과가 여러 개의 값이 나오는 서브 쿼리
다중 행 비교 연산자(IN, ALL, ANY)

IN(= OR)

SELECT *
FROM hr.employees
WHERE salary IN (SELECT min(salary)
                FROM hr.employees
                GROUP BY department_id);

ANY

OR 의 범주 속성을 가짐
> ANY : 최소값보다 크다 의미

SELECT *
FROM hr.employees
WHERE salary > any (SELECT salary
                    FROM hr.employees
                    WHERE job_id = 'IT_PROG');

SELECT *
FROM hr.employees
WHERE salary > 9000
OR salary > 6000
OR salary > 4800
OR salary > 4800
OR salary > 4200;

SELECT *
FROM hr.employees
WHERE salary > (SELECT min(salary)
                FROM hr.employees
                WHERE job_id = 'IT_PROG');

< ANY : 최대값보다 작다 의미

SELECT *
FROM hr.employees
WHERE salary < any (SELECT salary
                    FROM hr.employees
                    WHERE job_id = 'IT_PROG');

SELECT *
FROM hr.employees
WHERE salary < 9000
OR salary < 6000
OR salary < 4800
OR salary < 4800
OR salary < 4200;

SELECT *
FROM hr.employees
WHERE salary < (SELECT max(salary)
                FROM hr.employees
                WHERE job_id = 'IT_PROG');

= ANY : IN과 동일한 의미

SELECT *
FROM hr.employees
WHERE salary = any (SELECT salary
                    FROM hr.employees
                    WHERE job_id = 'IT_PROG');

SELECT *
FROM hr.employees
WHERE salary = 9000
OR salary = 6000
OR salary = 4800
OR salary = 4800
OR salary = 4200;

ALL

AND 범주 속성을 가짐
> ALL : 최대값 보다 크다 의미

SELECT *
FROM hr.employees
WHERE salary > all (SELECT salary
                    FROM hr.employees
                    WHERE job_id = 'IT_PROG');

SELECT *
FROM hr.employees
WHERE salary > 9000
AND salary > 6000
AND salary > 4800
AND salary > 4800
AND salary > 4200;

SELECT *
FROM hr.employees
WHERE salary > (SELECT max(salary)
                FROM hr.employees
                WHERE job_id = 'IT_PROG');

< ALL : 최소값보다 작다 의미

SELECT *
FROM hr.employees
WHERE salary < all (SELECT salary
                    FROM hr.employees
                    WHERE job_id = 'IT_PROG');

SELECT *
FROM hr.employees
WHERE salary < 9000
AND salary < 6000
AND salary < 4800
AND salary < 4800
AND salary < 4200;

SELECT *
FROM hr.employees
WHERE salary < (SELECT min(salary)
                FROM hr.employees
                WHERE job_id = 'IT_PROG');

= ALL : 서브쿼리에 다른 값들이면 의미가 없이 데이터가 추출되지 않음

SELECT *
FROM hr.employees
WHERE salary = all (SELECT salary
                    FROM hr.employees
                    WHERE job_id = 'IT_PROG');

SELECT *
FROM hr.employees
WHERE salary = 9000
AND salary = 6000
AND salary = 4800
AND salary = 4800
AND salary = 4200;

문제

-- [문제32] 관리자 사원들의 정보를 추출해주세요.
SELECT *
FROM hr.employees
WHERE employee_id in (SELECT manager_id
                        FROM hr.employees);

-- [문제33] 관리자 아닌 사원들의 정보를 추출해주세요.
SELECT *
FROM hr.employees
WHERE employee_id not in (SELECT manager_id
                            FROM hr.employees
                            WHERE manager_id is not null);

OR 진리표

OR	TRUE	FALSE
TRUE	TRUE	TRUE
FALSE	TRUE	FALSE
NULL	TRUE	NULL

AND 진리표

AND	TRUE	FALSE
TRUE	TRUE	FALSE
FALSE	FALSE	FALSE
NULL	NULL	FALSE

SELECT *
FROM hr.employees
WHERE employee_id not in (SELECT manager_id
                            FROM hr.employees)

SELECT *
FROM hr.employees
WHERE not(employee_id = null
OR employee_id = 100
OR employee_id = 100
OR employee_id = 102
OR employee_id = 103);  

SELECT *
FROM hr.employees
WHERE employee_id != null
AND employee_id != 100
AND employee_id != 100
AND employee_id != 102
AND employee_id != 103;

NULL 값 때문에 데이터가 조회되지 않음
IS NOT NULL로 NULL 값을 제외시켜줘야 함

correlated subquery(상호 관련 서브 쿼리, 상관 서브 쿼리)

행 단위 처리에 사용
inner query(subquery) 는 main(outer) query의 모든 행에 대해서 한번씩 수행된다.

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

SELECT *
FROM hr.employees e, 
    (SELECT department_id, avg(salary) avg_salary
    FROM hr.employees
    GROUP BY department_id) a
WHERE e.salary > a.avg_salary
AND e.department_id = a.department_id;

correlated subquery 수행 단계

main(outer) query 먼저 수행
첫 번째 행을 후보행으로 잡고, 후보행 값을 서브 쿼리에 전달
후보행 값을 이용해서 서브 행
서브 쿼리 결과 값을 사용해서 후보행과 비교한 후 참이면 결과 집합(메모리)에 저장하고, 거짓이면 버림
다음 행을 후보 행으로 잡고 메인 쿼리의 행 수만큼 반복 수행

exists 연산자

후보행 값이 서브쿼리에 존재하는지 여부를 판단하는 연산자
후보행 값이 서브쿼리에 존재하면 TRUE, 우리 찾는 데이터 검색 종료, 후보행을 메모리에 저장
후보행 값이 서브 쿼리에 존재하지 않으면 FALSE, 우리가 찾는 데이터가 아님

SELECT *
FROM hr.employees e
WHERE exists (SELECT 'x' -- 1, 'x', null 등 모두 가능, 존재 여부만 체크하면 됨
                FROM hr.employees
                WHERE manager_id = e.employee_id);

not exists 연산자

후보행 값이 서브쿼리에 존재하지 않는 데이터를 찾는 연산자
후보행 값이 서브쿼리에 존재하지 않으면 TRUE, 우리 찾는 데이터 검색 종료, 후보행을 메모리에 저장
후보행 값이 서브 쿼리에 존재하면 FALSE, 우리가 찾는 데이터가 아님

SELECT *
FROM hr.employees e
WHERE not exists (SELECT 'x'
                FROM hr.employees
                WHERE manager_id = e.employee_id);

문제

-- [문제34] 소속사원이 있는 부서정보를 출력해주세요.
SELECT *
FROM hr.departments d
WHERE exists (SELECT 1
                FROM hr.employees
                WHERE department_id = d.department_id);           

SELECT *
FROM hr.departments d
WHERE d.department_id in (SELECT department_id
                            FROM hr.employees);

-- [문제35] 소속사원이 없는 부서정보를 출력해주세요.                
SELECT *
FROM hr.departments d
WHERE not exists (SELECT 1
                FROM hr.employees
                WHERE department_id = d.department_id);

SELECT *
FROM hr.departments d
WHERE d.department_id not in (SELECT department_id
                            FROM hr.employees
                            WHERE department_id is not null);

INLINE VIEW

가상 테이블
FROM 절에 SUBQUERY를 인라인뷰라고 한다.

SELECT e.*
FROM hr.employees e, 
    (SELECT department_id, avg(salary) avg_sal
    FROM hr.employees
    GROUP BY department_id) e1
WHERE e.department_id = e1.department_id
AND e.salary > e1.avg_sal;

문제

-- [문제36] 부서별 입사인원수를 가로방향으로 출력해주세요.
SELECT 
    count(case department_id when 10 then 1 end) "10",
    count(case department_id when 20 then 1 end) "20",
    count(case department_id when 30 then 1 end) "30",
    count(case department_id when 40 then 1 end) "40",
    count(case department_id when 50 then 1 end) "50",
    count(case department_id when 60 then 1 end) "60",
    count(case department_id when 70 then 1 end) "70",
    count(case department_id when 80 then 1 end) "80",
    count(case department_id when 90 then 1 end) "90",
    count(case department_id when 100 then 1 end) "100",
    count(case department_id when 110 then 1 end) "110",
    count(case when department_id is null then 1 end) "부서가 없는 사원"
FROM hr.employees;

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

decode 함수(또는 case문)를 테이블 로우 수만큼 수행
- 첫 번째 쿼리 : 12 * 107
- 두 번째 쿼리 : 12 * 12

pivot 함수

행(세로) 데이터를 열(가로) 방향으로 변경하는 함수

SELECT *
FROM (SELECT department_id
        FROM hr.employees)
PIVOT (count(*) FOR department_id IN (10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, null "부서가 없는 사원"));

문제

-- [문제37] 년도별 입사 인원수를 출력해주세요. (가로방향으로 출력해주세요)
SELECT * 
FROM (SELECT to_char(hire_date, 'yyyy') year, count(*) cnt
        FROM hr.employees
        GROUP BY to_char(hire_date, 'yyyy'))
PIVOT (max(cnt) FOR year in ('2001' "2001", '2002' "2002", '2003' "2003", '2004' "2004", '2005' "2005", '2006' "2006", '2007' "2007", '2008' "2008"));

unpivot 함수

열(가로) 데이터를 행(세로) 방향으로 변경하는 함수

SELECT * 
FROM (
        SELECT * 
        FROM (SELECT to_char(hire_date, 'yyyy') year, count(*) cnt
                FROM hr.employees
                GROUP BY to_char(hire_date, 'yyyy'))
                PIVOT (max(cnt) FOR year in ('2001' "2001", '2002' "2002", '2003' "2003", '2004' "2004", '2005' "2005", '2006' "2006", '2007' "2007", '2008' "2008")))
UNPIVOT(인원수 FOR 년도 IN ("2001", "2002", "2003", "2004", "2005", "2006", "2007", "2008"));

저작자표시 비영리 변경금지 (새창열림)

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

251028 TIL (1)	2025.10.28
251027 TIL (0)	2025.10.27
251023 TIL (0)	2025.10.23
251022 TIL (0)	2025.10.22
251021 TIL (0)	2025.10.21

Joy's Devlog

Joy's Devlog

태그

최근글

댓글

공지사항

아카이브

서브쿼리

중첩 서브 쿼리(Cont.)

단일행 비교 연산자 사용시 주의점

다중행 서브 쿼리

IN(= OR)

ANY

ALL

문제

OR 진리표

AND 진리표

correlated subquery(상호 관련 서브 쿼리, 상관 서브 쿼리)

correlated subquery 수행 단계

exists 연산자

not exists 연산자

문제

INLINE VIEW

문제

pivot 함수

문제

unpivot 함수

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

관련글

티스토리툴바