SQL 필수 구문 레퍼런스 (Level 3)

1 개요

SQL Level 3은 단순 조회를 넘어 쿼리 안에서 논리적 추론을 수행하는 능력을 평가한다. 상관 서브쿼리, EXISTS, 자기 참조 JOIN 등 고급 패턴을 이해하면 복잡한 비즈니스 문제를 SQL 한 쿼리로 해결할 수 있다.

2 DS vs AIE 트랙 우선순위

구문	DS	AIE	설명
상관 서브쿼리	★★★	★★☆	외부 쿼리 참조 서브쿼리
EXISTS / NOT EXISTS	★★★	★★☆	존재 여부 확인
UNION / UNION ALL	★★★	★★☆	결과 집합 합치기
다중 조건 집계	★★★	★★☆	CASE WHEN + GROUP BY 조합
Self JOIN	★★★	★★☆	같은 테이블 자기 참조
날짜 고급 함수	★★★	★☆☆	LAST_DAY, EXTRACT
NULLIF	★★★	★☆☆	특정 값을 NULL로 변환
GROUP_CONCAT	★★★	★★☆	그룹 내 값 문자열 결합
CTE (WITH, 비재귀)	★★★	★★★	복잡 쿼리 분해
다중 컬럼 GROUP BY	★★★	★★☆	조합 집계
JOIN USING	★★☆	★★☆	동일 컬럼명 JOIN 단축
ORDER BY FIELD / CASE	★★☆	★☆☆	논리적 정렬
TIMESTAMPDIFF	★★★	★★☆	두 날짜 차이 (원하는 단위)
HAVING + 서브쿼리	★★★	★★☆	전체 평균 비교 등

3 상관 서브쿼리 (Correlated Subquery)

-- 외부 쿼리의 각 행에 대해 서브쿼리가 반복 실행된다
-- 각 직원의 급여가 자신이 속한 부서 평균보다 높은가?

SELECT name, dept, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.dept = e1.dept    -- 외부 쿼리의 e1.dept 참조
);
-- 결과: 부서 평균 이상의 직원만 반환
-- 주의: 외부 행 수만큼 서브쿼리 반복 실행 → 대용량에서 느릴 수 있음

-- 가장 최근 주문 날짜의 주문 조회 (상관 서브쿼리)
SELECT *
FROM orders o1
WHERE order_date = (
    SELECT MAX(order_date)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id    -- 고객별 최근 주문
);
-- 각 고객의 가장 최근 주문 1건씩 반환

4 EXISTS / NOT EXISTS

-- EXISTS: 서브쿼리가 결과를 반환하면 TRUE
-- IN과 유사하지만 서브쿼리 결과가 많을 때 EXISTS가 더 빠르다

-- 입양된 적 있는 동물 조회
SELECT a.NAME
FROM ANIMAL_INS a
WHERE EXISTS (
    SELECT 1
    FROM ANIMAL_OUTS o
    WHERE o.ANIMAL_ID = a.ANIMAL_ID
);
-- 결과: ANIMAL_OUTS에 매칭 행이 있는 동물만 반환

-- NOT EXISTS: 서브쿼리가 결과를 반환하지 않으면 TRUE
-- 한 번도 입양되지 않은 동물 조회
SELECT a.NAME
FROM ANIMAL_INS a
WHERE NOT EXISTS (
    SELECT 1
    FROM ANIMAL_OUTS o
    WHERE o.ANIMAL_ID = a.ANIMAL_ID
);
-- 결과: ANIMAL_OUTS에 매칭 행이 없는 동물 (LEFT JOIN WHERE IS NULL과 동일)

-- IN vs EXISTS 성능 차이
-- 서브쿼리 결과가 작으면: IN이 직관적
-- 서브쿼리 결과가 크면: EXISTS가 빠름 (첫 매칭 발견 시 바로 중단)

5 UNION / UNION ALL

-- UNION: 두 결과 집합을 합치고 중복 제거 (느림)
-- UNION ALL: 두 결과 집합을 합치고 중복 유지 (빠름)

-- 개와 고양이를 분리 조회 후 합치기
SELECT '개' AS type, NAME FROM ANIMAL_INS WHERE ANIMAL_TYPE = 'Dog'
UNION ALL
SELECT '고양이', NAME FROM ANIMAL_INS WHERE ANIMAL_TYPE = 'Cat'
ORDER BY type, NAME;
-- 결과: 개 목록 + 고양이 목록 (중복 유지)

-- 두 테이블에서 각각 조건에 맞는 행 합치기
SELECT ANIMAL_ID, 'INS_ONLY' AS status
FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS)
UNION ALL
SELECT ANIMAL_ID, 'OUTS_ONLY'
FROM ANIMAL_OUTS
WHERE ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_INS);

6 Self JOIN

-- 같은 테이블을 두 번 참조하는 JOIN
-- 예: employees(id, name, manager_id)에서 직원-상사 쌍 조회

