Kwangmin Kim - SQL 코딩 테스트: 기본 서브쿼리 문제 모음

1 문제 1: 완료 주문 중 최고 결제 금액 조회

1.1 문제 정보

항목	내용
출처	튜터 생성 (앵커: Programmers “가격이 제일 비싼 식품의 정보 출력하기” Lv.2, 정답률 ~65%)
난이도	Level 2
SQL 유형	WHERE + 스칼라 서브쿼리 (MAX 참조)

1.2 문제 설명

어느 온라인 쇼핑몰의 주문 이력과 고객 정보를 관리하는 데이터베이스가 있다. 운영팀에서 completed 상태로 처리된 주문 중 가장 높은 결제 금액과 동일한 결제 금액을 가진 주문들의 고객명, 주문 번호, 결제 금액, 주문 일자를 조회해 달라고 요청했다.

CREATE TABLE customers (
    customer_id   INT         PRIMARY KEY,
    customer_name VARCHAR(50),
    email         VARCHAR(100)
);

CREATE TABLE orders (
    order_id     INT  PRIMARY KEY,
    customer_id  INT,
    total_amount INT,
    order_date   DATE,
    status       VARCHAR(20)
);

샘플 데이터 (공개)

INSERT INTO customers VALUES
(101, '김민준', 'kim@example.com'),
(102, '이서연', 'lee@example.com'),
(103, '박지호', 'park@example.com'),
(104, '최수아', 'choi@example.com'),
(105, '정우진', 'jung@example.com');

INSERT INTO orders VALUES
(1, 101, 50000, '2026-01-05', 'completed'),
(2, 102, 80000, '2026-01-10', 'completed'),
(3, 103, 30000, '2026-01-15', 'cancelled'),
(4, 101, 80000, '2026-01-20', 'completed'),
(5, 104, 60000, '2026-01-25', 'completed'),
(6, 105, 45000, '2026-01-30', 'completed');

제한사항

status가 completed인 주문만 대상으로 한다
결과는 주문 일자 오름차순, 동일 일자이면 주문 번호 오름차순으로 정렬한다
결과 컬럼 순서: customer_name, order_id, total_amount, order_date

기대 결과 (공개 샘플 기준)

customer_name	order_id	total_amount	order_date
이서연	2	80000	2026-01-10
김민준	4	80000	2026-01-20

숨김 채점 데이터에 이식한 함정

NULL status — 외부 WHERE status='completed'로 자동 제외
NULL total_amount — MAX()는 NULL을 무시하고, WHERE 비교에서도 NULL은 제외
cancelled 상태의 80000원 주문 — status 필터로 제외
cancelled 상태의 90000원 주문 (핵심 함정) — 서브쿼리에 WHERE status='completed'를 누락하면 MAX=90000이 되어 결과가 빈 집합이 된다

1.3 최종 풀이

SELECT
    c.customer_name,
    o.order_id,
    o.total_amount,
    o.order_date
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
    AND o.total_amount = (
        SELECT MAX(total_amount)
        FROM orders
        WHERE status = 'completed'
    )
ORDER BY o.order_date ASC, o.order_id ASC;

각 절의 역할:

INNER JOIN ... ON c.customer_id = o.customer_id — 주문과 고객을 customer_id로 결합한다. customer_id가 양쪽에 존재하는 행만 포함한다
WHERE o.status = 'completed' — 완료 주문만 필터링한다. NULL status도 이 단계에서 제외된다
AND o.total_amount = (SELECT MAX(total_amount) FROM orders WHERE status = 'completed') — 서브쿼리 내부에도 동일한 status 조건을 넣어 완료 주문 범위 내 최댓값을 구한다. 비상관(non-correlated) 서브쿼리이므로 한 번만 실행된다
ORDER BY o.order_date ASC, o.order_id ASC — 다중 정렬 조건은 쉼표(,)로 구분한다

1.4 Tips

서브쿼리 내부 조건 일치 패턴: 외부 WHERE가 특정 조건(예: status='completed')으로 제한할 때, MAX/MIN을 구하는 서브쿼리도 반드시 같은 조건으로 범위를 제한해야 한다. 그렇지 않으면 제외되어야 할 데이터가 MAX에 포함되어 외부 쿼리 결과가 왜곡되거나 빈 집합이 된다
공동 1위(tie) 처리: ORDER BY ... DESC LIMIT 1은 동점 행을 놓친다. WHERE col = (SELECT MAX(col)...) 패턴을 쓰면 동일 최댓값을 가진 모든 행을 포착한다
비상관 스칼라 서브쿼리: 외부 쿼리의 컬럼을 참조하지 않는 서브쿼리는 옵티마이저가 한 번만 실행하여 상수로 치환한다 (Using const). 상관 서브쿼리 대비 비용이 현저히 낮다
NULL과 MAX: MAX(), MIN() 등 집계 함수는 NULL을 자동으로 무시한다. 별도의 WHERE col IS NOT NULL 필터가 필요 없다
NULL과 비교 연산: WHERE col = 값 비교에서 col이 NULL이면 결과는 UNKNOWN이므로 해당 행은 자동 제외된다. 별도 NULL 처리 코드가 불필요하다

1.5 면접 방어 포인트

