← 목록
SQL

NOT IN, 인라인뷰, WITH에서 걸리는 것

서브쿼리는 놓이는 자리마다 요구하는 게 다릅니다. SELECT절은 값 하나, FROM절은 집합, WHERE절은 참거짓이죠. 그 규칙만 알면 대부분은 풀립니다.

그런데 규칙을 지켜 썼는데도 결과가 어긋나는 자리가 있습니다. 이 글은 그런 지점 넷을 봅니다.

  • 인라인뷰로 집계를 붙이는 관용구, 그리고 그게 최선이 아닌 경우
  • NOT IN이 NULL 하나에 결과를 0행으로 만드는 것
  • 인라인뷰가 바깥 컬럼을 못 찾는 것
  • WITH가 “이름 붙인 인라인뷰”가 아니라는 것

여기서부터는 Oracle의 HR 샘플 스키마를 씁니다. Oracle이 연습용으로 배포하는 테이블 묶음이고, employees(사원), departments(부서), job_history(사원의 지난 직무 이력) 셋만 알면 따라오실 수 있습니다.

인라인뷰가 힘을 쓰는 자리, 집계한 결과를 다시 붙일 때

두 용어를 먼저 풀고 가겠습니다. 집계는 여러 행을 묶어 하나의 값으로 줄이는 것입니다. 합계(SUM), 평균(AVG), 개수(COUNT)처럼요. 조인은 두 테이블을 짝지어 한 줄로 잇는 것이고요. 사원 행 옆에 그 사원의 부서 이름을 붙이는 게 조인입니다.

SELECT e.last_name,
       e.salary,
       avg_sal.avg_salary
  FROM employees e
  JOIN (SELECT department_id, AVG(salary) AS avg_salary
          FROM employees
         GROUP BY department_id) avg_sal
    ON avg_sal.department_id = e.department_id;

사원 개개인의 급여와, 그 사원이 속한 부서의 평균 급여를 나란히 놓는 쿼리입니다.

왜 나눠야 할까요. GROUP BY는 행을 접어버리기 때문입니다. 부서로 묶으면 그 부서 사원들이 한 행으로 합쳐져서, 개별 사원의 이름과 급여가 사라집니다. 그런데 우리가 원하는 건 사원 한 명당 한 행이에요. 접힌 것과 안 접힌 것을 동시에 낼 수 없으니, 접는 일을 인라인뷰 안에 가두고 그 결과를 사원 행에 조인해 붙이는 겁니다.

그런데 이건 윈도우 함수가 더 직접적입니다

여기서 정직하게 짚고 가야 할 게 있습니다. 위와 같은 모양은 인라인뷰 없이도 됩니다.

SELECT last_name,
       salary,
       AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
  FROM employees;

윈도우 함수라고 부릅니다. GROUP BY처럼 묶어서 계산하되 행을 접지 않아요. PostgreSQL 공식 튜토리얼이 윈도우 함수를 소개하며 드는 첫 예제가 정확히 이 모양이고, MySQL 문서도 GROUP BY 버전과 나란히 놓고 차이를 설명합니다. Oracle에서는 8i 시절부터 있던 기능이라 새것도 아닙니다. 8i Data Warehousing Guide가 이미 AVG(...) OVER (PARTITION BY ...) 구문을 싣고 있어요.

Oracle 문서도 이점을 명시합니다. 리포팅 함수는 “별도 쿼리 블록 사이의 조인을 필요 없게 만들고”, “self-join이나 서브쿼리를 피하게 해준다“고요.

두 방식은 결과도 다릅니다. 26ai에서 직접 돌려보니 인라인뷰 버전은 3행, 윈도우 함수 버전은 4행이 나왔습니다.

차이는 부서가 없는 사원이었습니다. 인라인뷰 버전은 조인이라 department_id가 NULL인 사원이 짝을 못 찾고 결과에서 빠집니다. 윈도우 함수는 행을 지우지 않으니 그대로 남고요. 인라인뷰 쪽에서 그 사원을 살리려면 LEFT JOIN으로 바꿔야 하는데, 바꿔야 한다는 걸 알아야 바꿉니다. 조용히 빠지는 쪽이라 더 위험해요.

그럼 인라인뷰는 언제 쓰나

