[SQL] SUBQUERY

21 Jul 2018

Reading time ~5 minutes

SUBQUERY(서브쿼리)

SQL문 안에 SELECT 문이 있는 쿼리를 서브쿼리라고 한다. JOIN과 다르게 서브쿼리는 메인쿼리에 종속되기 때문에 메인쿼리의 결과만 출력할 수 있다. 서브쿼리를 크게 3종류로 나누면 다음과 같다.

Single row subquery (단일행 서브쿼리)
Multiple row subquery (여러행 서브쿼리)
Multiple column subquery (다중열 서브쿼리)

Single row subquery (단일행 서브쿼리)

서브쿼리에서 리턴되는 값이 1개의 값(단일행/단일값)일 때의 쿼리
비교연산자도 단일행 비교연산자를 사용(=, >, >=, <, <=, <>)
단일 컬럼, 단일 값일 때만 사용!

SELECT job_id
FROM employees
WHERE employee_id = 141;

>ST_CLERK

SELECT *
FROM employees
WHERE job_id = 'ST_CLERK';

--두 번 수행 할 것을 하나의 문장으로 만들자

SELECT *
FROM employees
WHERE job_id = (SELECT job_id
                FROM employees
                WHERE employee_id = 141);

서브쿼리가 먼저 수행되고 메인쿼리가 수행됌
(중첩서브쿼리: 서브쿼리 절이 먼저 수행되고 그 값을 메인쿼리 절이 사용함)

Ex. 141번 사원ID를 가진 사원보다 더 많은 급여를 가지는 사원은?

SELECT *
FROM employees
WHERE salary > (SELECT salary
                FROM employees
                WHERE employee_id = 141);