실행 계획 분석: 비상관 스칼라 서브쿼리는 한 번만 실행되어 상수로 치환된다 (Using const). status 컬럼에 인덱스가 있으면 서브쿼리와 외부 WHERE 모두 Index Range Scan으로 최적화된다. 대용량 테이블에서는 (status, total_amount) 복합 인덱스가 서브쿼리·외부 WHERE를 동시에 최적화하는 핵심 전략이다. JOIN 순서는 옵티마이저가 결정하며, 보통 orders를 스캔한 후 customers를 PK로 조회하는 Nested Loop이 선택된다
대안 비교:
- Window Function (RANK() OVER): 동점 처리는 명시적이나 Lv.2 범위를 초과하며, 전체 행을 스캔 후 순위를 매기므로 연산 비용이 높다
- Self JOIN (o1.total_amount = o2.total_amount + o2가 MAX): 서브쿼리를 회피할 수 있으나 가독성이 떨어지고 Cartesian Product 위험이 있다
- ORDER BY DESC LIMIT 1: 본 문제에 치명적 — 공동 최고 금액이 여러 건일 때 tie를 놓쳐 오답이 된다. 스칼라 서브쿼리 방식이 tie-safe하다
공학적 확장:
- 대용량: orders 수천만 건 이상이면 (status, total_amount) 복합 인덱스 필수. 서브쿼리가 한 번 실행되므로 비용은 O(log n) (인덱스 있을 때)
- 동시성: 서브쿼리와 외부 쿼리 사이 INSERT가 들어오면 팬텀 리드(phantom read) 가능 — REPEATABLE READ 이상 격리 수준 권장
- 방언 호환: MySQL/PostgreSQL/Oracle 모두 동일하게 동작. 스칼라 서브쿼리는 SQL 표준
- NULL 비율이 높은 경우: status에 NULL이 많으면 인덱스 선택도가 떨어진다. 비즈니스상 status를 NOT NULL로 설계하는 것이 이상적

2 문제 2: 평균 가격 이상인 도서 조회

2.1 문제 정보

항목	내용
출처	튜터 생성 (앵커: Programmers “가격이 제일 비싼 식품의 정보 출력하기” Lv.2 변형 — MAX → AVG, WHERE 단독 → SELECT+WHERE 결합)
난이도	Level 2
SQL 유형	SELECT 절 스칼라 서브쿼리 (AVG 참조) + WHERE 조건 결합

2.2 문제 설명

어느 온라인 서점의 도서·카테고리 데이터베이스가 있다. MD팀에서 전체 도서의 평균 가격 이상인 도서 목록을 조회하되, 각 도서의 가격이 평균과 얼마나 차이 나는지 한눈에 볼 수 있도록 평균 가격 자체도 함께 결과에 포함해 달라고 요청했다.

CREATE TABLE categories (
    category_id   INT PRIMARY KEY,
    category_name VARCHAR(50)
);

CREATE TABLE books (
    book_id     INT PRIMARY KEY,
    title       VARCHAR(100),
    author      VARCHAR(50),
    category_id INT,
    price       INT
);

샘플 데이터 (공개)

INSERT INTO categories VALUES
(1, 'Programming'),
(2, 'Algorithm'),
(3, 'System');

INSERT INTO books VALUES
(1, '파이썬 입문',       '김저자', 1, 15000),
(2, '알고리즘 정복',     '이저자', 2, 28000),
(3, '데이터베이스 기초', '박저자', 1, 22000),
(4, '네트워크 이론',     '최저자', 3, 32000),
(5, '자료구조',         '정저자', 2, 18000),
(6, '운영체제',         '강저자', 3, 25000);

제한사항

평균 가격은 전체 도서 기준으로 계산한다 (카테고리가 지정되지 않은 도서 포함)
결과에는 카테고리가 지정된 도서만 포함한다
평균 가격은 정수로 반올림한다 (ROUND())
결과는 가격 내림차순, 동일 가격이면 도서 번호 오름차순으로 정렬한다
결과 컬럼 순서: book_id, title, author, category_name, price, avg_price

기대 결과 (공개 샘플 기준)

book_id	title	author	category_name	price	avg_price
4	네트워크 이론	최저자	System	32000	23333
2	알고리즘 정복	이저자	Algorithm	28000	23333
6	운영체제	강저자	System	25000	23333

숨김 채점 데이터에 이식한 함정

NULL price — AVG()가 자동으로 무시
NULL category_id — INNER JOIN이 자동으로 제외
경계값 (>= vs >) — “이상”은 >=, “초과”는 >
동일 가격 다중 행 — 2차 정렬 기준(book_id ASC) 필수

2.3 최종 풀이

SELECT
    b.book_id,
    b.title,
    b.author,
    c.category_name,
    b.price,
    (SELECT ROUND(AVG(price)) FROM books) AS avg_price
FROM books b
INNER JOIN categories c
    ON b.category_id = c.category_id
WHERE b.price >= (SELECT AVG(price) FROM books)
ORDER BY b.price DESC, b.book_id ASC;

각 절의 역할:

SELECT ... (SELECT ROUND(AVG(price)) FROM books) AS avg_price — SELECT 절 스칼라 서브쿼리. 모든 결과 행에 동일한 평균 가격을 부착한다. 비상관 서브쿼리이므로 한 번만 실행된 후 상수로 치환된다
INNER JOIN categories c ON b.category_id = c.category_id — category_id가 NULL인 도서는 매칭 실패로 자동 제외된다. 별도 IS NOT NULL 필터가 불필요하다
WHERE b.price >= (SELECT AVG(price) FROM books) — “이상”은 >=, 경계값을 포함한다. 서브쿼리에 NULL인 price는 AVG 계산에서 자동 제외된다
ROUND(AVG(price)) — 두 번째 인자 생략 시 정수 반올림이 기본값이다. ROUND(x, 0)과 동치다
ORDER BY b.price DESC, b.book_id ASC — 다중 정렬 조건은 쉼표로 구분, 각 컬럼마다 정렬 방향을 명시한다. DESC 철자 주의