윈도우 함수가 늘 답이라는 뜻은 아닙니다. 집계한 값을 조건으로 걸러야 하면 다시 서브쿼리가 필요합니다.

“부서 평균보다 많이 받는 사원”을 뽑아 봅시다. WHERE salary > AVG(salary) OVER (...)라고 쓰고 싶지만 안 됩니다. WHERE는 윈도우 함수가 계산되기 전에 행을 거르는 자리거든요. 그래서 이렇게 감싸야 합니다.

SELECT * FROM (
  SELECT last_name, salary,
         AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
    FROM employees)
 WHERE salary > avg_salary;

윈도우 함수를 쓰고도 결국 인라인뷰 안에 들어갔습니다. PostgreSQL 문서도 윈도우 계산 뒤에 거르거나 묶어야 하면 서브쿼리를 쓰라고 명시합니다. 둘은 같이 쓰는 것이에요.

정리하면 이렇습니다.

  • 집계값을 그냥 붙이기만 할 거면 윈도우 함수가 짧고, 조인이 아니라 행이 빠질 걱정도 없습니다
  • 집계값으로 걸러야 하면 윈도우 함수를 쓰든 안 쓰든 인라인뷰가 필요합니다
  • 다른 테이블을 집계해서 붙일 거면 윈도우 함수로는 안 됩니다. 윈도우 함수는 지금 읽고 있는 그 테이블 안에서만 계산하니까요

흔히 어긋나는 세 가지

여기까지가 자리별 규칙입니다. 이제 그 규칙에서 자연스럽게 어긋나는 지점들을 봅시다.

NOT IN은 IN의 반대가 아닙니다

IN을 알았으니 NOT IN은 그 반대라고 읽게 됩니다. 그런데 서브쿼리 결과에 NULL이 하나라도 섞이면 다르게 동작합니다.

누구의 상사도 아닌 사원을 찾는 쿼리입니다. employees.manager_id는 그 사원의 상사를 가리키는 컬럼이니, 여기 한 번도 등장하지 않는 사원을 고르면 되겠죠.

SELECT last_name
  FROM employees
 WHERE employee_id NOT IN (SELECT manager_id FROM employees);

결과가 0행입니다. 그런 사원이 없어서가 아니라, 조건 자체가 참이 될 수 없어서요.

manager_id는 상사가 없는 사람에게는 NULL입니다. 회사에 최상단 한 명은 반드시 있으니, 이 컬럼에는 NULL이 거의 항상 섞여 있어요.

Oracle 문서는 이 동작을 명시합니다. NOT IN 뒤 목록의 항목 중 하나라도 NULL로 평가되면, 모든 행이 FALSE 또는 UNKNOWN이 되어 아무 행도 반환되지 않습니다.

왜 그런지는 NOT IN을 풀어 쓰면 보입니다. employee_id NOT IN (100, 124, NULL)은 이렇게 전개돼요.

employee_id != 100 AND employee_id != 124 AND employee_id != NULL

마지막 비교가 문제입니다. SQL에서 NULL은 “값이 없음”이지 특정 값이 아니라, != NULL은 참도 거짓도 아닌 UNKNOWN이 됩니다. AND로 묶인 조건 중 하나가 UNKNOWN이면 전체가 참이 될 수 없고, 그래서 모든 행이 걸러집니다.

반대 방향의 함정도 문서에 같이 나옵니다. 서브쿼리가 아예 0행을 돌려주면 NOT IN은 모든 행을 통과시킵니다. “비교할 게 하나도 없으니 전부 다르다”가 되는 거예요.

SELECT 'True' FROM employees
   WHERE department_id NOT IN (SELECT 0 FROM DUAL WHERE 1=2);

WHERE 1=2라 서브쿼리는 0행인데, 결과는 employees 전체 행이 나옵니다.

정리하면 NOT IN은 서브쿼리 결과가 비어 있으면 전부 통과, NULL이 섞이면 전부 차단입니다. 양 끝이 다 극단이에요.