SELECT *
FROM employees
WHERE salary > (SELECT salary
                FROM employees
                WHERE last_name = 'King';
                
>ORA-01427: single-row subquery returns more than one row

--비교값(King)이 여러 행이 리턴되므로 단일행으로 사용 불가

Multiple row subquery (여러행 서브쿼리)

여러행 비교연산자를 사용 (IN, NOT IN, > ANY, < ANY, = ANY, > ALL, < ALL)
단일 컬럼, 여러 행을 리턴받을 때만 사용

ANY
- OR의 범주
- > ANY는 > MIN()와 같다. (최소값보다 큼의 역할)
- < ANY는 < MAX()와 같다. (최대값보다 작음의 역할)
- = ANY는 IN 연산자와 같다.
ALL
- AND의 범주
- > ALL은 > MAX()와 같다. (최대값보다 큼의 역할)
- < ALL은 < MIN()와 같다. (최소값보다 작음의 역할)
- = ALL은 공집합(NULL)이 나옴. (의미가 없음)

ANY 예시

Ex. > ANY와 > MIN()

SELECT *
FROM employees
WHERE salary > ANY(
  SELECT salary
  FROM employees
  WHERE job_id = 'IT_PROG'
  );


SELECT *
FROM employees
WHERE salary > (
  SELECT MIN(salary)
  FROM employees
  WHERE job_id = 'IT_PROG'
  );

Ex. < ANY와 < MAX()

SELECT *
FROM employees
WHERE salary < ANY(
  SELECT salary
  FROM employees
  WHERE job_id = 'IT_PROG'
  );


SELECT *
FROM employees
WHERE salary < (
  SELECT MAX(salary)
  FROM employees
  WHERE job_id = 'IT_PROG'
  );

Ex. = ANY와 IN 연산자

SELECT *
FROM employees
WHERE salary = ANY(
  SELECT salary
  FROM employees
  WHERE job_id = 'IT_PROG'
  );


SELECT *
FROM employees
WHERE salary IN (
  SELECT salary
  FROM employees
  WHERE job_id = 'IT_PROG'
  );

ALL 예시

Ex. > ALL과 > MAX()

SELECT *
FROM employees
WHERE salary > ALL(
  SELECT salary
  FROM employees
  WHERE job_id = 'IT_PROG'
  );
  

SELECT *
FROM employees
WHERE salary > (
  SELECT MAX(salary)
  FROM employees
  WHERE job_id = 'IT_PROG'
  );

Ex. < ALL과 < MIN

SELECT *
FROM employees
WHERE salary < ALL(
  SELECT salary
  FROM employees
  WHERE job_id = 'IT_PROG'
  );


SELECT *
FROM employees
WHERE salary < (
  SELECT MIN(salary)
  FROM employees
  WHERE job_id = 'IT_PROG'
  );

Multiple column subquery (다중열 서브쿼리)

두 개 이상의 컬럼을 가지는 테이블을 쌍으로 비교하는 쿼리 방식. 아래 두 쿼리문을 비교하면서 생각해보자.

쌍비교

SELECT *
FROM employees
WHERE (manager_id, department_id) IN (
  SELECT manager_id, department_id
  FROM employees
  WHERE first_name = 'John'
  );

비쌍비교 -여러행 서브쿼리

SELECT *
FROM employees
WHERE manager_id IN (
  SELECT manager_id
  FROM employees
  WHERE first_name = 'John')
AND department_id IN (
  SELECT department_id
  FROM employees
  WHERE first_name = 'John');

John 이라는 이름을 가진 사원은 3명이 나오는데 쌍비교에서는 manager_id와 department_id를 합쳐서 3번 비교하지만 비쌍비교에서는 manager_id와 department_id를 두 값을 기준으로 각각 3번 비교하게 된다.

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

메인쿼리의 컬럼이 서브쿼리 안에 들어가 있는 경우
실행 순서는

메인쿼리 먼저 실행
Active Set 생성
첫번 째 행을 후보행으로 고정
후보행 값 선정 (아래 예시에서 department_id)
후보행 값을 서브쿼리절에 전달
후보행이랑 비교
결과 set에 저장
두번째 행을 후보행으로 고정… 쭉쭉쭉

메인쿼리의 행 수 만큼 서브쿼리절도 수행하기 때문에, 같은 직계값을 반복해서 구해서 cpu 사용량이 높아짐을 알아두자.

SELECT *
FROM employees o                          
WHERE o.salary > ALL(                     
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = o.department_id
);

EXISTS 연산자, NOT EXISTS 연산자

후보행 값이 서브쿼리절에 존재하면 더이상 검색하지 않음
존재 여부를 알고 싶을 때 사용하자!
Boolean 형식이기 때문에 컬럼을 사용하면 안됌

Ex. 관리자인 사원을 출력 

SELECT *
FROM employees o
WHERE EXISTS (  --Boolean 형식
  SELECT 'X'    --문법 오류를 방지하기위해 의미없는 'X'를 넣음
  FROM employees
  WHERE manager_id = o.employee_id
);

--employee_id가 manager_id가 같은 값을 찾으면 더이상 검색하지 않고 다음 값을 검색함

Ex. 관리자가 아닌 사원을 출력

SELECT *
FROM employees o
WHERE NOT EXISTS (
  SELECT 'X'
  FROM employees
  WHERE manager_id = o.employee_id
);

INLINE VIEW (가상 테이블)

위에서 Correlated subquery의 단점(같은 직계값을 반복해서 구함)을 확인하였다. 이를 해결하기 위해 가상 테이블을 만들어 서로 간의 테이블의 조인을 통해 문제를 해결하면 되지 않을까?

문제 66번을 가상테이블을 만들어 해결해보자.

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

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

이를 부서ID와 평균 급여가 담긴 가상의 테이블을 만들면 다음과 같다

SELECT e2.*
FROM (SELECT department_id, AVG(salary) avgsal --가상 컬럼
      FROM employees
      GROUP BY department_id) e1,              --'e1'이라는 가상테이블 
      employees e2
WHERE e1.department_id = e2.department_id
AND e1.avgsal < e2.salary;

인라인뷰에서 컬럼에 함수를 사용한다면 별칭을 만들어 사용하는 것이 좋다. (괄호가 들어가기 때문)
전체 집합을 축소시키면 좋겠다라고 생각됐을 때만 사용하는 것이 좋다.

WITH절

복합 QUERY문 안에서 동일한 SELECT문을 두 번이상 반복할 경우에 QUERY BLOCK을 만들어서 사용하면 성능이 향상된다.

용법

WITH
(가상테이블명) AS (subquery)

인라인뷰는 다시 호출할 수 없지만 with는 다시 호출할 수 있다. 아래 예시(문제 82)를 보고 이해를 해보자.

--부서의 총 급여가 전체 부서의 평균 급여보다 많은 부서의 이름과 총 급여를 표시하도록 query를 작성하세요.

SELECT d.department_name, e.sumsal
FROM (
     SELECT department_id, SUM(salary) sumsal
     FROM employees
     GROUP BY department_id
     HAVING SUM(salary) > (
      SELECT AVG(sumsal) 
      FROM (
        SELECT SUM(salary) sumsal
        FROM employees
        GROUP BY department_id
      ))) e, 
     departments d
where e.department_id=d.department_id;

직계값을 구하기 위해 같은 문장을 2번 작성했는데 WITH절을 사용하면 이를 줄일 수 있다.

WITH
dept_cost AS (
  SELECT d.department_name, SUM(salary) AS sumsal
  FROM employees e, departments d
  WHERE e.department_id = d.department_id --NULL값을 포함하려면 OUTER JOIN
  GROUP BY d.department_name
  ),
avg_cost AS (
  SELECT (sum(sumsal)/COUNT(*)) AS deptavg
  FROM dept_cost
  )
SELECT *
FROM dept_cost
WHERE sumsal > (
  SELECT deptavg
  FROM avg_cost
  );

sql, subquery sql