2.4 Tips

경계값 조사 읽기: “이상/이하/최소/최대”는 >=/<= (경계 포함), “초과/미만”은 >/< (경계 제외). 문제 독해 시 조사에 표시하며 읽는 습관을 들인다
ROUND() 두 번째 인자: 생략 또는 0 → 정수 반올림, 1 → 소수 첫째 자리, -1 → 십의 자리. 정수 반올림에 1을 쓰는 실수 주의
INNER JOIN의 자동 NULL 제거: JOIN 조건의 양쪽 컬럼 중 어느 쪽이 NULL이어도 UNKNOWN이 되어 매칭 실패 → 결과에서 자동 제외. “JOIN 컬럼 IS NOT NULL” 필터를 추가하는 것은 중복
AVG()의 NULL-safe 성질: AVG(price) = SUM(price) / COUNT(price) (non-NULL 개수로 나눔). SUM(price) / COUNT(*)과 다르다 — 후자는 NULL을 포함해 개수를 세므로 결과가 달라진다
SELECT 절 스칼라 서브쿼리 패턴: 각 행에 전역 집계값(평균·최댓값·전체 건수 등)을 상수로 부착할 때 사용. Window Function 없이 Lv.2 범위에서 동일 효과를 낸다

2.5 면접 방어 포인트

실행 계획 분석: SELECT 절의 (SELECT ROUND(AVG(price)) FROM books)와 WHERE 절의 (SELECT AVG(price) FROM books)는 둘 다 비상관 스칼라 서브쿼리다. MySQL 옵티마이저는 각각 한 번씩만 실행하여 상수로 치환한다 (Using const). 외부 쿼리 행 수가 N이어도 서브쿼리 실행 비용은 상수(2회)다. EXPLAIN에서 DEPENDENT SUBQUERY가 아닌 SUBQUERY 또는 Using const로 나타난다. price 인덱스가 있으면 외부 WHERE는 Index Range Scan으로, category_id PK 조회는 Eq_ref로 최적화된다
대안 비교:
- CTE로 평균을 한 번만 정의: DRY 측면에서 우수하나 Lv.4 기능이라 본 범위 초과. 대규모 리팩토링이 필요할 때 고려
- Window Function AVG() OVER(): 한 번 스캔으로 모든 행에 평균 부착 가능하나 Lv.4 기능 + 전체 윈도우 연산으로 불필요한 정렬 비용 발생
- Self JOIN + 집계 서브쿼리: 서브쿼리를 회피할 수 있으나 가독성·성능 모두 열세
- SELECT 절 스칼라 서브쿼리 2회 (채택): Lv.2 범위의 표준 해법. 코드가 명료하고 옵티마이저가 자동 캐시
공학적 확장:
- 대용량: books 수천만 건 이상이면 AVG 계산 비용이 상당하다. price 인덱스는 전역 평균 계산에 직접 도움이 되지 않는다(전체 합계 필요). 프로덕션에서는 집계 뷰(summary table) 또는 물질화 뷰(materialized view)로 평균을 사전 계산하는 패턴이 표준
- 동시성: 두 서브쿼리가 각각 실행되는 사이 INSERT/UPDATE가 들어오면 평균값이 달라질 수 있다 (Non-repeatable Read). 정합성이 중요한 리포트라면 REPEATABLE READ 이상 격리 수준 또는 트랜잭션 내에서 한 번만 평균을 세션 변수로 저장해 재사용
- 정밀도: price가 정수여도 AVG()는 DECIMAL/DOUBLE을 반환한다. 비교(>=)는 자동 형 변환되나, 화면 출력 시 소수점이 붙어 보이므로 ROUND()로 정수화 필수
- 방언 호환: ROUND(x) 기본값은 MySQL/PostgreSQL 동일. Oracle도 동일하게 동작. 표준 SQL

3 문제 3: 카테고리 평균보다 비싼 상품 조회

3.1 문제 정보

항목	내용
출처	튜터 생성 (앵커: Programmers “식품분류별 가장 비싼 식품의 정보 조회하기” Lv.4 단순화 변형 — MAX → AVG, Lv.2로 하향)
난이도	Level 2
SQL 유형	FROM 절 인라인 뷰 (Derived Table) — 그룹별 집계를 파생 테이블로

3.2 문제 설명

어느 온라인 쇼핑몰의 상품·카테고리 데이터베이스가 있다. 마케팅팀에서 각 상품이 자기 카테고리의 평균 가격보다 얼마나 비싼지 확인하려 한다. 이를 위해 카테고리 평균보다 비싼 상품만 뽑고, 해당 상품의 카테고리 평균과 그 차액을 함께 조회해 달라고 요청했다.

CREATE TABLE categories (
    category_id   INT PRIMARY KEY,
    category_name VARCHAR(50)
);

CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_id  INT,
    price        INT
);

샘플 데이터 (공개)

INSERT INTO categories VALUES
(1, 'Electronics'),
(2, 'Clothing'),
(3, 'Books');

INSERT INTO products VALUES
(1, '무선 이어폰',  1, 80000),
(2, '스마트폰',    1, 900000),
(3, '티셔츠',      2, 25000),
(4, '자켓',        2, 150000),
(5, '소설책',      3, 12000),
(6, '전공 서적',   3, 45000);

제한사항

평균 가격은 같은 카테고리 내 상품 기준으로 계산한다
“평균보다 비싼”은 > (경계값 제외)
평균 가격과 차액은 정수로 반올림한다
카테고리가 지정되지 않은 상품은 결과에서 제외
결과 컬럼 순서: product_name, category_name, price, avg_price, diff
결과는 차액 내림차순, 동일 차액이면 상품 번호 오름차순으로 정렬