그래서 NOT IN을 서브쿼리와 함께 쓸 때는 두 가지 선택지가 있습니다.

  • 서브쿼리에 WHERE 컬럼 IS NOT NULL을 붙인다. 간단하고 NOT IN 형태를 유지합니다. 다만 그 컬럼에 NULL이 들어올 수 있다는 걸 아는 사람만 붙일 수 있어서, 나중에 컬럼이 추가되거나 바뀌면 다시 뚫립니다.
  • NOT EXISTS로 바꾼다. NOT EXISTS는 값을 비교하는 게 아니라 행이 있는지만 보기 때문에, 위에서 본 UNKNOWN 문제가 아예 생기지 않습니다. 대신 상관 서브쿼리라 조인 조건을 직접 써야 해서 NOT IN보다 문장이 길어지고, 조인 조건을 잘못 쓰면 조용히 다른 결과가 나옵니다.

컬럼에 NOT NULL 제약이 확실히 걸려 있다면 NOT IN을 그대로 써도 됩니다. 제약을 확인할 수 없거나 나중에 바뀔 수 있는 컬럼이면 NOT EXISTS가 안전한 쪽이에요.

이건 도구가 잡아주는 실수입니다

“그래서 이게 얼마나 자주 나오는데요” 싶으실 수 있습니다. 근거가 있습니다.

정적 분석 도구 Sonar에 S3641 규칙이 있습니다. 이름이 그대로 “Nullable subqueries should not be used in NOT IN conditions“이고, Sonar는 이걸 버그(Bug)로 분류합니다. PL/SQL 판은 데이터 딕셔너리를 읽어 그 컬럼이 실제로 nullable인지까지 보고 경고합니다.

Oracle 문서도 이 동작을 설명하면서 한마디 덧붙입니다. “This behavior can easily be overlooked, especially when the NOT IN operator references a subquery.” 벤더가 직접 “놓치기 쉽다”고 적어둔 겁니다.

동작이 명세에 있다는 것과, 사람들이 실제로 걸려 넘어진다는 것은 다른 이야기입니다. 도구가 규칙으로 잡는다는 건 후자의 증거예요.

인라인뷰는 바깥 컬럼을 못 봅니다

WHERE절 서브쿼리는 바깥 컬럼을 참조할 수 있었습니다. 위 EXISTS 예제에서 안쪽이 e.employee_id를 썼던 게 그거였죠.

그래서 FROM절에서도 될 것 같습니다. 사원마다 가장 최근 직무 하나씩만 붙이고 싶다면 이렇게 쓰고 싶어져요.

SELECT e.last_name, recent.job_id
  FROM employees e,
       (SELECT h.job_id
          FROM job_history h
         WHERE h.employee_id = e.employee_id   -- 바깥의 e를 참조
         ORDER BY h.end_date DESC
         FETCH FIRST 1 ROW ONLY) recent;

안 됩니다. 실행하면 이렇게 나옵니다.

ORA-00904: "E"."EMPLOYEE_ID": invalid identifier

“그런 컬럼이 없다”입니다. 저 안에서는 e라는 이름이 아예 보이지 않거든요.

이건 자리의 성격에서 나옵니다. FROM절은 “어떤 테이블들을 놓고 시작할지”를 정하는 자리입니다. e도 recent도 아직 그 목록을 만드는 중이라, recent를 만들 시점에는 e의 특정 행이라는 게 존재하지 않아요. 반면 WHERE절은 FROM이 다 정해진 다음에 행을 하나씩 훑는 자리라 “지금 이 행의 e.employee_id“를 가리킬 수 있습니다.

그리고 여기가 버전을 타는 지점입니다.

Oracle Database 12c 릴리스 1(12.1)에서 LATERAL, CROSS APPLY, OUTER APPLY가 들어왔습니다. 신기능 문서는 LATERAL을 이렇게 설명합니다. ANSI 표준의 일부이며, 인라인뷰 문법의 확장으로 인라인뷰 안에 왼쪽 상관(left-correlation) 스코프를 제공한다고요.

즉 12.1부터는 LATERAL을 붙여 인라인뷰가 왼쪽 테이블의 컬럼을 보도록 허용할 수 있습니다.

SELECT e.last_name, recent.job_id
  FROM employees e
  CROSS APPLY (SELECT h.job_id
                 FROM job_history h
                WHERE h.employee_id = e.employee_id
                ORDER BY h.end_date DESC
                FETCH FIRST 1 ROW ONLY) recent;

