서브쿼리·집합 연산자·뷰

질의 안에 질의를 넣는 것이 서브쿼리입니다. 앞 노트의 조인이 두 집합을 옆으로 붙이는 연산이었다면 서브쿼리는 한 집합을 다른 집합의 조건이나 재료로 쓰는 방식이고, 집합 연산자는 두 결과를 위아래로 잇습니다. 셋 다 「같은 답을 내는 여러 길」을 만들기 때문에 시험은 어느 길이 어떤 결과와 어떤 비용을 내는가를 묻습니다.

서브쿼리가 서는 자리

서브쿼리는 적는 위치에 따라 이름과 성질이 달라집니다.

중첩 서브쿼리

WHERE 절에 들어가는 서브쿼리를 중첩 서브쿼리라 합니다. 조건을 만들기 위한 값이나 목록을 뽑아 주는 자리입니다.

SELECT 사원명, 급여
  FROM 사원
 WHERE 급여 > (SELECT AVG(급여) FROM 사원);

인라인 뷰

FROM 절에 들어가는 서브쿼리가 인라인 뷰입니다. 결과 집합이 곧 하나의 테이블처럼 쓰입니다.

SELECT d.부서번호, d.부서명, s.평균급여
  FROM 부서 d
  JOIN (SELECT 부서번호, AVG(급여) AS 평균급여
          FROM 사원 GROUP BY 부서번호) s
    ON s.부서번호 = d.부서번호;

앞 노트에서 1:M 조인이 집계를 부풀리던 문제를 푸는 방법이 이것입니다. M쪽을 먼저 집계해 1:1로 만들어 놓고 붙이면 중복이 생기지 않습니다.

스칼라 서브쿼리

SELECT 절에 들어가 값 하나를 돌려주는 서브쿼리입니다. 한 행 한 칼럼이어야 하고, 두 행 이상이면 오류가 납니다.

SELECT e.사원명,
       (SELECT d.부서명 FROM 부서 d WHERE d.부서번호 = e.부서번호) AS 부서명
  FROM 사원 e;

조회 결과가 하나도 없으면 오류가 아니라 NULL이 됩니다. 그래서 이 형태는 아우터 조인과 같은 결과를 내고, 실제로 옵티마이저가 아우터 조인으로 바꿔 푸는 경우가 많습니다. 다만 바깥 행마다 한 번씩 수행될 수 있어 바깥 건수가 크면 부담이 큽니다.

단일행과 다중행

단일행 서브쿼리

서브쿼리가 한 행만 돌려주면 =·>·<> 같은 일반 비교 연산자를 씁니다. 그런데 이 형태는 데이터에 따라 실행 중에 깨질 수 있습니다. 오늘은 한 행이 나오던 서브쿼리가 데이터가 늘어 두 행을 돌려주면 그때부터 오류가 납니다. 집계 함수로 감싸거나 다중행 연산자로 바꿔 두는 편이 안전합니다.

IN·ANY·ALL

여러 행을 돌려주는 서브쿼리에는 다중행 연산자를 씁니다.

ANY는 하나만 만족해도 참, ALL은 전부 만족해야 참이라고 읽으면 위 네 줄이 저절로 나옵니다. SOME은 ANY의 다른 이름이라 결과가 같습니다.

NULL이 섞였을 때

목록에 NULL이 있으면 IN과 NOT IN의 운명이 갈립니다.

• IN은 영향을 덜 받습니다. 다른 값과 같기만 하면 참이 되기 때문입니다.
• NOT IN은 결과가 한 행도 나오지 않습니다. x NOT IN (10, 20, NULL)은 x <> 10 AND x <> 20 AND x <> NULL로 펼쳐지는데, 마지막 항이 늘 알 수 없음이라 AND 전체가 참이 될 수 없습니다.

서브쿼리가 돌려주는 목록은 눈에 보이지 않으므로 이 사고는 조용히 일어납니다. 「어제까지 나오던 목록이 오늘 갑자기 0건」의 흔한 원인이 서브쿼리 대상 테이블에 NULL 한 행이 들어온 것입니다.

연관 서브쿼리와 EXISTS

바깥 값을 참조하는 서브쿼리

서브쿼리 안에서 바깥 질의의 칼럼을 쓰면 연관 서브쿼리가 됩니다. 바깥 행이 정해져야 안쪽을 풀 수 있으므로 바깥 행마다 한 번씩 수행되는 모양이 됩니다.

SELECT e.사원명, e.급여
  FROM 사원 e
 WHERE e.급여 > (SELECT AVG(x.급여) FROM 사원 x WHERE x.부서번호 = e.부서번호);

「자기 부서 평균보다 많이 받는 사원」처럼 기준이 행마다 다른 조건을 적을 때 씁니다.

EXISTS

EXISTS는 서브쿼리가 행을 하나라도 돌려주는지만 봅니다. 값을 쓰지 않으므로 SELECT 목록에 무엇을 적든 상관없고 관례적으로 1을 적습니다.