기대 결과 (공개 샘플 기준)

product_name	category_name	price	avg_price	diff
스마트폰	Electronics	900000	490000	410000
자켓	Clothing	150000	87500	62500
전공 서적	Books	45000	28500	16500

숨김 채점 데이터에 이식한 함정

NULL category_id — INNER JOIN으로 자동 제외
경계값 (> 사용) — 평균과 정확히 일치하는 행은 제외
추가 데이터로 카테고리 평균이 변동 — 인라인 뷰가 전체 데이터 기준으로 재계산되는지 확인
NULL price — AVG()가 자동으로 무시

3.3 최종 풀이

SELECT
    p.product_name,
    c.category_name,
    p.price,
    ROUND(ca.cat_avg) AS avg_price,
    ROUND(p.price - ca.cat_avg) AS diff
FROM products p
INNER JOIN (
    SELECT category_id, AVG(price) AS cat_avg
    FROM products
    GROUP BY category_id
) ca ON p.category_id = ca.category_id
INNER JOIN categories c ON p.category_id = c.category_id
WHERE p.price > ca.cat_avg
ORDER BY diff DESC, p.product_id ASC;

각 절의 역할:

인라인 뷰 ca: (SELECT category_id, AVG(price) AS cat_avg FROM products GROUP BY category_id) ca — 카테고리별 평균을 미리 계산한 가상 테이블. 카테고리 수만큼의 행을 가진다 (Electronics·Clothing·Books → 3행)
INNER JOIN ca ON p.category_id = ca.category_id — 각 상품 행에 자기 카테고리의 평균 가격을 부착한다. JOIN 후에는 ca.cat_avg가 행마다 적절한 평균값을 가진다
INNER JOIN categories c ON p.category_id = c.category_id — 카테고리명 부착. NULL category_id인 상품은 자동 제외
WHERE p.price > ca.cat_avg — 행 단위 비교. 경계값(=) 제외
ROUND(p.price - ca.cat_avg) AS diff — 정수 차액. WHERE에서 양수만 통과하므로 ABS() 불필요
ORDER BY diff DESC, p.product_id ASC — 차액 내림차순, 동일 시 상품 번호 오름차순

3.4 Tips

FROM 인라인 뷰 = 가상 테이블: (서브쿼리) 별칭 형태로 작성하면 그 뒤로 일반 테이블처럼 다룰 수 있다. JOIN, WHERE, ORDER BY 모두에서 컬럼 참조 가능
인라인 뷰 별칭은 필수: MySQL/PostgreSQL 모두 별칭 없으면 syntax error
별칭 스코프 차이 (핵심):

종류	외부 참조 가능?	이유
인라인 뷰 컬럼 (`ca.cat_avg`)	YES — 모든 절	가상 테이블의 진짜 컬럼, FROM에서 평가됨
SELECT 별칭 (`AS avg_price`)	NO (대부분), ORDER BY만 OK	SELECT 평가 후 부여, 같은 단계의 다른 식·WHERE에선 아직 존재하지 않음

상관 서브쿼리 vs FROM 인라인 뷰: 같은 결과지만 비용 차이가 극단적이다. 상관 서브쿼리는 외부 N행마다 서브쿼리를 N번 재실행, 인라인 뷰는 한 번 계산 후 JOIN
GROUP BY 컬럼 ↔︎ SELECT 컬럼 일치: 인라인 뷰의 SELECT 컬럼과 GROUP BY 컬럼은 일치해야 한다 (집계 함수 제외)
WHERE vs HAVING:
- WHERE: 행 단위 필터 (GROUP BY 이전, 모든 행에 대해)
- HAVING: 그룹 단위 필터 (GROUP BY 이후, 집계 결과에 대해)
- 본 문제는 행 단위 비교(p.price > ca.cat_avg)이므로 WHERE가 맞다

3.5 면접 방어 포인트

실행 계획 분석: MySQL 옵티마이저는 인라인 뷰를 (a) Materialized(임시 테이블로 한 번 계산 후 저장) 또는 (b) Merged(외부 쿼리에 풀어 통합) 방식으로 처리한다. 본 쿼리는 GROUP BY 포함이라 보통 Materialized — EXPLAIN에서 <derived2>로 표시. JOIN 순서는 옵티마이저가 결정하나, 카테고리 수가 적으므로 ca (소수 행) → categories → products 순서가 일반적. products(category_id) 인덱스가 인라인 뷰의 GROUP BY와 외부 JOIN 모두 최적화한다
대안 비교:
- 상관 서브쿼리 WHERE p.price > (SELECT AVG(price) FROM products p2 WHERE p2.category_id = p.category_id): 코드가 짧지만 각 행마다 서브쿼리 재실행. N행 × O(M) 비용 — 대용량에서 치명적
- Window Function AVG(price) OVER (PARTITION BY category_id): 한 번 스캔으로 처리 가능하나 Lv.4 기능, 본 범위 초과
- Self JOIN + GROUP BY: 가독성·성능 모두 열세
- FROM 인라인 뷰 (채택): Lv.2 범위 표준 해법. 그룹 평균이 SELECT/WHERE 양쪽에서 필요할 때 최적
공학적 확장:
- 대용량: products 수천만 건이면 인라인 뷰의 GROUP BY 비용이 핵심. (category_id) 인덱스 + (category_id, price) 복합 인덱스로 정렬·집계 동시 최적화
- 카디널리티 비대칭: 카테고리 수(M) ≪ 상품 수(N)일수록 인라인 뷰가 효율적. M개의 평균 행을 N행에 JOIN
- NULL 트리플 안전망: (1) AVG()의 NULL price 무시, (2) INNER JOIN의 NULL category_id 자동 제외, (3) GROUP BY category_id의 NULL 그룹 분리 후 외부 JOIN에서 자동 탈락
- 방언 호환: PostgreSQL/Oracle 모두 동일. PostgreSQL은 인라인 뷰를 적극적으로 inline-merge하는 경향
- CTE 리팩토링: Lv.4 이상에서는 동일 로직을 WITH ca AS (...)로 표현 가능. 동일 뷰를 여러 번 참조해야 하면 CTE가 가독성·재사용성 우수