SELECT e.name AS 직원, m.name AS 상사
FROM employees e
JOIN employees m ON e.manager_id = m.id;
-- 결과:
-- 직원  | 상사
-- ------|-----
-- Lee   | Kim   (Kim이 Lee의 상사)
-- Park  | Kim
-- Choi  | Lee

-- NULL 처리: manager_id가 NULL인 최상위 관리자 포함
SELECT e.name AS 직원, COALESCE(m.name, '최상위') AS 상사
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

7 조건부 집계 (Conditional Aggregation)

-- CASE WHEN + 집계함수 조합으로 피벗 없이 통계 생성
-- 예: 월별 완료/미완료 주문 수를 한 행으로

SELECT
    MONTH(order_date) AS 월,
    COUNT(*) AS 전체,
    SUM(CASE WHEN status = 'complete' THEN 1 ELSE 0 END) AS 완료,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS 대기중,
    AVG(CASE WHEN status = 'complete' THEN amount END) AS 완료_평균금액
FROM orders
GROUP BY 월
ORDER BY 월;
-- 결과: 월별로 완료/대기 건수와 완료 주문의 평균 금액이 한 행에

-- 부서별 성별 인원 수 (피벗)
SELECT
    dept,
    SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS 남성,
    SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS 여성
FROM employees
GROUP BY dept;

8 고급 날짜 함수

-- LAST_DAY: 해당 월의 마지막 날
SELECT LAST_DAY('2024-02-01');    -- → '2024-02-29' (2024년 윤년)
SELECT LAST_DAY('2023-02-01');    -- → '2023-02-28'

-- EXTRACT: 날짜 구성요소 추출
SELECT EXTRACT(YEAR FROM '2024-03-15');    -- → 2024
SELECT EXTRACT(QUARTER FROM '2024-03-15'); -- → 1 (1분기)
SELECT EXTRACT(WEEK FROM '2024-03-15');    -- → 11 (11번째 주)

-- DATE_FORMAT 고급
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');  -- → '2024-03-15 09:30:00'
SELECT DATE_FORMAT(NOW(), '%W, %M %d %Y');        -- → 'Friday, March 15 2024'

-- TIMESTAMPDIFF: 두 날짜 차이를 원하는 단위로
SELECT TIMESTAMPDIFF(MONTH, '2020-01-01', '2024-03-15');  -- → 50 (개월 수)
SELECT TIMESTAMPDIFF(YEAR, birth_date, NOW()) AS age FROM users;
-- 각 사용자의 만 나이 계산

9 NULLIF

-- NULLIF(a, b): a = b이면 NULL 반환, 아니면 a 반환
-- 0으로 나누기 오류 방지에 자주 사용

SELECT
    name,
    total_sales / NULLIF(visit_count, 0) AS avg_per_visit
    -- visit_count가 0이면 NULLIF가 NULL 반환 → 나누기 오류 방지
FROM sales_reps;

-- 특정 값을 NULL로 대체 (이후 COALESCE로 처리)
SELECT COALESCE(NULLIF(status, 'N/A'), '미입력') AS clean_status FROM data;
-- status가 'N/A'면 NULL로 변환 → COALESCE로 '미입력'으로 처리

10 GROUP_CONCAT — 그룹 내 값 결합

-- 그룹별 값을 쉼표 등으로 이어붙여 한 행으로 (MySQL)
SELECT dept, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS members
FROM employees
GROUP BY dept;
-- 결과:
-- dept  | members
-- ------|---------------------
-- Sales | Kim, Lee, Park
-- IT    | Choi, Jung

-- DISTINCT + 정렬 + 구분자 조합
SELECT category, GROUP_CONCAT(DISTINCT tag ORDER BY tag ASC SEPARATOR '|') AS tags
FROM articles
GROUP BY category;

-- 최대 길이 주의: group_concat_max_len 기본 1024자 (긴 경우 SET SESSION으로 확장)

• PostgreSQL은 STRING_AGG(col, ', '), Oracle은 LISTAGG(col, ', ') WITHIN GROUP (ORDER BY col)
• Programmers “우유와 요거트가 담긴 장바구니”, “조건에 맞는 사용자 정보 조회하기” 등에서 활용

11 CTE 기본 (Level 3 도입)

-- 복잡한 서브쿼리를 이름 붙여 분리 (비재귀 CTE)
WITH high_earners AS (
    SELECT dept, AVG(salary) AS avg_sal
    FROM employees
    WHERE salary > 5000
    GROUP BY dept
)
SELECT dept, avg_sal
FROM high_earners
WHERE avg_sal >= 7000;

-- 다중 CTE: 순차 참조 가능
WITH
monthly AS (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym, SUM(amount) AS total
    FROM orders GROUP BY ym
),
above_avg AS (
    SELECT ym, total FROM monthly
    WHERE total > (SELECT AVG(total) FROM monthly)
)
SELECT * FROM above_avg ORDER BY ym;

