434. 서브쿼리 (Subquery)와 IN 연산자

⚠️ 이 문서는 하나의 거대한 SQL 문장 안에 또 다른 SQL 문장이 괄호로 숨어있는 '서브쿼리(Subquery)' 구조와, 그중에서도 초보 개발자들이 가장 많이 쓰지만 잘못 쓰면 테이블 풀 스캔 지옥을 만들어버리는 IN 연산자의 위험성과 튜닝 방법을 다룹니다.

핵심 인사이트 (3줄 요약)

  1. 본질: 서브쿼리는 쿼리 안의 쿼리다. 실행 결과를 바깥쪽(Main) 쿼리에 넘겨주어 복잡한 조건을 처리할 때 쓰인다.
  2. IN 연산자의 함정: WHERE 부서코드 IN (SELECT 부서코드 FROM ...) 구조를 썼을 때, 과거의 DB는 서브쿼리를 먼저 실행하지 않고 메인 쿼리를 한 줄씩 읽을 때마다 서브쿼리를 매번 다시 실행하는 바보짓을 했다.
  3. 해결책 (Semi Join): 현대의 똑똑한 옵티마이저는 IN 서브쿼리를 멍청하게 돌리지 않고, 내부적으로 **세미 조인(Semi Join)**이나 EXISTS로 자동 변환(Unnesting)하여 중복을 제거하고 초고속으로 처리해 준다.

Ⅰ. 개요: 쿼리 속의 쿼리 (Context & Necessity)

"영업팀에 속한 직원들의 이름과 급여를 뽑아줘!"

  • JOIN을 쓴다면: 직원 테이블과 부서 테이블을 엮어서 찾는다.
  • 서브쿼리를 쓴다면: "먼저 부서 테이블에서 영업팀의 부서코드를 찾아와!(서브쿼리) 그걸로 직원 테이블을 뒤져보자!(메인쿼리)"
SELECT 이름, 급여 FROM 직원 
WHERE 부서코드 IN (
    SELECT 부서코드 FROM 부서 WHERE 부서명 = '영업팀'
);

서브쿼리는 인간이 생각하는 논리적 순서와 일치하기 때문에 코드를 짜기 편하고 읽기 쉽다. 하지만 데이터베이스 엔진 입장에서는 매우 골치 아픈 구조다.

📢 섹션 요약 비유: 서브쿼리는 **'러시아 인형 마트료시카'**와 같습니다. 큰 인형(메인 쿼리)을 열면 그 안에 작은 인형(서브쿼리)이 또 들어있죠. 데이터베이스는 안에 있는 작은 인형을 먼저 열어서 힌트를 얻은 뒤, 바깥쪽 큰 인형을 해결합니다.

Ⅱ. 서브쿼리의 종류 (위치에 따라)

서브쿼리는 SQL 문의 어디에 쓰이느냐에 따라 이름과 용도가 다르다.

  1. SELECT 절 (스칼라 서브쿼리 - Scalar Subquery)
    • "딱 하나의 값(1행 1열)"만 리턴해야 한다. 메인 쿼리가 100줄을 출력하면 이 서브쿼리도 100번 돈다. (성능 주의)
  2. FROM 절 (인라인 뷰 - Inline View)
    • 서브쿼리의 결과를 마치 '가상의 테이블'처럼 취급해서 조인할 때 쓴다. (메모리에 임시 테이블이 생기므로 남발하면 안 됨)
  3. WHERE 절 (중첩 서브쿼리 - Nested Subquery)
    • IN, EXISTS, >, < 연산자와 함께 쓰여 조건을 필터링한다. (가장 흔함)

Ⅲ. IN 연산자와 세미 조인 (Semi Join) ★

WHERE ... IN (서브쿼리)는 개발자가 가장 사랑하는 문법이지만 성능의 뇌관이다.

1. IN 연산자의 과거 (멍청한 실행)

  • 옛날 옵티마이저는 메인 쿼리(직원 테이블)의 데이터 10만 건을 차례대로 읽으면서, 한 건을 읽을 때마다 서브쿼리(부서 테이블)를 실행해서 비교했다. (10만 번의 쿼리 실행)