4 문제 4: 한 번도 대출되지 않은 도서 조회

4.1 문제 정보

항목	내용
출처	튜터 생성 (앵커: Programmers “보호소에서 중성화한 동물” Lv.4 NOT IN 패턴 단순화 — Lv.2로 하향)
난이도	Level 2
SQL 유형	NOT IN + 서브쿼리 (제외 패턴 / Anti-Join) — NULL 함정 포함

4.2 문제 설명

어느 도서관 시스템의 도서·대출 데이터베이스가 있다. 사서팀에서 단 한 번도 대출된 적이 없는 도서 목록을 조회해 달라고 요청했다. 폐기 또는 홍보 대상을 선정하는 기초 자료로 쓸 예정이다.

CREATE TABLE books (
    book_id        INT PRIMARY KEY,
    title          VARCHAR(100),
    author         VARCHAR(50),
    genre          VARCHAR(50),
    published_year INT
);

CREATE TABLE loans (
    loan_id     INT PRIMARY KEY,
    book_id     INT,
    member_id   INT,
    loan_date   DATE
);

샘플 데이터 (공개)

INSERT INTO books VALUES
(1, '파이썬 입문',       '김저자', 'Programming', 2020),
(2, '알고리즘 정복',     '이저자', 'Programming', 2021),
(3, '데이터베이스 기초', '박저자', 'Database',    2019),
(4, '자료구조',         '정저자', 'Programming', 2022),
(5, '운영체제',         '강저자', 'System',      2018),
(6, '네트워크 이론',     '최저자', 'System',      2023);

INSERT INTO loans VALUES
(1, 2, 101, '2026-01-05'),
(2, 3, 102, '2026-01-10'),
(3, 5, 103, '2026-01-15'),
(4, 2, 104, '2026-02-01');

제한사항

결과 컬럼 순서: book_id, title, author, published_year
대출 기록이 단 한 건이라도 있으면 결과에서 제외
결과는 출간 연도 내림차순, 동일 시 도서 번호 오름차순 정렬
채점 데이터에는 loans.book_id에 NULL 행이 의도적으로 포함됨 (NOT IN NULL 함정)

기대 결과 (공개 샘플 기준)

book_id	title	author	published_year
6	네트워크 이론	최저자	2023
4	자료구조	정저자	2022
1	파이썬 입문	김저자	2020

4.3 최종 풀이

SELECT
    book_id,
    title,
    author,
    published_year
FROM books
WHERE book_id NOT IN (
    SELECT book_id
    FROM loans
    WHERE book_id IS NOT NULL
)
ORDER BY published_year DESC, book_id ASC;

각 절의 역할:

서브쿼리 (SELECT book_id FROM loans WHERE book_id IS NOT NULL) — 대출 이력이 있는 도서 ID 집합을 반환. WHERE book_id IS NOT NULL이 핵심 — NOT IN의 NULL 함정 차단
WHERE book_id NOT IN (...) — 대출 이력 집합에 속하지 않는 도서만 외부에서 선택 (anti-join 효과)
DISTINCT는 정확성에 영향 없으므로 생략 — NOT IN은 중복에 무관

4.4 Tips

NOT IN의 3값 논리 NULL 함정 (악명 높음):
- x NOT IN (1, 2, NULL)은 x != 1 AND x != 2 AND x != NULL로 전개
- x != NULL은 항상 UNKNOWN
- AND에 UNKNOWN이 섞이면 전체 UNKNOWN → FALSE 처리
- 결과: 서브쿼리에 NULL이 단 하나라도 있으면 외부 모든 행이 탈락 → 빈 결과
방어 전략 3가지:
1. 서브쿼리에서 명시적 NULL 제외: WHERE col IS NOT NULL (이번 채택)
2. NOT EXISTS로 대체: 자동 NULL-safe, 의도 명확
3. LEFT JOIN + IS NULL: anti-join 표준 패턴, 대용량에서 가장 빠른 경향
NOT EXISTS가 실전 표준: 운영 코드에서는 NOT IN보다 NOT EXISTS가 권장 (NULL-safe + 옵티마이저 친화)
방언 차이: UNIQUE는 Oracle의 DISTINCT 동의어. MySQL은 UNIQUE를 인덱스 제약 키워드로만 인식 → SELECT에서 사용 시 syntax error
DISTINCT(col) 표기: 함수가 아니라 DISTINCT (col) — 괄호는 식 그룹화. 표기상 혼란만 주므로 괄호 없이 권장

4.5 면접 방어 포인트