SELECT m.회원번호
  FROM 회원 m
 WHERE EXISTS (SELECT 1 FROM 주문 o WHERE o.회원번호 = m.회원번호);

첫 건을 찾는 순간 그 행의 판정이 끝납니다. 주문이 100건이든 1건이든 확인 비용이 같고, 조인과 달리 결과 건수가 늘지 않아 뒤에서 중복을 지울 일도 없습니다.

NOT EXISTS

NOT EXISTS는 짝이 없는 행을 찾습니다. 그리고 NOT IN과 달리 NULL에 무너지지 않습니다.

-- 주문이 한 번도 없는 회원 — 주문 테이블에 회원번호 NULL이 있어도 정상 동작
SELECT m.회원번호
  FROM 회원 m
 WHERE NOT EXISTS (SELECT 1 FROM 주문 o WHERE o.회원번호 = m.회원번호);

EXISTS는 행의 존재만 판정하므로 NULL을 다른 값과 비교하는 단계 자체가 없기 때문입니다. 「없는 것 찾기」에는 NOT EXISTS를 기본으로 삼고, NOT IN을 쓸 때는 대상 칼럼이 NOT NULL인지 반드시 확인합니다.

집합 연산자

네 연산자

집합 연산자는 두 질의의 결과를 위아래로 잇습니다.

MINUS는 오라클 이름이고 표준 SQL에서는 EXCEPT입니다.

지켜야 하는 규칙

• 두 질의의 칼럼 개수가 같고 대응하는 타입이 호환되어야 합니다.
• 결과의 칼럼 이름은 첫째 질의의 것을 씁니다.
• ORDER BY는 맨 마지막에 한 번만 적습니다. 각 질의에 따로 붙일 수 없습니다.

비용

UNION ALL을 뺀 셋은 중복을 없애야 하므로 결과 전체를 정렬하거나 해시 테이블에 담습니다. 대상이 크면 작업 영역을 넘겨 임시 공간에 썼다 읽는 일까지 생깁니다.

그래서 두 결과가 겹치지 않는 것이 확실하면 UNION ALL을 씁니다. 기간을 나눠 읽거나 서로 다른 상태의 행을 모으는 질의는 대개 겹칠 수 없는데도 습관적으로 UNION이 붙어 있는 경우가 많습니다. 튜닝에서 가장 값싸게 고칠 수 있는 자리 중 하나입니다.

뷰와 WITH 절

뷰

뷰는 이름을 붙여 저장해 둔 질의입니다. 데이터를 따로 갖지 않고, 뷰를 조회하면 그 자리에서 안쪽 질의가 펼쳐집니다.

CREATE VIEW 부서별급여 AS
SELECT 부서번호, AVG(급여) AS 평균급여, COUNT(*) AS 인원
  FROM 사원 GROUP BY 부서번호;

복잡한 질의를 감추고, 특정 칼럼만 보여 줘 접근을 제한하며, 안쪽 테이블 구조가 바뀌어도 뷰를 고치면 쓰는 쪽이 그대로일 수 있다는 것이 쓰는 이유입니다. 다만 뷰를 뷰 위에 겹쳐 쌓으면 펼쳐진 질의가 사람이 읽을 수 없을 만큼 커지고, 필요 없는 테이블까지 딸려 들어와 느려집니다.

WITH 절

WITH는 질의 앞머리에 이름 붙인 결과 집합을 정의합니다. 한 번 적어 두고 본문에서 여러 번 부를 수 있어, 같은 인라인 뷰를 두 번 쓰는 질의가 읽기 쉬워집니다.

WITH 부서집계 AS (
  SELECT 부서번호, AVG(급여) AS 평균급여 FROM 사원 GROUP BY 부서번호
)
SELECT e.사원명, e.급여, s.평균급여
  FROM 사원 e JOIN 부서집계 s ON s.부서번호 = e.부서번호
 WHERE e.급여 > s.평균급여;

뷰와 달리 그 질의 안에서만 삽니다. 여러 번 참조되는 WITH 블록을 오라클이 임시 테이블로 한 번만 만들어 재사용할지, 부를 때마다 펼칠지는 옵티마이저가 정하며 힌트로 지정할 수도 있습니다.

연습 문제

1. 부서 20의 사원 급여가 2000, 3000, 5000이고 부서 10의 사원 급여가 1000, 2500, 3500, 5000, 6000이다. 부서 10에서 다음 두 조건에 걸리는 사원 수는?
   ㉠ 급여 > ALL (SELECT 급여 FROM 사원 WHERE 부서번호 = 20)
   ㉡ 급여 > ANY (SELECT 급여 FROM 사원 WHERE 부서번호 = 20)
   ① 1명, 4명
   ② 1명, 3명
   ③ 2명, 4명
   ④ 4명, 1명

   ①. > ALL은 목록의 최댓값 5000보다 커야 하므로 6000 한 명입니다. > ANY는 최솟값 2000보다 크기만 하면 되므로 2500·3500·5000·6000 네 명입니다. 5000은 > ALL에서는 최댓값과 같아 빠지고 > ANY에서는 걸립니다.
