복잡한 조회를 정리해야 하는 이유
앞 장에서는 JOIN으로 여러 테이블을 연결하는 방법을 배웠습니다. 이제는 한 단계 더 나아가야 합니다. 실제 조회 쿼리는 단순히 테이블을 연결하는 데서 끝나지 않습니다.
예를 들어 다음과 같은 질문을 생각해 봅시다.
결제 완료 주문이 있는 회원만 보고 싶다.
평균 가격보다 비싼 상품만 찾고 싶다.
주문별 합계를 먼저 구한 뒤, 회원별 총액을 다시 구하고 싶다.
자주 쓰는 긴 조인 쿼리를 짧게 재사용하고 싶다.
이런 상황에서 필요한 도구가 서브쿼리와 뷰입니다.
서브쿼리는 쿼리 안에 들어가는 또 다른 쿼리입니다. 먼저 어떤 결과를 구하고, 그 결과를 바깥쪽 쿼리에서 다시 사용하는 방식입니다.
뷰는 자주 사용하는 SELECT 문에 이름을 붙여 둔 것입니다. 실제 데이터를 새로 저장하는 테이블이라기보다, 복잡한 조회 쿼리를 간단한 이름으로 꺼내 쓰는 장치라고 이해하면 됩니다.
이번 장에서는 이전 장에서 만든 테이블을 계속 사용합니다.
members: 회원products: 상품orders: 주문order_items: 주문 상품
서브쿼리의 기본 형태
서브쿼리는 괄호 안에 작성합니다.
SELECT 열이름
FROM 테이블이름
WHERE 열이름 IN (
SELECT 열이름
FROM 다른_테이블
WHERE 조건
);안쪽 쿼리가 먼저 중간 결과를 만듭니다. 바깥쪽 쿼리는 그 결과를 조건이나 임시 테이블처럼 사용합니다.
간단한 예를 보겠습니다. 평균 가격보다 비싼 상품을 조회해 보겠습니다.
SELECT
id,
name,
price
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);안쪽 쿼리는 상품 평균 가격을 구합니다.
SELECT AVG(price)
FROM products;바깥쪽 쿼리는 그 평균값보다 price가 큰 상품만 조회합니다.
이처럼 비교 연산자 >, <, =와 함께 사용하는 서브쿼리는 보통 결과가 하나의 값이어야 합니다. 이를 단일 값 서브쿼리라고 생각하면 됩니다.
WHERE 절 서브쿼리로 조건 만들기
WHERE 절의 서브쿼리는 "특정 조건을 만족하는 행만 고르기"에 자주 사용합니다.
결제 완료 주문이 있는 회원만 조회해 보겠습니다.
SELECT
id,
name,
email
FROM members
WHERE id IN (
SELECT member_id
FROM orders
WHERE status = 'PAID'
);안쪽 쿼리는 결제 완료 주문의 회원 번호를 구합니다.
SELECT member_id
FROM orders
WHERE status = 'PAID';바깥쪽 쿼리는 members.id가 그 목록 안에 있는 회원만 가져옵니다.
이 쿼리는 다음처럼 해석하면 됩니다.
회원 중에서, 결제 완료 주문 목록에 포함된 회원만 보여 달라.
이번에는 특정 상품을 주문한 회원을 찾아보겠습니다. 예를 들어 무선 마우스를 주문한 회원입니다.
SELECT
id,
name,
email
FROM members
WHERE id IN (
SELECT o.member_id
FROM orders AS o
INNER JOIN order_items AS oi
ON o.id = oi.order_id
INNER JOIN products AS p
ON oi.product_id = p.id
WHERE p.name = '무선 마우스'
);안쪽 쿼리는 orders, order_items, products를 연결해서 무선 마우스가 포함된 주문의 회원 번호를 찾습니다. 바깥쪽 쿼리는 그 회원 번호에 해당하는 회원 정보를 조회합니다.
조인만으로도 같은 결과를 만들 수 있습니다. 하지만 서브쿼리를 사용하면 "먼저 조건에 맞는 회원 번호를 찾고, 그 회원을 조회한다"는 흐름이 분명해집니다.
중요한 것은 문법보다 읽는 순서입니다.
안쪽 쿼리가 어떤 결과를 만드는지 확인합니다.
바깥쪽 쿼리가 그 결과를 어디에 사용하는지 봅니다.
전체 질문을 자연어로 다시 말해 봅니다.
FROM 절 서브쿼리로 중간 결과 만들기
서브쿼리는 WHERE 절에만 쓰지 않습니다. FROM 절에서도 사용할 수 있습니다.
FROM 절의 서브쿼리는 임시 테이블처럼 동작합니다. 보통 "먼저 중간 결과를 만들고, 그 결과를 다시 집계할 때" 유용합니다.
예를 들어 회원별 결제 완료 주문 금액을 구한다고 해 봅시다. 주문 금액은 order_items의 수량과 주문 당시 가격을 곱해서 계산해야 합니다.
먼저 주문별 합계를 구합니다.
SELECT
o.id AS order_id,
o.member_id,
SUM(oi.quantity * oi.price) AS order_total
FROM orders AS o
INNER JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'PAID'
GROUP BY o.id, o.member_id;이 결과는 주문 한 건당 합계를 보여 줍니다.
이제 이 결과를 중간 테이블처럼 사용해서 회원별 합계를 다시 구할 수 있습니다.
SELECT
order_totals.member_id,
COUNT(*) AS paid_order_count,
SUM(order_totals.order_total) AS paid_total
FROM (
SELECT
o.id AS order_id,
o.member_id,
SUM(oi.quantity * oi.price) AS order_total
FROM orders AS o
INNER JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'PAID'
GROUP BY o.id, o.member_id
) AS order_totals
GROUP BY order_totals.member_id;여기서 order_totals는 FROM 절 서브쿼리에 붙인 별칭입니다. MySQL에서는 FROM 절에 서브쿼리를 사용할 때 별칭을 붙여야 합니다.
이 쿼리는 두 단계로 나누어 읽으면 쉽습니다.
첫째, 안쪽 쿼리에서 결제 완료 주문별 합계를 구합니다.
주문별 합계둘째, 바깥쪽 쿼리에서 주문별 합계를 회원별로 다시 묶습니다.
회원별 결제 주문 수와 총 결제 금액회원 이름까지 함께 보고 싶다면 members와 다시 조인하면 됩니다.
SELECT
m.id,
m.name,
summary.paid_order_count,
summary.paid_total
FROM members AS m
LEFT JOIN (
SELECT
order_totals.member_id,
COUNT(*) AS paid_order_count,
SUM(order_totals.order_total) AS paid_total
FROM (
SELECT
o.id AS order_id,
o.member_id,
SUM(oi.quantity * oi.price) AS order_total
FROM orders AS o
INNER JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'PAID'
GROUP BY o.id, o.member_id
) AS order_totals
GROUP BY order_totals.member_id
) AS summary
ON m.id = summary.member_id
ORDER BY m.id;이 쿼리는 길지만 구조는 단순합니다.
가장 안쪽: 주문별 합계
중간: 회원별 결제 주문 수와 총액
바깥쪽: 회원 이름과 함께 표시
쿼리가 길어질수록 한 번에 모든 것을 해결하려고 하지 마세요. 중간 결과를 작은 단위로 나누면 훨씬 읽기 쉬워집니다.
IN과 EXISTS의 차이
IN과 EXISTS는 둘 다 "조건에 맞는 데이터가 있는가?"를 확인할 때 자주 사용합니다. 하지만 생각하는 방식이 조금 다릅니다.
IN은 어떤 값이 목록 안에 있는지 확인합니다.
SELECT
id,
name
FROM members
WHERE id IN (
SELECT member_id
FROM orders
WHERE status = 'PAID'
);이 쿼리는 다음처럼 읽습니다.
회원 id가 결제 완료 주문의 member_id 목록 안에 있는가?
반면 EXISTS는 서브쿼리 결과가 하나라도 존재하는지 확인합니다.
SELECT
m.id,
m.name
FROM members AS m
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.member_id = m.id
AND o.status = 'PAID'
);이 쿼리는 다음처럼 읽습니다.
이 회원에게 결제 완료 주문이 하나라도 존재하는가?
EXISTS 안의 SELECT 1에서 1은 특별한 값을 가져오려는 뜻이 아닙니다. 존재 여부만 확인하므로 실제로 어떤 열을 선택하는지는 중요하지 않습니다. 관습적으로 SELECT 1을 많이 사용합니다.
두 쿼리는 현재 예제에서는 같은 결과를 낼 수 있습니다. 하지만 사고방식이 다릅니다.
IN: 값이 결과 목록에 포함되는지 확인한다.EXISTS: 조건을 만족하는 행이 존재하는지 확인한다.
관계 데이터에서는 EXISTS가 더 자연스럽게 읽히는 경우가 많습니다. 특히 바깥쪽 테이블의 한 행마다 관련 데이터가 있는지 확인할 때 그렇습니다.
NOT IN보다 NOT EXISTS가 안전한 경우
주문이 없는 회원을 찾고 싶다고 해 봅시다. 앞 장에서는 LEFT JOIN과 IS NULL을 사용했습니다. 서브쿼리로도 작성할 수 있습니다.
SELECT
m.id,
m.name
FROM members AS m
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.member_id = m.id
);이 쿼리는 다음처럼 읽습니다.
이 회원에게 연결된 주문이 하나도 존재하지 않는가?
비슷하게 NOT IN을 사용할 수도 있습니다.
SELECT
id,
name
FROM members
WHERE id NOT IN (
SELECT member_id
FROM orders
);단, NOT IN은 서브쿼리 결과에 NULL이 섞이면 예상과 다른 결과를 낼 수 있습니다. 현재 orders.member_id는 NOT NULL로 설계했기 때문에 문제가 적지만, 모든 테이블이 항상 이렇게 안전하게 설계되어 있는 것은 아닙니다.
그래서 "관련 데이터가 없는 행"을 찾을 때는 NOT EXISTS를 우선 고려하는 것이 좋습니다.
정리하면 다음과 같습니다.
포함 여부를 단순히 비교할 때는
IN이 읽기 쉽습니다.관련 행의 존재 여부를 확인할 때는
EXISTS가 자연스럽습니다.관련 행이 없는 데이터를 찾을 때는
NOT EXISTS가 안전한 선택인 경우가 많습니다.
상관 서브쿼리 이해하기
앞에서 본 EXISTS 예제에는 중요한 특징이 있습니다.
SELECT
m.id,
m.name
FROM members AS m
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.member_id = m.id
);안쪽 쿼리에서 바깥쪽 쿼리의 m.id를 사용하고 있습니다.
WHERE o.member_id = m.id이런 서브쿼리를 상관 서브쿼리라고 합니다. 안쪽 쿼리가 바깥쪽 쿼리의 현재 행에 의존하기 때문입니다.
쉽게 말하면 MySQL은 회원을 한 명씩 보면서 이렇게 확인합니다.
민준에게 주문이 있는가?
서연에게 주문이 있는가?
지우에게 주문이 있는가?
상관 서브쿼리는 관계를 확인할 때 매우 유용합니다. 다만 무리하게 많이 사용하면 쿼리가 느려질 수 있습니다. 성능이 걱정될 때는 조인으로 바꿔 보거나, 다음 장에서 배울 인덱스를 함께 고려해야 합니다.
처음에는 성능보다 정확하고 읽기 쉬운 쿼리를 작성하는 데 집중하세요. 그다음 실행 계획과 인덱스로 개선하면 됩니다.
뷰가 필요한 순간
이제 뷰를 살펴보겠습니다.
뷰는 자주 사용하는 SELECT 문에 이름을 붙인 것입니다. 다음과 같은 상황에서 유용합니다.
조인 쿼리가 너무 길어서 매번 쓰기 번거롭다.
여러 사람이 같은 조회 기준을 사용해야 한다.
원본 테이블의 일부 열만 보여 주고 싶다.
복잡한 조회 로직을 단순한 테이블처럼 사용하고 싶다.
예를 들어 주문 상세를 조회하려면 매번 네 개의 테이블을 조인해야 합니다.
SELECT
o.id AS order_id,
o.status,
o.ordered_at,
m.id AS member_id,
m.name AS member_name,
p.id AS product_id,
p.name AS product_name,
oi.quantity,
oi.price,
oi.quantity * oi.price AS line_total
FROM orders AS o
INNER JOIN members AS m
ON o.member_id = m.id
INNER JOIN order_items AS oi
ON o.id = oi.order_id
INNER JOIN products AS p
ON oi.product_id = p.id;이 쿼리를 자주 사용한다면 뷰로 만들 수 있습니다.
CREATE OR REPLACE VIEW v_order_lines AS
SELECT
o.id AS order_id,
o.status,
o.ordered_at,
m.id AS member_id,
m.name AS member_name,
p.id AS product_id,
p.name AS product_name,
oi.quantity,
oi.price,
oi.quantity * oi.price AS line_total
FROM orders AS o
INNER JOIN members AS m
ON o.member_id = m.id
INNER JOIN order_items AS oi
ON o.id = oi.order_id
INNER JOIN products AS p
ON oi.product_id = p.id;이제 긴 조인 쿼리 대신 뷰 이름을 사용할 수 있습니다.
SELECT
order_id,
member_name,
product_name,
quantity,
price,
line_total
FROM v_order_lines
WHERE status = 'PAID'
ORDER BY order_id, product_name;겉으로는 테이블을 조회하는 것처럼 보이지만, 실제로는 뷰에 저장된 SELECT 문이 실행됩니다.
뷰의 장점은 쿼리를 짧게 만드는 것만이 아닙니다. 의미 있는 이름을 붙여 복잡한 조회를 하나의 개념으로 정리할 수 있습니다.
v_order_lines라는 이름만 보아도 "주문 상품 줄 단위 조회"라는 뜻을 알 수 있습니다.
뷰로 반복 조회 단순화하기
뷰를 만들면 그 위에서 다시 집계할 수 있습니다.
회원별 결제 금액을 뷰를 이용해 구해 보겠습니다.
SELECT
member_id,
member_name,
COUNT(DISTINCT order_id) AS paid_order_count,
SUM(line_total) AS paid_total
FROM v_order_lines
WHERE status = 'PAID'
GROUP BY member_id, member_name
ORDER BY paid_total DESC;원래라면 orders, members, order_items, products를 모두 조인해야 했습니다. 하지만 뷰를 사용하면 조회 목적이 더 잘 보입니다.
결제 완료 주문 상세에서 회원별 주문 수와 총액을 구한다.
뷰는 특히 보고서성 조회에 잘 맞습니다. 예를 들어 관리자 화면, 통계 화면, 엑셀 다운로드용 데이터처럼 같은 형태의 조회를 반복할 때 유용합니다.
이번에는 상품별 판매 수량을 구해 보겠습니다.
SELECT
product_id,
product_name,
SUM(quantity) AS sold_quantity,
SUM(line_total) AS sales_amount
FROM v_order_lines
WHERE status = 'PAID'
GROUP BY product_id, product_name
ORDER BY sales_amount DESC;뷰 하나를 만들어 두면 여러 조회에 재사용할 수 있습니다.
회원별 매출
상품별 매출
주문별 상세
결제 완료 주문 목록
반복되는 조인 로직은 뷰에 넣고, 조회마다 달라지는 조건과 집계는 바깥쪽 쿼리에서 처리하면 좋습니다.
뷰와 권한 관리
뷰는 권한 관리에도 활용할 수 있습니다.
예를 들어 어떤 사용자에게 주문 분석용 데이터는 보여 주고 싶지만, 회원 이메일이나 주소 같은 민감한 정보는 보여 주고 싶지 않을 수 있습니다.
이럴 때 필요한 열만 포함한 뷰를 만들 수 있습니다.
CREATE OR REPLACE VIEW v_sales_report AS
SELECT
o.id AS order_id,
o.status,
o.ordered_at,
m.name AS member_name,
p.name AS product_name,
oi.quantity,
oi.price,
oi.quantity * oi.price AS line_total
FROM orders AS o
INNER JOIN members AS m
ON o.member_id = m.id
INNER JOIN order_items AS oi
ON o.id = oi.order_id
INNER JOIN products AS p
ON oi.product_id = p.id;이 뷰에는 members.email이 포함되어 있지 않습니다. 따라서 보고서 조회자는 이메일을 보지 않고도 주문 분석을 할 수 있습니다.
권한은 다음처럼 뷰 단위로 줄 수 있습니다.
GRANT SELECT ON mysql_book.v_sales_report
TO 'report_user'@'localhost';사용자와 권한 관리는 뒤 장에서 더 자세히 다룹니다. 지금은 뷰가 "복잡한 조회를 감추는 도구"이면서 동시에 "보여 줄 열을 제한하는 도구"로도 쓰인다는 점만 기억하면 됩니다.
단, 뷰가 보안의 모든 문제를 해결해 주는 것은 아닙니다. 실제 운영 환경에서는 사용자 권한, 뷰 정의 방식, 원본 테이블 권한을 함께 확인해야 합니다.
뷰 사용할 때 주의할 점
뷰는 편리하지만 무조건 많이 만든다고 좋은 것은 아닙니다.
첫째, 뷰는 보통 데이터를 복사해서 저장하지 않습니다. 뷰를 조회할 때마다 내부의 SELECT 문이 실행된다고 이해하는 편이 좋습니다. 따라서 매우 복잡한 뷰를 자주 조회하면 성능 문제가 생길 수 있습니다.
둘째, 뷰 위에 또 다른 뷰를 계속 쌓으면 구조를 파악하기 어려워집니다. 처음에는 편하지만 시간이 지나면 "이 값이 어디서 나온 것인지" 추적하기 힘들어질 수 있습니다.
셋째, 모든 뷰가 자유롭게 수정 가능한 것은 아닙니다. 단순한 테이블 조회 뷰는 INSERT, UPDATE가 가능한 경우도 있지만, 조인이나 집계가 들어간 뷰는 수정이 제한되는 경우가 많습니다. 이 책에서는 뷰를 주로 조회용으로 사용한다고 생각하세요.
넷째, 뷰 안의 컬럼 이름은 명확해야 합니다. 여러 테이블에 id, name 같은 열이 있으므로 member_id, product_name, line_total처럼 의미가 드러나는 이름을 붙이는 것이 좋습니다.
좋은 뷰는 긴 쿼리를 숨기는 것에서 끝나지 않습니다. 조회 결과의 의미를 더 분명하게 만들어야 합니다.
서브쿼리와 뷰를 고르는 기준
서브쿼리와 뷰는 역할이 다릅니다.
서브쿼리는 한 번의 조회 안에서 중간 결과가 필요할 때 적합합니다.
이번 쿼리 안에서만 필요한 임시 결과뷰는 여러 곳에서 반복해서 사용할 조회 로직에 적합합니다.
이름을 붙여 계속 재사용할 조회 결과다음 기준으로 선택하면 됩니다.
한 번만 쓰는 조건이면 서브쿼리를 사용합니다.
쿼리 안에서 단계적으로 집계해야 하면
FROM절 서브쿼리를 사용합니다.같은 조인과 계산을 여러 번 반복한다면 뷰를 고려합니다.
특정 사용자에게 일부 열만 보여 주고 싶다면 뷰를 고려합니다.
성능이 중요하다면 실제 실행 계획을 확인해야 합니다.
중요한 것은 "짧게 쓰는 것"이 아니라 "정확하게 읽히는 것"입니다.
쿼리는 작성하는 시간보다 읽고 수정하는 시간이 더 깁니다. 미래의 자신과 동료가 이해할 수 있는 구조로 정리하는 습관이 필요합니다.
자주 하는 실수
서브쿼리 결과가 여러 행인데 단일 값 비교를 하는 경우
다음 쿼리는 문제가 될 수 있습니다.
SELECT
id,
name
FROM members
WHERE id = (
SELECT member_id
FROM orders
);= 비교는 오른쪽에 하나의 값이 오기를 기대합니다. 그런데 서브쿼리가 여러 회원 번호를 반환하면 오류가 발생할 수 있습니다.
여러 값과 비교하려면 IN을 사용합니다.
SELECT
id,
name
FROM members
WHERE id IN (
SELECT member_id
FROM orders
);FROM 절 서브쿼리에 별칭을 붙이지 않는 경우
MySQL에서는 FROM 절 서브쿼리에 별칭을 붙여야 합니다.
잘못된 예입니다.
SELECT
member_id,
SUM(order_total)
FROM (
SELECT
o.member_id,
SUM(oi.quantity * oi.price) AS order_total
FROM orders AS o
INNER JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY o.id, o.member_id
)
GROUP BY member_id;올바른 예입니다.
SELECT
order_totals.member_id,
SUM(order_totals.order_total) AS total_amount
FROM (
SELECT
o.id AS order_id,
o.member_id,
SUM(oi.quantity * oi.price) AS order_total
FROM orders AS o
INNER JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY o.id, o.member_id
) AS order_totals
GROUP BY order_totals.member_id;뷰를 실제 테이블처럼 오해하는 경우
뷰는 테이블처럼 조회할 수 있지만, 일반적으로 원본 데이터를 기반으로 실행되는 SELECT 문입니다. 뷰를 만들었다고 해서 데이터가 별도로 복사되어 저장된다고 생각하면 안 됩니다.
원본 테이블의 데이터가 바뀌면 뷰 조회 결과도 함께 바뀝니다.
너무 많은 로직을 한 쿼리에 몰아넣는 경우
서브쿼리를 여러 겹으로 중첩하면 오히려 읽기 어려워질 수 있습니다. 복잡한 쿼리는 다음 중 하나를 고려하세요.
중간 결과를
FROM절 서브쿼리로 분리한다.자주 쓰는 부분을 뷰로 만든다.
쿼리를 단계별로 실행해 중간 결과를 확인한다.
성능 문제가 있으면 인덱스와 실행 계획을 확인한다.
복잡함을 없앨 수는 없습니다. 하지만 구조를 나누면 관리할 수 있습니다.
이번 장 정리
서브쿼리는 쿼리 안에 들어가는 또 다른 쿼리입니다. WHERE 절에서는 조건에 맞는 행을 고르는 데 자주 사용하고, FROM 절에서는 중간 결과를 만든 뒤 다시 집계할 때 유용합니다.
IN은 값이 목록 안에 있는지 확인합니다. EXISTS는 조건을 만족하는 관련 행이 존재하는지 확인합니다. 특히 관련 데이터가 없는 행을 찾을 때는 NOT EXISTS가 안전하고 읽기 쉬운 경우가 많습니다.
뷰는 자주 사용하는 SELECT 문에 이름을 붙인 것입니다. 긴 조인 쿼리를 짧게 재사용할 수 있고, 필요한 열만 노출해 권한 관리에도 활용할 수 있습니다.
쿼리가 길어지는 것은 자연스러운 일입니다. 중요한 것은 길어진 쿼리를 그대로 방치하지 않는 것입니다. 서브쿼리로 단계를 나누고, 뷰로 반복을 줄이면 복잡한 조회도 읽기 쉬운 구조로 정리할 수 있습니다.
연습 문제
다음 쿼리를 직접 작성해 보세요.
평균 상품 가격보다 비싼 상품만 조회해 보세요.
결제 완료 주문이 있는 회원을
IN서브쿼리로 조회해 보세요.결제 완료 주문이 있는 회원을
EXISTS로 다시 작성해 보세요.주문이 없는 회원을
NOT EXISTS로 조회해 보세요.주문별 총액을
FROM절 서브쿼리로 만든 뒤, 회원별 총 주문 금액을 구해 보세요.주문 상세 조회를 위한
v_order_lines뷰를 만들고, 이 뷰를 이용해 상품별 판매 금액을 구해 보세요.회원 이메일을 제외한 보고서용 뷰를 만들어 보세요.
연습할 때는 먼저 안쪽 쿼리만 따로 실행해 보세요. 중간 결과가 맞는지 확인한 뒤 바깥쪽 쿼리와 연결하면 실수를 줄일 수 있습니다.
이 노트는 보도·비평·교육·연구 목적으로 작성되었습니다. 저작권 문의가 있으시면 에서 알려주세요.