실행 계획 분석: MySQL은 NOT IN을 일반적으로 Materialized Subquery로 처리한다 — 서브쿼리 결과를 임시 테이블/해시로 만든 후 외부 행마다 lookup. EXPLAIN에서 <subquery> 또는 MATERIALIZED로 표시. 옵티마이저는 NOT IN의 NULL 가능성을 알고 있어, 컬럼이 NOT NULL 선언이면 NULL 검사를 생략하여 더 효율적. NULLable 컬럼이면 추가 NULL 분기 비용 발생. loans(book_id) 인덱스로 서브쿼리 비용 감소

대안 비교:

접근	성능	NULL-safe	가독성
NOT IN + IS NOT NULL (채택)	중	명시적 처리 필요	명료
NOT EXISTS	중~상	자동 NULL-safe	SQL 표준, 의도 명확
LEFT JOIN + IS NULL	상 (대용량)	NULL-safe	다소 우회적
EXCEPT/MINUS	상	NULL-safe	가장 명료 (MySQL 미지원)

NOT EXISTS 예시:

SELECT b.book_id, b.title, b.author, b.published_year
FROM books b
WHERE NOT EXISTS (
    SELECT 1 FROM loans l
    WHERE l.book_id = b.book_id
);

LEFT JOIN 예시:

SELECT b.book_id, b.title, b.author, b.published_year
FROM books b
LEFT JOIN loans l ON b.book_id = l.book_id
WHERE l.book_id IS NULL;

공학적 확장:
- 대용량: books 100만 + loans 1억 규모면 NOT IN/NOT EXISTS는 인덱스 lookup, LEFT JOIN은 hash join이 일반적. 옵티마이저 통계가 정확하면 비슷한 성능
- 방언 호환: NOT IN/NOT EXISTS는 모든 주요 DBMS 표준. EXCEPT/MINUS는 MySQL 8.0 미지원
- 운영 패턴: “한 번도 사용/주문/방문하지 않은 X” 유형은 마케팅·재고 관리에서 빈번. 매번 쿼리하지 말고 활동 플래그 컬럼 유지 (is_ever_used BOOL) 패턴이 운영상 유리
- 인덱스 선택도: loans.book_id 카디널리티가 높을수록 인덱스 효과 큼. 1%만 대출되는 sparse 패턴이면 NOT IN/NOT EXISTS 모두 빠름

5 문제 5: 5점 평가를 준 적 있는 회원 조회

5.1 문제 정보

항목	내용
출처	튜터 생성 (앵커: Programmers “재구매가 일어난 상품과 회원 리스트 구하기” Lv.2 ~50% IN 패턴 변형 — GROUP BY+HAVING 대신 단순 IN 서브쿼리)
난이도	Level 2
SQL 유형	IN + 서브쿼리 (하위 집합 필터) — NOT IN과의 NULL 비대칭성 학습

5.2 문제 설명

어느 영화 스트리밍 플랫폼의 회원·시청 데이터베이스가 있다. 마케팅팀에서 5점 만점 평가를 단 한 번이라도 준 적이 있는 회원들의 정보를 추출해 달라고 요청했다.

CREATE TABLE members (
    member_id   INT PRIMARY KEY,
    name        VARCHAR(50),
    signup_date DATE,
    grade       VARCHAR(20)
);

CREATE TABLE movie_views (
    view_id     INT PRIMARY KEY,
    member_id   INT,
    movie_title VARCHAR(100),
    view_date   DATE,
    rating      INT
);

샘플 데이터 (공개)

INSERT INTO members VALUES
(1, '김민준', '2025-06-01', 'GOLD'),
(2, '이서연', '2025-08-15', 'SILVER'),
(3, '박지호', '2025-10-20', 'BRONZE'),
(4, '최수아', '2026-01-05', 'GOLD'),
(5, '정우진', '2026-02-10', 'SILVER'),
(6, '강도윤', '2026-03-15', 'BRONZE');

INSERT INTO movie_views VALUES
(1, 1, '인터스텔라',   '2025-12-01', 5),
(2, 2, '매트릭스',     '2025-12-15', 4),
(3, 3, '인셉션',       '2026-01-10', 3),
(4, 4, '어벤져스',     '2026-01-20', 5),
(5, 5, '파이트클럽',   '2026-02-25', 4),
(6, 1, '쇼생크탈출',   '2026-03-01', 5);

제한사항

결과 컬럼 순서: member_id, name, signup_date, grade
5점 평가가 단 한 건이라도 있으면 결과에 포함 (회원당 한 번만 표시)
members에 존재하지 않는 member_id는 자연 제외
결과는 가입일 내림차순, 동일 시 회원 번호 오름차순 정렬

기대 결과 (공개 샘플 기준)

member_id	name	signup_date	grade
4	최수아	2026-01-05	GOLD
1	김민준	2025-06-01	GOLD

숨김 채점 데이터에 이식한 함정

NULL member_id (IN이 자동 무시 — NOT IN과 정반대)
NULL rating (rating = 5에서 자연 제외)
members에 없는 member_id 99 (IN 매칭에서 자연 제외)
추가 5점 평가로 결과 행 증가

5.3 최종 풀이

SELECT
    member_id,
    name,
    signup_date,
    grade
FROM members
WHERE member_id IN (
    SELECT member_id
    FROM movie_views
    WHERE rating = 5
)
ORDER BY signup_date DESC, member_id ASC;

각 절의 역할:

서브쿼리 (SELECT member_id FROM movie_views WHERE rating = 5) — 5점 평가를 매긴 회원 ID 집합. NULL rating은 rating = 5에서 자동 제외 (NULL 비교는 UNKNOWN)
WHERE member_id IN (...) — 그 집합에 속하는 회원만 선택. IN은 NULL-safe — 서브쿼리 결과에 NULL이 있어도 다른 값과는 정상 매칭, NULL은 그냥 매치 안 됨
추가 NULL 방어 불필요 — members.member_id는 PRIMARY KEY (NOT NULL), 서브쿼리 NULL은 IN이 자동 무시
DISTINCT 불필요 — IN/NOT IN 모두 서브쿼리 중복은 정확성에 무관