2. 상품 테이블에 분류코드가 A, B, NULL인 행이 있다. SELECT * FROM 주문 WHERE 분류코드 NOT IN (SELECT 분류코드 FROM 상품)의 결과 건수는?
   ① 주문 전체 건수
   ② 분류코드가 A·B가 아닌 주문 건수
   ③ 0건
   ④ 오류가 난다

   ③. 서브쿼리 목록에 NULL이 섞여 있으면 NOT IN이 <> NULL 항을 포함하게 되고, 그 항은 늘 알 수 없음이라 AND 전체가 참이 될 수 없습니다. 같은 뜻을 NOT EXISTS로 적으면 의도대로 동작합니다.
3. A 질의가 1, 2, 2, 3 네 행을, B 질의가 2, 3, 4 세 행을 돌려준다. A UNION ALL B, A UNION B, A INTERSECT B, A MINUS B의 결과 건수는?
   ① 7, 4, 2, 1
   ② 7, 7, 2, 2
   ③ 4, 4, 3, 1
   ④ 7, 4, 3, 2

   ①. UNION ALL은 그대로 이어 붙여 $4 + 3 = 7$ 행입니다. UNION은 중복을 없애 1·2·3·4 네 행, INTERSECT는 양쪽에 다 있는 2·3 두 행, MINUS는 A에만 있는 1 한 행입니다.
4. 스칼라 서브쿼리에 대한 설명으로 옳은 것은?
   ① 여러 행을 돌려주면 첫 행의 값을 쓴다
   ② 조회되는 행이 없으면 오류가 난다
   ③ 조회되는 행이 없으면 NULL이 된다
   ④ FROM 절에만 쓸 수 있다

   ③. 스칼라 서브쿼리는 한 행 한 칼럼을 기대하므로 여러 행이 나오면 오류이고, 아예 없으면 NULL이 됩니다. 이 성질 때문에 아우터 조인과 같은 결과를 냅니다. FROM 절에 쓰는 것은 인라인 뷰입니다.
5. 집합 연산자를 쓴 질의에서 오류가 나는 것은?
   ① 두 질의의 칼럼 개수를 같게 맞추고 맨 뒤에 ORDER BY를 한 번 적었다
   ② 앞 질의에 ORDER BY를 적고 UNION 뒤의 질의에도 ORDER BY를 적었다
   ③ 두 질의의 칼럼 이름이 다르지만 타입이 호환된다
   ④ UNION ALL로 잇고 중복 행이 그대로 남았다

   ②. ORDER BY는 합쳐진 결과 전체에 한 번만 적용되므로 마지막에 한 번만 적을 수 있습니다. ③은 정상이며 결과의 칼럼 이름은 첫째 질의의 것을 씁니다. ④는 UNION ALL이 원래 중복을 남기는 연산자라 정상입니다.
6. 「주문이 한 번도 없는 회원」을 찾는 질의를 NOT IN 서브쿼리로 작성했더니 운영 중 어느 날부터 결과가 0건이 되었다. 원인과 고치는 방법을 서술하고, 그 방법이 왜 같은 문제를 겪지 않는지 함께 설명하시오.

   원인은 서브쿼리가 돌려주는 회원번호 목록에 NULL이 섞인 것입니다. 주문 테이블의 회원번호가 NOT NULL이 아니어서 비회원 주문 같은 행이 하나라도 들어오면 목록에 NULL이 포함되고, NOT IN은 회원번호 <> 값1 AND … AND 회원번호 <> NULL로 펼쳐지는데 마지막 항이 늘 알 수 없음이라 AND 전체가 참이 될 수 없습니다. 그 결과 조건을 통과하는 행이 하나도 없어 0건이 됩니다. 고치는 방법은 NOT EXISTS로 바꾸는 것입니다 — WHERE NOT EXISTS (SELECT 1 FROM 주문 o WHERE o.회원번호 = m.회원번호)로 적으면 됩니다. EXISTS는 서브쿼리가 행을 돌려주는지 아닌지만 판정하므로 목록을 만들어 비교하는 단계가 없고, 안쪽 조건에서 회원번호가 NULL인 주문 행은 등호 비교가 참이 되지 않아 그냥 짝이 아닌 것으로 처리될 뿐 바깥 판정을 무너뜨리지 않습니다. 임시로는 서브쿼리에 WHERE 회원번호 IS NOT NULL을 더해도 되지만, 근본적으로는 칼럼에 NOT NULL 제약을 두거나 NOT EXISTS를 기본형으로 쓰는 편이 안전합니다. 채점은 목록의 NULL을 원인으로 지목한 것에 2점, 펼쳐진 조건식으로 설명한 것에 1점, NOT EXISTS 대안에 1점, 그 방법이 NULL에 무너지지 않는 이유에 1점입니다.