[KOSTA 교육 4일차] 서브쿼리

junjun·2024년 4월 16일

Multi-Row SubQuery inline view single-row subquery

KOSTA

목록 보기

4/48

서브쿼리

쿼리 안의 쿼리이다.
Main Query안에 있는 쿼리이다.
N개 이상의 쿼리가 겹쳐질 수 있다.
값을 뽑아내는 데에 사용하거나 ( 스칼라 서브쿼리 ), 인라인뷰를 추출하는데 사용할 수 있다.
복잡한 서브쿼리를 짤 때에는 가장 안쪽의 서브쿼리에서부터 Bottom-Up 방식으로 쿼리를 완성해나가는 것이 좋다.

서브쿼리의 종류

Single-Row Subquery
Multiple-Row Subquery
Multiple-Column Subquery

WHERE 절에서의 SubQuery

-- 직업이 (7369 사번의 직업)이고, 급여가 (7876 사번의 급여) 보다 많이 받는 사원
SELECT
    *
FROM
    EMP
WHERE
    JOB = (SELECT
                JOB
            FROM
                EMP
            WHERE
                EMPNO = 7369)
AND
    SAL > (SELECT
                SAL
            FROM   
                EMP
            WHERE
                EMPNO = 7876);

Single-Row SubQuery vs Multiple-Row SubQuery vs Mutiple-Column SubQuery

Single-Row SubQuery

서브쿼리의 결과가 단일 열 & 단일 행 (= 값) 인 경우

Multiple-Row SubQuery

서브쿼리의 결과가 단일 열 & 여러 행 (= 값들)

Multiple-Column SubQuery

서브쿼리의 결과가 여러 열 & 여러 행 (=테이블)인 경우

Multi-Row SubQueries

한 개 이상의 Row를 리턴

View vs Table

-- View는 가상의 테이블이다.
-- 테이블은 디스크에 저장된다.
-- 뷰는 포인터만 가지고 있다.
-- 테이블 두 개에 대한 포인터만 들고 있는다.
-- 가지고 있는 테이블은 없다, 원본 테이블에 대한 참조
-- 원본이 변경되면 뷰도 변경된다.
-- 뷰를 수정하면, 원본도 수정한다.

