SQL 튜터 세션 정리

날짜: 2026-04-15

오늘 푼 문제

# 문제 핵심 개념
1 대장균 개체의 자식 수 구하기 CTE, LEFT JOIN, COALESCE
2 3세대 대장균 ID 구하기 다중 CTE, INNER JOIN
3 세대별 자식 없는 개체 수 구하기 WITH RECURSIVE, NOT IN + NULL 방어
4 대장균 크기 구간별 분류 RANK() OVER(), CASE WHEN, 퍼센타일 계산

핵심 개념 정리

1. CTE (Common Table Expression)

WITH t1 AS (
    SELECT ...
),
t2 AS (
    SELECT ... FROM t1 -- 이전 CTE 참조 가능
)
SELECT * FROM t2;
  • WITH 이름 AS (쿼리) 순서 주의 (괄호가 먼저가 아님)
  • 여러 CTE를 쉼표로 연결 가능
  • 서브쿼리 대비 가독성이 훨씬 좋음

2. WITH RECURSIVE (재귀 CTE)

WITH RECURSIVE gen AS (
    -- 앵커: 시작점 (1세대)
    SELECT ID, 1 AS GENERATION
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL

    UNION ALL

    -- 재귀: 이전 결과 기반으로 다음 단계
    SELECT e.ID, gen.GENERATION + 1
    FROM ECOLI_DATA e
    INNER JOIN gen ON e.PARENT_ID = gen.ID
)
SELECT * FROM gen WHERE GENERATION = 3;
  • 계층 구조(세대, 조직도 등)를 동적으로 순회할 때 사용
  • MySQL 기본 최대 재귀 횟수: 1,000회

3. LEFT JOIN + COALESCE

SELECT a.ID, COALESCE(b.VALUE, 0)
FROM tableA a
LEFT JOIN tableB b ON a.ID = b.ID;
  • LEFT JOIN: 왼쪽 테이블의 모든 행 유지, 매칭 없으면 NULL
  • COALESCE(값, 대체값): NULL을 다른 값으로 치환

4. COUNT()의 NULL 처리

COUNT(*)        -- NULL 포함 전체 행 카운트
COUNT(컬럼명)   -- NULL 제외하고 카운트

→ LEFT JOIN 후 자식 없는 행만 세려면 COUNT(leaf.ID) 활용

5. NOT IN + NULL 함정

-- 위험: PARENT_ID에 NULL이 포함되면 결과 없음
WHERE ID NOT IN (SELECT PARENT_ID FROM ECOLI_DATA)

-- 안전: NULL 제거
WHERE ID NOT IN (SELECT PARENT_ID FROM ECOLI_DATA WHERE PARENT_ID IS NOT NULL)

6. Window 함수

-- RANK: 동점이면 같은 순위, 다음 순위 건너뜀 (1,1,3)
-- DENSE_RANK: 동점이면 같은 순위, 건너뛰지 않음 (1,1,2)
-- ROW_NUMBER: 무조건 고유 순위 (1,2,3)
-- NTILE(n): 전체를 n등분하여 그룹 번호 부여

RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS rnk
NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) AS quartile

틀렸던 포인트 & 주의사항

SQL 예약어를 alias로 사용 금지

  • rank, count 등은 MySQL 예약어 → rnk, cnt로 대체하거나 백틱(`) 사용
  • 에러 메시지: You have an error in your SQL syntax

CTE는 쿼리 맨 앞에만 위치

-- 잘못된 예
WHERE ID IN (WITH t1 AS (...) SELECT ...)

-- 올바른 예
WITH t1 AS (...)
SELECT ... WHERE ID IN (SELECT ... FROM t1)

LEFT JOIN vs INNER JOIN

  • INNER JOIN: 양쪽 테이블 모두에 존재하는 행만 반환
  • LEFT JOIN: 왼쪽 테이블 전체 유지, 없는 경우 NULL

서브쿼리 vs CTE 비교

-- 서브쿼리 (가독성 낮음)
SELECT ID FROM ECOLI_DATA
WHERE PARENT_ID IN (
    SELECT ID FROM ECOLI_DATA
    WHERE PARENT_ID IN (
        SELECT ID FROM ECOLI_DATA
        WHERE PARENT_ID IS NULL
    )
);

-- CTE (가독성 높음, 위에서 아래로 읽힘)
WITH gen1 AS (
    SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL
),
gen2 AS (
    SELECT e.ID FROM ECOLI_DATA e INNER JOIN gen1 ON e.PARENT_ID = gen1.ID
)
SELECT ID FROM ECOLI_DATA
INNER JOIN gen2 ON PARENT_ID = gen2.ID;

다음에 공부할 것