5.4 Tips

NOT IN과 IN의 NULL 처리 비대칭성 (핵심):

연산자	서브쿼리에 NULL 있을 때	외부 컬럼이 NULL일 때
NOT IN	결과 전체 empty (3값 논리 함정)	UNKNOWN → 제외
IN	자동 무시 (NULL은 매칭 안 됨, 다른 값과 정상 비교)	UNKNOWN → 제외

왜 비대칭인가: NOT IN은 부등식 AND 전개 → UNKNOWN이 AND를 무너뜨림. IN은 등식 OR 전개 → 다른 등식 중 하나만 TRUE면 전체 TRUE 유지
패턴 일반화 주의: 한 패턴(NOT IN 방어)을 무조건 다른 곳(IN)에 옮기지 말 것. 각 연산자의 의미론을 정확히 이해하고 필요한 곳에만 적용
PK에 IS NOT NULL은 무의미: PRIMARY KEY는 NOT NULL 자동 부여 → 추가 검사 항상 TRUE
IN vs INNER JOIN: 1:N 관계에서 INNER JOIN은 중복 행을 만든다. IN은 외부 행당 매칭 1회 (Semi-Join 효과로 자동 중복 제거). 회원 정보 한 번씩 조회에는 IN이 안전

5.5 면접 방어 포인트

실행 계획 분석: MySQL 옵티마이저는 IN 서브쿼리를 Semi-Join으로 변환하여 처리한다 (EXPLAIN에서 Using semi-join). INNER JOIN과 비슷하나 외부 행당 매칭 1회로 끝나 중복 제거 효과 자동. movie_views(member_id, rating) 복합 인덱스가 있으면 서브쿼리는 Index Range Scan. INNER JOIN + DISTINCT 패턴 대비 명료하고 효율적
대안 비교:
- EXISTS: 의미 동일. NULL-safe(IN도 자연 safe라 본 문제는 무관). 옵티마이저 친화도 비슷. 서브쿼리에 외부 컬럼 참조가 자연스러움
- INNER JOIN + DISTINCT: 중복 행 발생 → DISTINCT 필수, 비효율
- INNER JOIN (DISTINCT 없이): 회원이 5점 평가 횟수만큼 중복 출현 → 잘못된 결과
- IN 서브쿼리 (채택): Lv.2 표준 해법, 1:N 관계의 안전한 필터링
공학적 확장:
- 대용량: movie_views(member_id, rating) 복합 인덱스 필수. 평점 조건이 자주 바뀌면 부분 인덱스(WHERE rating = 5)도 옵션 (PostgreSQL 한정)
- 카디널리티 영향: 5점 평가 비율이 낮을수록 IN 서브쿼리가 빠름 (서브쿼리 결과 작음)
- 방언 호환: IN 서브쿼리는 SQL 표준. 모든 주요 DBMS 동일 동작
- 활동 기반 마케팅 패턴: “특정 행동을 한 적 있는 사용자” 추출은 마케팅·CRM 핵심 패턴. 자주 조회하면 활동 플래그 컬럼이나 매트뷰로 사전 계산

6 문제 6: 리뷰가 없는 도서 찾기 (LEFT JOIN + IS NULL)

6.1 문제 정보

항목	내용
출처	튜터 생성 (앵커: Programmers “없어진 기록 찾기” Lv.3, 정답률 ~72%)
난이도	Level 2
SQL 유형	LEFT JOIN + IS NULL (Anti Join 변형) — 제외 키 선택 함정

6.2 문제 설명

어느 독립 서점의 관리자가 재고 정리를 앞두고 있다. 지금까지 단 한 건의 고객 리뷰도 받지 못한 도서를 추려 프로모션 대상을 선정하려 한다.

CREATE TABLE BOOKS (
    BOOK_ID        INT PRIMARY KEY,
    TITLE          VARCHAR(100) NOT NULL,
    AUTHOR         VARCHAR(50),
    PUBLISHED_YEAR INT,
    PRICE          INT
);

CREATE TABLE REVIEWS (
    REVIEW_ID   INT PRIMARY KEY,
    BOOK_ID     INT,
    RATING      INT,
    REVIEW_TEXT VARCHAR(200),
    CREATED_AT  DATE
);

샘플 데이터 (공개)

INSERT INTO BOOKS VALUES
(1, 'The Silent Coder',  'A. Kim',  2021, 18000),
(2, 'Data Forest',       'B. Lee',  2019, 22000),
(3, 'SQL in Practice',   'C. Park', 2022, 25000),
(4, 'Hidden Patterns',   'D. Choi', 2020, 20000),
(5, 'Null Stories',      'E. Han',  2023, 17000),
(6, 'Edge Cases',        'F. Jung', 2018, 15000);

INSERT INTO REVIEWS VALUES
(101, 1, 5, 'Great book',        '2026-01-10'),
(102, 3, 4, 'Useful',             '2026-02-02'),
(103, 3, 3, 'Average',            '2026-02-18'),
(104, 6, 5, 'Loved the examples', '2026-03-01');

제한사항

리뷰가 한 건도 존재하지 않는 도서의 BOOK_ID, TITLE, AUTHOR를 조회한다.
정렬: BOOK_ID 오름차순.
REVIEWS.BOOK_ID는 NULL 허용 (익명 리뷰 존재 가능).
REVIEWS.REVIEW_TEXT는 NULL 허용 (별점만 남긴 리뷰 존재 가능).