-- 뷰 생성 문법
CREATE OR REPLACE FORCE VIEW v_empdept as (
SELECT
    e.empno,
    e.ename,
    d.deptno,
    d.dname,
    e.job,
    d.loc
FROM
    EMP e
JOIN  
    DEPT d
ON
    e.deptno = d.deptno

-- View 사용
-- 반복적으로 자주 쓰는 테이블은 뷰로 만들어준다.
-- 편의성 및 '민감한 정보' 보여주지 않기 ( 보안 )
-- 복제 vs 뷰 : 공간 차이, 수정 시 데이터 무결성 깨짐 (레플리카)
SELECT
    *
FROM
    v_empdept
WHERE
    loc = 'DALLAS';

Inline View를 왜 사용하나?

JOIN 이 일어날 때, 좀 더 적은 튜플의 수가 나오게 하기 위해서.
미리 데이터를 전처리한다.

-- 인라인 뷰
-- 인라인 뷰를 왜 쓸까? = 성능
-- 가능한 줄이는 것. . . ( 줄어든 친구들이 카티션 곱 )
-- 테이블 크기를 줄인다. ( WITH JOIN )

-- Multi-Column SubQuery ( Inline View )
-- FROM 절에도 SubQuery를 쓸 수 있다. ( = Inline View )
SELECT
    e.*
FROM
    EMP e,
    (
        SELECT
            JOB,
            SAL
        FROM
            EMP
        WHERE
            EMPNO = 7654
    ) ee
WHERE
    e.JOB = ee.JOB
AND
    e.SAL > ee.SAL;

-- 직업이 'CLERK' 이거나 'MANAGER' 이고
-- LOC : DALLAS
-- 사원들의 사원번호, 사원명, 직업,부서명, 위치 ( INLINE VIEW 사용 )
-- 인라인 뷰를 하나만 사용했었는데, 여러 개를 쓸 수도 있다.
-- 데이터를 가져올 때부터 줄여야 한다.
SELECT
    d.deptno,
    e.ename,
    e.job,
    d.dname,
    d.loc
FROM
    (SELECT
        job,
        ename,
        deptno
    FROM
        EMP 
    WHERE
        job in ('CLERK', 'MANAGER')
    ) e
JOIN
    (SELECT
         DEPTNO,
         DNAME,
         LOC
     FROM
        DEPT
     WHERE
        LOC = 'DALLAS'
    ) d
ON
    e.deptno = d.deptno;

RDB를 쓸 정도면 빅데이터가 아니다. ( 2GB 수준 .. )
쿼리를 짠다해도, 다 똑같은 쿼리가 아니다. ( = 효율적이다, 아니다를 생각해봐야해 )
인라인 뷰를 왜 써요?
- 메모리 효율적이게 쓰려고 합니다.
카티션 프로덕트를 줄이면, 메모리 량과 속도가 다르다.

SELECT 절 서브쿼리

PIVOT 에 많이 사용한다.
SELECT절 서브쿼리
7369 사원의 이름 + 7499 사원의 직업 출력하기
-- 이런 쿼리는 어디에 쓸까? ( SELECT 서브쿼리는 어디에 쓸까? )
-- PIVOT = 길게 늘어뜨리지 말고, 옆으로 늘어지게 하기 위함.
-- 행과 열을 바꾸기 위해 사용한다. ( 행, 열 위치 바꿈 )
-- 보여지기 식으로 해야하는 경우, PIVOT 형태
-- 회원쪽, 정산쪽에서 많이 사용

-- SELECT절 서브쿼리
-- 7369 사원의 이름 + 7499 사원의 직업 출력하기
-- 이런 쿼리는 어디에 쓸까? ( SELECT 서브쿼리는 어디에 쓸까? )
-- PIVOT = 길게 늘어뜨리지 말고, 옆으로 늘어지게 하기 위함.
-- 행과 열을 바꾸기 위해 사용한다. ( 행, 열 위치 바꿈 )
-- 보여지기 식으로 해야하는 경우, PIVOT 형태
-- 회원쪽, 정산쪽에서 많이 사용
SELECT
    (SELECT
        ENAME
    FROM
        EMP
    WHERE
        EMPNO = 7369)
    as ename
    ,
    (SELECT
        JOB
     FROM
        EMP
     WHERE
        EMPNO = 7499)
    as job
FROM
    DUAL;

ON 절에 조건이 여러 개 올 수 있다.

-- ON 절에 조건 여러개 쓸 수 있다.
SELECT
    e.*
FROM
    EMP e
JOIN
    (
        SELECT
            JOB,
            DEPTNO
        FROM
            EMP
        WHERE
            EMPNO = 7499
    ) ee
ON
    e.job = ee.job
AND
    e.deptno = ee.deptno;

IN, ANY, ALL 사용 예시

-- 10번 부서 사원들과 같은 월급을 받는 사원들의 이름, 월급, 부서번호

SELECT
    ename,
    sal,
    deptno
FROM
    EMP
WHERE
    SAL = ANY(
        SELECT
            SAL
        FROM
            EMP
        WHERE
            DEPTNO = 10
    );
    
-- 10번 부서 사원들과 같은 월급을 받는 사원들의 이름, 월급, 부서번호 ( IN 사용 )
SELECT
    ename,
    sal,
    deptno
FROM
    EMP
WHERE
    SAL IN (
        SELECT
            SAL
        FROM
            EMP
        WHERE
            DEPTNO = 10
    );

튜플에 대한 조건, Multi-Row SubQuery 예시

-- 커미션을 받는 사원과 부서번호, 월급이 같은 사원의
-- 이름, 월급, 부서번호를 출력

SELECT
    ENAME,
    SAL,
    DEPTNO
FROM
    EMP e
WHERE
    (e.DEPTNO, e.SAL)
IN
    (
        SELECT
            DEPTNO,
            SAL
        FROM
            EMP
        WHERE
            COMM IS NOT NULL
        AND
            COMM != 0
    );
    
-- NVL을 사용하여 최적화
SELECT
    ENAME,
    SAL,
    DEPTNO
FROM
    EMP e
WHERE
    (e.DEPTNO, e.SAL)
IN
    (
        SELECT
            DEPTNO,
            SAL
        FROM
            EMP
        WHERE
            NVL(COMM, 0) != 0
    );
    
-- JOIN 사용
-- 위의 상태에서 DNAME도 찍어보기
SELECT
    e.ENAME,
    e.SAL,
    d.DNAME
FROM
    EMP e
JOIN
    (
        SELECT
            DEPTNO,
            DNAME
        FROM
            DEPT
    ) d
ON
    e.deptno = d.deptno
WHERE
    (e.DEPTNO, e.SAL)
IN
    (
        SELECT
            DEPTNO,
            SAL
        FROM
            EMP
        WHERE
            NVL(COMM, 0) != 0
    );

-- MGR이 'KING'인 모든 사원의 이름과 급여

SELECT
    ENAME,
    SAL
FROM
    EMP
WHERE
    MGR = (
        SELECT
            EMPNO
        FROM
            EMP
        WHERE
            ENAME = 'KING'
    );

날짜, 시간, 포맷

-- 현재 시간, 현재시간으로부터 한시간 후 시간
-- 날짜 포맷팅 By TO_CHAR
-- 날짜는 이렇게 씀.
SELECT
    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') as 현재시간,
    TO_CHAR(SYSDATE + 1/24, 'YYYY-MM-DD HH24:MI:SS') as 한시간후
FROM
    DUAL;