• 같은 서브쿼리를 여러 번 참조해야 할 때 중복 제거
• 재귀 CTE는 Level 5에서 다룸

12 다중 컬럼 GROUP BY

-- 여러 기준을 조합한 집계
SELECT category, year_month, COUNT(*), SUM(amount)
FROM orders
GROUP BY category, DATE_FORMAT(order_date, '%Y-%m')
ORDER BY category, year_month;

-- 주의: SELECT의 모든 비집계 컬럼은 GROUP BY에 포함되어야 한다 (표준 SQL)
-- MySQL ONLY_FULL_GROUP_BY 모드에서도 동일

13 JOIN USING / NATURAL JOIN

-- USING: 양쪽 테이블의 동일 컬럼명 JOIN 단축
SELECT i.NAME, o.DATETIME_OUT
FROM ANIMAL_INS i JOIN ANIMAL_OUTS o USING (ANIMAL_ID);
-- = ON i.ANIMAL_ID = o.ANIMAL_ID 와 동일
-- SELECT에서 ANIMAL_ID는 테이블 별칭 없이 참조해야 함

-- NATURAL JOIN: 모든 동일명 컬럼으로 자동 JOIN (권장 안 함 — 의도 불명)
-- 예상치 못한 컬럼 매칭이 발생할 수 있어 명시적 ON 또는 USING 권장

14 TIMESTAMPDIFF / DATE_ADD 심화

-- TIMESTAMPDIFF(단위, 시작, 끝): 단위별 차이
SELECT TIMESTAMPDIFF(YEAR, birth_date, NOW()) AS age FROM users;
SELECT TIMESTAMPDIFF(MONTH, start_date, end_date) AS months FROM contracts;
SELECT TIMESTAMPDIFF(HOUR, check_in, check_out) AS hours FROM bookings;

-- 단위: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

-- DATE_ADD / DATE_SUB 단위 종류
SELECT DATE_ADD('2024-01-15', INTERVAL 3 MONTH);     -- → '2024-04-15'
SELECT DATE_SUB(NOW(), INTERVAL 1 WEEK);             -- 일주일 전

-- 특정 기간 내 필터링 (일관된 패턴)
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
WHERE order_date BETWEEN '2024-01-01' AND LAST_DAY('2024-01-01')

15 HAVING + 서브쿼리

-- 전체 평균보다 평균 급여가 높은 부서
SELECT dept, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);
-- 결과: 부서 평균이 전사 평균 초과인 부서만

-- 최다 주문 고객
SELECT customer_id, COUNT(*) AS cnt
FROM orders
GROUP BY customer_id
HAVING COUNT(*) = (
    SELECT MAX(cnt) FROM (
        SELECT COUNT(*) AS cnt FROM orders GROUP BY customer_id
    ) t
);

16 ORDER BY FIELD / CASE (논리 정렬)

-- 사용자 정의 순서로 정렬
SELECT name, grade
FROM students
ORDER BY FIELD(grade, 'A', 'B', 'C', 'D', 'F');
-- = ORDER BY CASE grade WHEN 'A' THEN 1 ... END (표준 SQL 호환)

-- NULL을 맨 뒤로
ORDER BY col IS NULL, col ASC;    -- MySQL 관용
-- 또는 ORDER BY ISNULL(col), col ASC;

17 유형별 패턴 매핑 (Level 3)

문제 유형	핵심 구문	패턴
같은 테이블 비교	Self JOIN	FROM t a JOIN t b ON 조건
행 존재 여부	EXISTS/NOT EXISTS	WHERE EXISTS (SELECT 1 FROM ...)
두 쿼리 결합	UNION ALL	쿼리1 UNION ALL 쿼리2
교차 통계 (피벗)	조건부 집계	SUM(CASE WHEN ... THEN 1 ELSE 0 END)
그룹 내 특정 행	상관 서브쿼리	WHERE col = (SELECT MAX(col) WHERE ...)
0 나누기 방지	NULLIF	/ NULLIF(col, 0)
월말 날짜	LAST_DAY	WHERE date <= LAST_DAY(target)
그룹 내 값 결합	GROUP_CONCAT	GROUP_CONCAT(col ORDER BY col SEPARATOR ', ')
복잡 쿼리 분해	CTE	WITH t AS (...) SELECT ... FROM t
논리 순서 정렬	FIELD / CASE	ORDER BY FIELD(col, 'A', 'B')
기간 차이 계산	TIMESTAMPDIFF	TIMESTAMPDIFF(MONTH, a, b)
전체 평균 비교	HAVING + 서브쿼리	HAVING AVG(x) > (SELECT AVG(x) FROM t)

18 관련 문서

• SQL 필수 구문 레퍼런스 (Level 2)
• SQL 필수 구문 레퍼런스 (Level 4)