기대 결과 (공개 샘플 기준)

BOOK_ID	TITLE	AUTHOR
2	Data Forest	B. Lee
4	Hidden Patterns	D. Choi
5	Null Stories	E. Han

6.3 최종 풀이

SELECT b.book_id, b.title, b.author
FROM books b
LEFT JOIN reviews r ON b.book_id = r.book_id
WHERE r.review_id IS NULL
ORDER BY b.book_id;

LEFT JOIN reviews r ON b.book_id = r.book_id: 리뷰가 없는 책은 모든 r.*이 NULL인 행 1건으로 남는다
WHERE r.review_id IS NULL: 제외 키로 PK(NOT NULL 보장 컬럼) 를 선택 — 매칭 실패 행만 정확히 추출
ORDER BY b.book_id: 조회 안정성을 위한 정렬

6.4 Tips

제외 키 선택 원칙 (핵심): LEFT JOIN 후 “매칭되지 않은 왼쪽 행”을 추출할 때 WHERE 조건의 키는 NULL이 될 수 있는 경우가 ‘매칭 실패’ 하나뿐인 컬럼이어야 한다

우선순위	컬럼	이유
1순위	오른쪽 테이블 PK (예: `r.review_id`)	행 존재 시 절대 NULL 아님 → 매칭 실패만 정확히 식별
2순위	조인 키 (`r.book_id`)	매칭 성공 시 NOT NULL 보장
회피	내용 컬럼 (`r.review_text`, `r.rating` 등 NULL 허용)	원본 NULL과 매칭 실패 NULL 구분 불가

내용 컬럼을 제외 키로 쓸 때 생기는 오답: WHERE r.review_text IS NULL로 걸면 “별점만 남긴 리뷰(review_text NULL)”가 있는 책도 “리뷰 없음”으로 잘못 분류된다. 그 책은 실제로 리뷰가 있다
제외 패턴 3가지 비교:
- LEFT JOIN + IS NULL (채택): 옵티마이저가 Anti Semi Join으로 변환, 인덱스 활용 가능
- NOT EXISTS: 대부분 DBMS가 Anti Semi Join, early termination 가능, NULL 안전
- NOT IN: NULL 방어 누락 시 결과 전체 empty (3값 논리 함정). 방어 가능할 때만 선택
중복 행 주의: 매칭된 책이 여러 리뷰를 가질 경우 LEFT JOIN 결과는 그만큼 행이 불어나지만, IS NULL 필터가 매칭 행을 모두 버리므로 결과 중복은 없음. 반대로 “리뷰가 있는 책”을 찾는 쿼리라면 DISTINCT 필요

6.5 면접 방어 포인트

실행 계획 분석: MySQL 옵티마이저는 LEFT JOIN ... WHERE 우측PK IS NULL 패턴을 Anti Semi Join으로 인식하여 최적화한다. reviews(book_id) 인덱스가 있으면 Index Nested Loop, 없으면 Hash Anti Join 고려. EXPLAIN에서 Using where; Not exists 표시가 정상 신호
대안 비교:
- NOT EXISTS: 동일 결과, 의도 표현 가장 명확, early termination 가능 — 카디널리티가 책보다 리뷰가 훨씬 많을 때 유리
- NOT IN: NULL 방어(WHERE sub.col IS NOT NULL) 필수, 대용량에서 가장 불리
- LEFT JOIN + IS NULL (채택): 조인 키 인덱스 + 중복 폭증 없음의 경우 NOT EXISTS와 동등 성능. 선택 이유는 “JOIN 기반 사고 일관성”
공학적 확장:
- 인덱스 전략: reviews(book_id) 비클러스터드 인덱스 필수. books는 PK 스캔
- NULL 비율: reviews.book_id에 NULL이 많으면 NOT IN은 위험, LEFT JOIN과 NOT EXISTS는 영향 없음
- 방언 호환: 3가지 모두 표준 SQL. Oracle MINUS, PostgreSQL EXCEPT 같은 집합 연산으로도 표현 가능하나 컬럼 일치 제약이 있어 가독성 떨어짐
- 대용량 확장: 리뷰 테이블이 수억 건이면 “리뷰 플래그 컬럼” 또는 매트뷰(materialized view)로 사전 계산 고려

1 문제 1: 완료 주문 중 최고 결제 금액 조회

1.1 문제 정보

1.2 문제 설명

1.3 최종 풀이

1.4 Tips

1.5 면접 방어 포인트

2 문제 2: 평균 가격 이상인 도서 조회

2.1 문제 정보

2.2 문제 설명

2.3 최종 풀이

2.4 Tips

2.5 면접 방어 포인트

3 문제 3: 카테고리 평균보다 비싼 상품 조회

3.1 문제 정보

3.2 문제 설명

3.3 최종 풀이

3.4 Tips

3.5 면접 방어 포인트

4 문제 4: 한 번도 대출되지 않은 도서 조회

4.1 문제 정보

4.2 문제 설명

4.3 최종 풀이

4.4 Tips

4.5 면접 방어 포인트

5 문제 5: 5점 평가를 준 적 있는 회원 조회

5.1 문제 정보

5.2 문제 설명

5.3 최종 풀이

5.4 Tips

5.5 면접 방어 포인트

6 문제 6: 리뷰가 없는 도서 찾기 (LEFT JOIN + IS NULL)

6.1 문제 정보

6.2 문제 설명

6.3 최종 풀이

6.4 Tips

6.5 면접 방어 포인트

7 관련 문제