SQL 튜터 세션 정리

SQL

프로그래머스

CTE, WITH RECURSIVE, Window 함수, JOIN 전략 등 핵심 SQL 개념 정리 (5문제)

Published

2026.04.15

SQL 튜터 세션 정리

날짜: 2026-04-15

오늘 푼 문제

#	문제	핵심 개념
1	대장균 크기 LOW/MEDIUM/HIGH 분류	CASE WHEN
2	물고기 평균 길이 필터링	CTE, COALESCE, AVG, HAVING
3	물고기 종류별 최대 크기 ID 출력	다중 CTE, MAX + INNER JOIN으로 원본 행 복원
4	부서별 평균 연봉 조회	AVG, ROUND, GROUP BY, JOIN
5	사원별 성과금 정보 조회	다중 테이블 JOIN, CASE WHEN 중첩

핵심 개념 정리

1. CTE (Common Table Expression)

WITH t1 AS (
    SELECT ...
),
t2 AS (
    SELECT ... FROM t1  -- 이전 CTE 참조 가능
)
SELECT * FROM t2;

WITH 이름 AS (쿼리) 순서 주의 (괄호가 먼저가 아님)
여러 CTE를 쉼표로 연결 가능
서브쿼리 대비 가독성이 훨씬 좋음

서브쿼리 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;

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 vs INNER JOIN 선택 기준

-- LEFT JOIN: 왼쪽 테이블 전체 유지 (매칭 없으면 NULL)
-- → "없어도 일단 다 보여줘야 할 때" (예: 자식 수 구하기)
SELECT a.ID, COALESCE(b.VALUE, 0)
FROM tableA a
LEFT JOIN tableB b ON a.ID = b.ID;

-- INNER JOIN: 양쪽 모두 존재하는 행만 반환
-- → "특정 조건을 만족하는 행만 뽑을 때" (예: 최대값 행 복원)
SELECT F.ID, F.FISH_TYPE, F.LENGTH
FROM FISH_INFO F
INNER JOIN T1 ON F.FISH_TYPE = T1.FISH_TYPE AND F.LENGTH = T1.MAX_LENGTH;

4. WHERE vs HAVING

-- WHERE: GROUP BY 이전 필터 (개별 행)
SELECT FISH_TYPE, AVG(LENGTH)
FROM FISH_INFO
WHERE LENGTH IS NOT NULL   -- 집계 전 행 필터
GROUP BY FISH_TYPE;

-- HAVING: GROUP BY 이후 필터 (그룹)
SELECT FISH_TYPE, AVG(LENGTH)
FROM FISH_INFO
GROUP BY FISH_TYPE
HAVING AVG(LENGTH) >= 33;  -- 집계 함수가 조건에 들어가면 HAVING

핵심: 집계 함수(AVG, COUNT, SUM 등)가 조건에 들어가면 무조건 HAVING

5. COALESCE

-- NULL을 다른 값으로 치환
COALESCE(LENGTH, 10)   -- LENGTH가 NULL이면 10으로 대체

-- CASE WHEN 대비 짧게 쓸 수 있음
-- 아래 두 표현은 동일
CASE WHEN LENGTH IS NULL THEN 10 ELSE LENGTH END
COALESCE(LENGTH, 10)  -- 더 간결

6. COUNT()의 NULL 처리

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

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

7. 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)

SQL에서 NOT IN 목록에 NULL이 하나라도 있으면 모든 비교가 unknown 처리됨

8. CASE WHEN 문법

CASE
    WHEN 조건1 THEN 결과1
    WHEN 조건2 THEN 결과2
    ELSE 기본값       -- THEN 없음! ELSE 뒤에 바로 값
END

주의: ELSE THEN (X) → ELSE (O), 조건 사이 쉼표 없음

9. Window 함수

-- RANK: 동점이면 같은 순위, 다음 순위 건너뜀 (1,1,3)
RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS rnk

-- DENSE_RANK: 동점이면 같은 순위, 건너뛰지 않음 (1,1,2)
DENSE_RANK() OVER (ORDER BY col DESC)

-- ROW_NUMBER: 무조건 고유 순위 (1,2,3)
ROW_NUMBER() OVER (ORDER BY col DESC)

-- NTILE(n): 전체를 n등분하여 그룹 번호 부여
NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) AS quartile

예약어 주의: rank, count 등은 MySQL 예약어 → alias로 쓸 때 rnk, cnt 등으로 대체

10. ROUND() 및 수학 함수

ROUND(숫자, N)      -- 반올림 (N: 소수점 자릿수)
ROUND(3.456, 2)    -- → 3.46
ROUND(3.456, 0)    -- → 3  (소수점 첫째 자리에서 반올림 = 정수)
ROUND(314.5, -1)   -- → 310 (음수면 정수 자리 반올림)

TRUNCATE(n, d)     -- 버림
CEIL(n)            -- 올림
FLOOR(n)           -- 내림

11. MAX 후 원본 행 복원 패턴

-- FISH_TYPE별 가장 큰 물고기 ID 구하기
WITH max_by_type AS (
    SELECT FISH_TYPE, MAX(LENGTH) AS MAX_LENGTH
    FROM FISH_INFO
    GROUP BY FISH_TYPE
)
SELECT F.ID, F.FISH_TYPE, F.LENGTH
FROM FISH_INFO F
INNER JOIN max_by_type M
    ON F.FISH_TYPE = M.FISH_TYPE
    AND F.LENGTH = M.MAX_LENGTH;

GROUP BY만으로는 ID를 보장할 수 없음 → MAX 먼저 구하고 원본과 INNER JOIN

틀렸던 포인트 & 주의사항

실수	원인	해결
`WITH (쿼리) AS t1`	CTE 문법 순서 오류	`WITH t1 AS (쿼리)`
`WHERE` 절 안에 CTE 작성	CTE는 쿼리 맨 앞에만 위치 가능	CTE를 쿼리 상단으로 이동
`NOT IN` 결과 없음	서브쿼리에 NULL 포함	`WHERE 컬럼 IS NOT NULL` 추가
`rank`를 alias로 사용	SQL 예약어 충돌	`rnk`, `cnt` 등으로 대체
`ELSE THEN "값"`	CASE WHEN 문법 오류	`ELSE "값"` (THEN 제거)
`HAVING` 대신 `WHERE` 사용	집계 후 필터링 개념 혼동	집계 함수 조건 → `HAVING`
`LEFT JOIN`으로 모든 행 포함	JOIN 유형 선택 오류	조건 만족 행만 → `INNER JOIN`
`ROUND(AVG(SAL), 1)`	소수점 자릿수 오해	정수 반올림 → `ROUND(..., 0)`

다음에 공부할 것

NOT IN vs NOT EXISTS — NULL 처리 방식 차이
Window 함수 비교: RANK / DENSE_RANK / ROW_NUMBER / NTILE
COALESCE vs NULLIF 차이와 활용
날짜 함수: DATEDIFF, DATE_FORMAT, YEAR, MONTH, DAY
문자열 함수: CONCAT, SUBSTRING, LENGTH, REPLACE, TRIM
WITH RECURSIVE 재귀 깊이 제한 및 MAXRECURSION 옵션