[oracle] 서브 쿼리

재현·2024년 6월 14일

🔍 서브 쿼리

✍ 하나의 SQL문안에 포함되어 있는 또 다른 SQL문을 말한다. 서브쿼리를 사용하여 간단한 문장을 강력한 문장으로 만들 수 있고 테이블 자체의 데이터에 의존하는 조건으로 테이블의 행을 검색할 필요가 있을 때 서브쿼리는 아주 유용하다

🔍 서브쿼리 사용 가능한 SQL절

구간
SELECT(스칼라 서브쿼리)
FROM(인라인 뷰)
WHERE
HAVING
ORDER BY
기타 DML(INSERT, UPDATE, CREATE)

🔍 단일 행 서브 쿼리

✍ 단일 행 서브쿼리는 내부 SELECT 문장으로부터 하나의 행을 리턴하는 질의이다. 이런 유형의 서브쿼리는 단일 행 연산자를 사용한다.

SELECT SALARY FROM EMPLOYEES
WHERE FIRST_NAME = 'Nancy';

SELECT FIRST_NAME FROM EMPLOYEES
WHERE SALARY > 12008;

-- 단일 행 서브쿼리
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT SALARY
                FROM EMPLOYEES
                WHERE FIRST_NAME = 'Nancy');

SELECT FIRST_NAME, JOB_ID, HIRE_DATE
FROM EMPLOYEES
WHERE JOB_ID = (SELECT JOB_ID
                FROM EMPLOYEES
                WHERE EMPLOYEE_ID = 103);

🔍 다중 행 서브쿼리

✍ 다중 행 서브쿼리는 내부 SELECT 문장으로부터 여러 개의 행을 리턴하는 질의, 다중 행 서브쿼리는 사용하는 연산자가 단일 행과 다르다.

연산자	설명
IN	메인쿼리의 데이터가 서브쿼리의 결과 중 하나라도 일치한 데이터가 있으면 TRUE
ANY, SOME	메인쿼리의 조건식을 만족하는 서브쿼리의 결과가 하나 이상이면 TRUE
ALL	메인쿼리의 조건식을 서브쿼리의 결과 모두 만족한다면 TRUE
EXISTS	서브쿼리의 결과가 1개라도 존재하면 TRUE

✍ 그 중 ANY와 ALL의 차이점을 명시

연산자	설명
<ANY	최댓값보다 작은
>ANY	최솟값보다 큰
<ALL	최솟값보다 작은
>ALL	최댓값보다 큰
=ANY	IN과 같은 역할

-- 다중 행 서브쿼리
-- ERROR = FIRST_NAME가 'David'인 사원이 여러명이다.
SELECT FIRST_NAME, SALARY      
FROM EMPLOYEES
WHERE SALARY = (SELECT SALARY
                FROM EMPLOYEES
                WHERE FIRST_NAME = 'David');

SELECT FIRST_NAME, SALARY      
FROM EMPLOYEES
WHERE SALARY > ANY (SELECT SALARY
                FROM EMPLOYEES
                WHERE FIRST_NAME = 'David');

-- 다중 행 서브쿼리(IN)
-- 'David' 부서번호 파악(60, 80)
SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE FIRST_NAME = 'David';

-- 'David' 부서번호(60, 80)와 같은 사원들의 정보 출력
SELECT FIRST_NAME, JOB_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                        FROM EMPLOYEES
                        WHERE FIRST_NAME = 'David');
                        
-- 다중 행 서브쿼리(EXISTS)
SELECT *
FROM EMPLOYEES e, DEPARTMENTS d
WHERE d.MANAGER_ID = e.EMPLOYEE_ID;
          
SELECT FIRST_NAME, DEPARTMENT_ID, JOB_ID
FROM EMPLOYEES e
WHERE EXISTS (SELECT *
              FROM EMPLOYEES e, DEPARTMENTS d
              WHERE d.MANAGER_ID = e.EMPLOYEE_ID);

🔍 상호연관 서브쿼리