2. 옵티마이저의 진화 (Subquery Unnesting)

  • 현대의 옵티마이저는 이 쿼리를 받으면 "어? 이거 그냥 JOIN이랑 똑같은 거잖아?"라고 판단하고, 괄호로 묶인 서브쿼리의 포장을 뜯어버린다(Unnesting).
  • 그리고 내부적으로 **세미 조인(Semi Join)**이라는 특수 기술로 변환해서 돌린다.

3. 세미 조인 (Semi Join)이란?

  • 일반 JOIN은 왼쪽과 오른쪽 데이터를 합친다.
  • 세미 조인은 합치지 않는다. "오른쪽 테이블(서브쿼리)에 이 값이 존재하는지(EXISTS) 확인만 하고, 있으면 바로 통과시키고 다음 데이터로 넘어가는" 필터링 역할만 한다.
  • 한 번 존재한다는 걸 확인하면 더 이상 오른쪽 테이블을 뒤지지 않으므로 속도가 어마어마하게 빠르다.
┌──────────────────────────────────────────────────────────────┐
│           IN 서브쿼리와 세미 조인(Semi Join)의 실행 원리 시각화          │
├──────────────────────────────────────────────────────────────┤
│                                                              │
│ [ 👨‍💼 직원 (Main) ]         [ 🏢 부서 (Subquery) ]              │
│   사번 │ 부서코드                  부서코드 │ 부서명               │
│   101 │  D1   ───(IN 검사)──▶    D1   │ 영업팀 (✔ 합격! 다음!) │
│   102 │  D1   ───(IN 검사)──▶    D2   │ 기획팀                │
│                                  D1   │ 영업팀 (또 있지만 안 봄)│
│                                                              │
│ ★ 특징: 일반 JOIN은 D1이 두 개면 데이터가 뻥튀기(2배)되지만,             │
│       Semi Join은 '존재 여부'만 묻기 때문에 중복을 무시하고 바로 넘어감!     │
└──────────────────────────────────────────────────────────────┘

Ⅳ. 결론

"서브쿼리를 쓰는 것을 두려워하지 마라. 단, 옵티마이저를 믿어라." "서브쿼리는 무조건 느리니까 무조건 JOIN으로 바꿔라"는 것은 10년 전 RDBMS를 쓰던 호랑이 담배 피우던 시절의 격언이다. 현대의 CBO(비용 기반 옵티마이저 - 420번 문서)는 여러분이 짠 IN 서브쿼리를 기가 막히게 세미 조인으로 뜯어고쳐(Unnesting) 최적의 속도를 내준다. 개발자는 억지로 조인으로 바꾸느라 SQL의 가독성을 망치기보다는, 서브쿼리 안에 걸린 조인 컬럼에 인덱스가 잘 걸려 있는지만 확실하게 챙겨주면 된다.

📌 관련 개념 맵

  • 관련 최적화: Subquery Unnesting, View Merging
  • 핵심 연산자: IN, EXISTS, ANY, ALL
  • 조인 기술: Semi Join (세미 조인), Anti Join (안티 조인 - NOT IN, NOT EXISTS)
  • 성능 주의: NOT IN을 쓸 때 서브쿼리 결과에 Null이 하나라도 섞여 있으면 메인 쿼리 결과가 통째로 0건이 나오는 치명적인 버그가 발생함. (반드시 IS NOT NULL 처리 필요)

👶 어린이를 위한 3줄 비유 설명

  1. 서브쿼리는 엄마가 "냉장고에 가서 (아빠가 사 온 아이스크림) 좀 가져와!"라고 심부름을 시키는 거예요. 괄호 안의 심부름을 먼저 알아내야 진짜 심부름을 할 수 있죠.
  2. IN 연산자는 아이스크림이 10개 있든 100개 있든 상관없이 "아이스크림 종류가 있긴 한가요?"라고 묻는 거예요.
  3. 세미 조인(Semi Join)은 냉장고 문을 열고 아이스크림을 딱 1개 발견하는 순간, 더 뒤져보지 않고 바로 "네! 있어요!" 하고 쿨하게 문을 닫아버리는 아주 빠른 검색법이랍니다!