CROSS APPLY와 OUTER APPLY의 차이도 문서에 명시돼 있습니다. CROSS APPLY는 오른쪽에서 결과가 나온 행만 돌려주고, OUTER APPLY는 결과가 안 나온 행도 오른쪽 컬럼을 NULL로 채워 돌려줍니다. JOIN과 LEFT OUTER JOIN의 관계와 같아요.

참고로 위 예제의 FETCH FIRST 역시 12.1에서 들어온 문법입니다. 다만 그 쿼리가 안 되는 이유는 FETCH FIRST 때문이 아니라, 인라인뷰가 e를 찾지 못하기 때문이에요. FETCH FIRST를 빼도 e를 못 찾는 건 그대로입니다.

다만 트레이드오프가 있습니다. 여기서 옵티마이저는 우리가 쓴 SQL을 보고 “실제로 어떤 순서로 읽을지”를 스스로 정하는 DB 안의 부품이고, 그렇게 정해진 계획을 실행계획이라고 합니다. 우리는 무엇을 원하는지만 쓰고, 어떻게 가져올지는 옵티마이저가 정합니다.

APPLY는 왼쪽 행마다 오른쪽을 평가하는 형태라, 표현력을 얻는 대신 옵티마이저가 고를 수 있는 실행 방식이 일반 조인보다 좁아질 수 있습니다. 얼마나 좁아지는지는 실행계획을 직접 봐야 알 수 있고, 이 글에서는 측정하지 않았습니다. 배포 대상 DB가 12.1 미만이면 애초에 문법 자체를 못 쓴다는 점도 함께 봐야 합니다.

WITH는 인라인뷰에 이름만 붙인 게 아닙니다

WITH(공통 테이블 표현식, CTE)를 인라인뷰와 나란히 놓고 “네 번째 종류”로 외우면 축이 어긋납니다. 앞의 셋은 어느 절에 놓였나로 갈렸는데, WITH는 그 축이 아니거든요.

WITH는 쿼리 블록에 이름을 붙이는 것입니다. Oracle 문서 용어로는 서브쿼리 팩토링(subquery factoring)이라고 해요.

WITH avg_sal AS (
  SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
   GROUP BY department_id
)
SELECT e.last_name, e.salary, avg_sal.avg_salary
  FROM employees e
  JOIN avg_sal ON avg_sal.department_id = e.department_id;

앞서 인라인뷰로 쓴 것과 결과가 같습니다. 다른 건 집계 블록이 FROM절 한복판에서 빠져나와 위로 올라갔다는 것, 그리고 이름이 생겨서 여러 번 참조할 수 있다는 것입니다.

여기서 “그럼 그냥 인라인뷰를 위로 옮겨 적은 것뿐인가” 싶어집니다. 문서는 그것보다 한 걸음 더 나갑니다.

The database optimizes the query by treating the query_name as either an inline view or as a temporary table.

붙인 이름을 인라인뷰로 처리할 수도, 임시 테이블로 처리할 수도 있다는 뜻입니다. 후자면 그 블록의 결과가 실제로 어딘가에 한 번 만들어져 놓입니다. 그러니 WITH는 “이름만 붙인 인라인뷰”가 아니라, 처리 방식이 옵티마이저 재량으로 갈릴 수 있는 형태예요.

무엇이 그 선택을 결정하는지는 문서가 밝히지 않습니다. 두 가지로 처리될 수 있다고만 적혀 있어요. “여러 번 참조하면 임시 테이블이 된다”는 설명을 여기저기서 보게 되는데, Oracle 제품 문서에서는 그 근거를 찾지 못했습니다.

그래서 실행계획을 떠 봤습니다. 26ai Free에서 위 쿼리처럼 avg_sal을 한 번만 참조하면 계획에 VIEW가 나오고, 같은 블록을 두 번 참조하면 이렇게 바뀝니다.

| TEMP TABLE TRANSFORMATION        |                       |
|  LOAD AS SELECT                  | SYS_TEMP_0FD9D6603... |

블록의 결과를 임시 테이블에 한 번 만들어 놓고, 그걸 두 번 읽습니다. 널리 도는 그 설명과 맞는 동작이 나온 셈이에요.

다만 이건 한 사례이지 규칙이 아닙니다. 제가 만든 작은 테이블 하나에서 나온 결과고, 문서가 기준을 밝히지 않는 이상 “참조 횟수가 기준”이라고 단정할 수 없습니다. 실제로 어떻게 도는지 알고 싶으면 여러분의 쿼리로 실행계획을 직접 뜨는 것 말고 방법이 없습니다.

관련해서 하나 더 짚습니다. 이 처리 방식을 강제한다고 알려진 MATERIALIZE와 INLINE 힌트는 19c SQL Language Reference의 힌트 목록에 없습니다. 같은 목록에 UNNEST, NO_UNNEST, PUSH_SUBQ는 있습니다. 즉 앞의 둘은 비문서화 힌트예요. 동작한다는 이야기가 많지만, 문서에 없는 것에 운영 코드를 기대는 건 다른 문제입니다.

WITH에는 인라인뷰가 흉내 낼 수 없는 기능도 있습니다. 재귀 서브쿼리 팩토링(recursive subquery factoring)이라고, WITH 블록이 자기 이름을 자기 안에서 참조하며 계층 데이터를 훑는 기능이에요. 조직도처럼 부모와 자식이 같은 테이블에 있는 데이터를 펼칠 때 씁니다.

문서는 이게 CONNECT BY보다 강력하다고 적고 있습니다. 깊이 우선과 너비 우선 탐색을 SEARCH 절로 고를 수 있고, 재귀 분기를 여러 개 둘 수 있다는 점을 이유로 듭니다. 이름을 붙였기에 자기 자신을 가리킬 수 있는 것이라, WITH가 인라인뷰와 어디서 갈라지는지 가장 분명하게 보여주는 대목이기도 합니다.

인라인뷰 대신 WITH를 쓸 때

같은 걸 두 가지로 쓸 수 있으니 기준이 필요합니다.

같은 블록을 두 번 이상 참조해야 하면 WITH입니다. 인라인뷰로는 아예 안 되거든요. 이름이 없으니 두 번째로 가리킬 방법이 없습니다. 같은 서브쿼리를 복사해 붙이면 정의가 두 벌이 되어 한쪽만 고치는 사고가 납니다.

재귀가 필요하면 WITH뿐입니다. 위에서 본 대로예요.

중첩이 깊어져 읽기 어려우면 WITH가 낫습니다. 인라인뷰는 FROM절 한복판에 블록이 박혀서, 바깥 쿼리의 뼈대가 안 보입니다. WITH로 빼면 위에서 재료를 만들고 아래에서 조립하는 순서로 읽혀요.

반대로 한 번만 쓰고 짧으면 인라인뷰가 낫습니다. 이름을 지어야 하는 부담이 없고, 그 자리에서 바로 읽힙니다. 두세 줄짜리를 굳이 위로 올리면 눈이 왔다 갔다 합니다.

그리고 WITH를 쓴다고 처리 방식까지 고르는 건 아니라는 점을 기억해 두세요. 위에서 본 것처럼 인라인뷰로 처리될 수도, 임시 테이블로 처리될 수도 있고, 그걸 강제하는 힌트는 문서에 없습니다.

스칼라 서브쿼리를 못 쓰는 자리가 따로 있습니다

SELECT절에서 값 하나로 잘 쓰이길래 “값이 필요한 곳이면 어디든 되겠구나” 싶어집니다. 문서도 “대부분의 표현식 자리에 쓸 수 있다”고 합니다. 문제는 그 “대부분”에서 빠진 자리가 하필 쓰고 싶어지는 곳이라는 겁니다.

19c 문서가 명시한 금지 자리입니다.

  • GROUP BY 절
  • CHECK 제약 조건
  • 컬럼의 기본값(default)
  • 함수 기반 인덱스의 기준
  • DML의 RETURNING 절
  • 클러스터의 해시 표현식
  • CREATE PROFILE처럼 질의와 무관한 문장

CHECK 제약이 특히 헷갈리는 자리입니다. “다른 테이블에 그 값이 있는지 검사하고 싶다”는 요구가 자연스럽게 생기는데, 거기에 스칼라 서브쿼리를 못 씁니다.

대안은 둘이고 성격이 다릅니다. 외래 키(foreign key)는 이 컬럼의 값이 저 테이블에 실제로 있는 값이어야 한다고 DB에 걸어두는 규칙입니다. 앞의 product_image에 붙인 REFERENCES product가 그거예요. 외래 키는 “그 값이 저 테이블에 있나”만 검사합니다. 그 이상은 못 해요. 조건이 붙는 검사, 예컨대 “저 테이블에서 상태가 활성인 행에만 있어야 한다”는 외래 키로 표현되지 않습니다. 그때는 트리거로 갑니다. 대신 트리거는 테이블 정의를 봐서는 안 보이는 로직이라, 나중에 데이터가 왜 거부되는지 추적하기 어려워집니다. 조건 없는 존재 검사면 외래 키로 끝내고, 트리거는 그걸로 안 될 때만 씁니다.

이 목록은 버전에 따라 줄어들었습니다

이 절을 쓰면서 걸린 게 있어 그대로 남깁니다. 같은 목록이 10g 문서에서는 더 길었습니다. 10g 문서에는 위 항목들 외에 이 셋이 더 있었어요.

  • HAVING 절
  • CASE 표현식의 WHEN 조건
  • START WITH와 CONNECT BY 절

19c 문서에는 이 셋이 없습니다. 그런데 “문서 목록에서 빠졌다”와 “이제 실제로 동작한다”는 같은 말이 아니라서, 직접 돌려봤습니다.

Docker로 띄운 Oracle AI Database 26ai Free (23.26.2.0.0)에서 확인한 결과입니다.

  • HAVING에 스칼라 서브쿼리: 동작함
  • CASE의 WHEN 조건에 스칼라 서브쿼리: 동작함
  • START WITH에 스칼라 서브쿼리: 동작함
  • GROUP BY에 스칼라 서브쿼리: 거부됨. ORA-22818: subquery expressions not allowed here

즉 10g 문서에만 있던 셋은 이 버전에서 실제로 풀렸고, 19c 문서에 남아 있는 GROUP BY는 여전히 막혀 있습니다. 문서 목록의 변화가 실제 동작의 변화와 일치했습니다.

다만 이건 26ai에서 측정한 결과입니다. 그 사이 어느 버전에서 풀렸는지, 그리고 여러분이 쓰는 버전이 어느 쪽인지는 이 결과로 알 수 없어요. 11g나 12c를 쓰신다면 거기서 다시 돌려 보셔야 합니다.

그래서 이 자리에서 기억에 의존하면 정확히 틀립니다. 10g 시절에 “HAVING에는 스칼라 서브쿼리 못 쓴다”고 배운 사람은 지금도 그렇게 말하고 다닐 거예요. 버전을 말하지 않은 SQL 제약 이야기는 절반만 맞는 말입니다.

그럼 서브쿼리를 안 쓰면 되나

여기까지 보면 이런 생각이 듭니다. 자리마다 요구가 다르고 함정도 이만큼이면, 아예 안 쓰는 길은 없나.

있습니다. 조인으로 바꾸거나, SQL을 나눠 던지고 애플리케이션에서 붙이거나, 뷰로 빼두면 됩니다. 셋 다 됩니다.

대신 각각 다른 것을 내놓습니다. 조인으로 바꾸면 행이 불어나고, 나눠서 가져오면 한 시점의 데이터라는 보장을 잃습니다. 무엇을 내주고 무엇을 얻는지는 다음 편에서 봅니다.

확인한 환경

문서로 확인한 내용은 아래 참고의 Oracle 공식 문서를 따랐습니다. 문서만으로 판단이 안 서는 것(금지 목록이 버전 사이에 줄어든 것, WITH의 처리 방식, 인라인뷰가 바깥 별칭을 못 찾을 때의 에러)은 직접 돌려서 확인했고, 그 환경은 이렇습니다.

  • Docker의 gvenzl/oracle-free:slim 이미지
  • Oracle AI Database 26ai Free Release 23.26.2.0.0
  • 몇 행짜리 테스트 테이블. 데이터 양에 따라 달라지는 것(실행계획 선택 등)은 이 규모에서 나온 결과라는 점을 감안해 주세요

참고