✍ 상호연관 서브쿼리는 한 개의 행을 처리할 때마다 서브쿼리로 주고 값을 처리한 후에 그 결과를 메인 쿼리로 전달하는 방식이다. 이 방식으로 인해 내부적으로 성능이 저하될 수 있는 단점이 있다.

-- 상호연관 서브쿼리
-- 본인이 속한 부서의 급여 평균보다 급여를 더 많이 받는 사람 
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES a
WHERE SALARY > (SELECT AVG(SALARY)
                FROM EMPLOYEES b
                WHERE b.DEPARTMENT_ID = a.DEPARTMENT_ID);

🔍 스칼라 서브쿼리

✍ 스칼라 서브쿼리는 SELECT 절에 사용하는 서브쿼리이고, 이용하면 다양한 결과를 도출할 수 있으며, 특히 조인을 수행할 시 조인할 행의 수를 줄여 성능을 향상시킬 수 있다.

-- 스칼라 서브쿼리(SELECT 절에 사용되는 서브쿼리)
SELECT FIRST_NAME, (SELECT DEPARTMENT_NAME
                    FROM DEPARTMENTS d
                    WHERE d.DEPARTMENT_ID = e.DEPARTMENT_ID)
FROM EMPLOYEES e
ORDER BY FIRST_NAME;

🔍 인라인 뷰 서브쿼리

✍ 인라인 뷰 서브쿼리는 FROM 절에 서브쿼리가 온 것을 말한다. 보통 FROM 절에는 테이블 또는 뷰가 올 수 있다. 그런데 서브쿼리를 FROM 절에 사용해 하나의 테이블 또는 뷰처럼 사용할 수 있을 뿐 아니라 뷰도 하나의 SELECT문이므로 FROM절에 사용하는 서브쿼리도 하나의 뷰로 볼 수 있다. 그래서 FROM절에 오는 뷰를 인라인 뷰라고 한다.

-- 인라인 뷰 서브쿼리(FROM 절에 사용되는 서브쿼리)
-- FROM의 서브쿼리 안에 있는 컬럼은 SELECT 절에 오는 컬럼명이 같아야 한다.
-- 조인과 3중 쿼리 등에 자주 사용된다.
SELECT ROW_NUMBER, FIRST_NAME, SALARY
FROM (SELECT FIRST_NAME, SALARY,
      ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROW_NUMBER
      FROM EMPLOYEES
      ORDER BY SALARY DESC)
WHERE ROW_NUMBER BETWEEN 1 AND 10;

🔍 3중 쿼리

SELECT ROWNUM, FIRST_NAME, SALARY
FROM (SELECT FIRST_NAME, SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC)
WHERE ROWNUM BETWEEN 1 AND 10; -- 무조건 1부터 시작해야 값이 출력된다.

-- 3중 쿼리
SELECT RNUM, FIRST_NAME, SALARY
FROM (SELECT FIRST_NAME, SALARY, ROWNUM AS RNUM
      FROM (SELECT FIRST_NAME, SALARY
            FROM EMPLOYEES
            ORDER BY SALARY DESC)
     )
WHERE RNUM BETWEEN 11 AND 20;

🔍 계층형 쿼리

✍ 관계형(relational)이라는 의미가 서로 평등하고 수평적인 관계를 의미하는 반면 계층형(hirearchical) 구조는 이와는 다르게 평등한 관계가 아닌 계급적이고 수직적인 관계를 가진다. 한마디로 말해서 관게형이 평면적이라 한다면 계층형은 수직적 구조라 할수있다.

SELECT 컬럼1, 컬럼2...
FROM 테이블
WHERE 조건
START WITH 최상위 조건
CONNECT BY [NOCYCLE] [PRIOR 계층형 구조 조건]

-- 계층형 쿼리
SELECT EMPLOYEE_ID,
       LPAD(' ', 3*(LEVEL-1)) || FIRST_NAME || ' ' || LAST_NAME,
       LEVEL
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER SIBLINGS BY FIRST_NAME;

SELECT EMPLOYEE_ID,
       LPAD(' ', 3*(LEVEL-1)) || FIRST_NAME || ' ' || LAST_NAME,
       LEVEL
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 113
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID;