혹시 데이터베이스에서 특정 조건에 맞는 데이터만 쏙쏙 뽑아내고 싶었던 적, 있지않은가?
예를 들어, 우리 서비스에서 가장 인기 있는 상품을 찾고 싶거나,
평균 나이가 20대인 사용자들의 정보만 필요할 때가 있다.
이럴 때 바로 서브쿼리(Subquery)를 사용하면 매우 유용하다.
서브쿼리란? (SubQuery)
서브쿼리(Subqeury)란, 하나의 SQL 쿼리문 안에 포함되어 있는 또 다른 SQL 쿼리문을 말한다.
쉽게 말해, 서브쿼리는 쿼리 속의 쿼리라고 할 수 있다.
마치 러시아 인형 마트료시카처럼 큰 쿼리 안에 작은 쿼리가 들어가 있는 형태로,
SQL 문 속에 또 다른 SQL 문이 들어있는 구조다.
왜 굳이 쿼리 안에 또 다른 쿼리를 넣는걸까?
서브쿼리를 사용하면 복잡한 조건을 여러 단계로 나누어 처리할 수 있다.
복잡한 조건을 가진 데이터도 간결하고 효율적으로 조회할 수 있다.
예를 들어, 전체 직원 중에서 평균 연봉보다 높은 직원 정보만 따로 추출하고 싶다고 가정해보자.
이때 서브쿼리를 사용하면, 먼저 ‘전체 직원의 평균 연봉’을 구하는 서브쿼리를 작성하고,
그 결과를 이용해 조건에 맞는 직원 정보를 조회하는 방식으로 해결할 수 있다.
때문에 훨씬 효율적이고 가독성 높은 쿼리를 작성할 수 있게 된다.
서브쿼리, 어떻게 사용할까?
서브쿼리는 주로
WHERE
절이나
FROM
절에서 사용된다.
간단한 예시를 통해 살펴보자.
WHERE 절에서 사용하는 서브쿼리
🔍 시나리오: 우리 쇼핑몰에서 가장 비싼 상품의 이름을 찾고 싶어요!
SELECT
상품명
FROM
상품
WHERE
가격 = (SELECT MAX(가격) FROM 상품);- 먼저, 안쪽의
(SELECT MAX(가격) FROM 상품)서브쿼리가 가장 비싼 상품의 가격을 찾아낸다. - 그리고 바깥쪽의 쿼리는 찾아낸 가격과 같은 상품의 이름을
SELECT한다.
FROM 절에서 사용하는 서브쿼리
🔍 시나리오: 각 상품 카테고리별 평균 가격보다 비싼 상품들의 목록을 보고 싶어요!
SELECT
상품명, 가격
FROM
상품
JOIN (
SELECT 카테고리, AVG(가격) AS 평균가격
FROM 상품
GROUP BY 카테고리
) AS 카테고리별평균
ON 상품.카테고리 = 카테고리별평균.카테고리
WHERE
상품.가격 > 카테고리별평균.평균가격;- 먼저, 안쪽의 서브쿼리가 각 카테고리별 평균 가격을 계산하여
카테고리별평균이라는 임시 테이블을 생성한다. - 그리고 바깥쪽의 쿼리는
JOIN을 통해 상품 정보와카테고리별평균테이블을 연결한다. - 각 상품의 가격이 해당 카테고리의 평균 가격보다 높은 상품들이 있는지
where절을 통해 조건을 확인하고SELECT한다.
서브쿼리 종류, 더 알아보기
서브쿼리에는 다양한 종류가 존재한다.
단일 행 서브쿼리(Single-Row Subquery), 하나의 값만 쏙!
단일 행 서브쿼리는 말 그대로 결과 값으로 단 하나의 행만 반환하는 서브쿼리를 의미한다.
주로 메인 쿼리의 조건을 만족하는 특정 값을 찾아낼 때 유용하게 사용된다.
단일 행 서브쿼리는
=, >, <, >=, <=, <>
등의 비교 연산자와 함께 사용된다.
예시: 전체 직원 중에서 ‘영업부’의 평균 연봉보다 높은 연봉을 받는 직원을 찾는다고 가정해 보자.
SELECT
*
FROM
직원
WHERE
연봉 > (SELECT AVG(연봉)
FROM 직원
WHERE 부서= '영업부');- 괄호 안의 서브쿼리
(SELECT AVG(연봉) FROM 직원 WHERE 부서 = '영업부')가 먼저 실행되어 ‘영업부’ 직원들의 평균 연봉을 계산한다. - 메인 쿼리는 서브쿼리에서 계산된 평균 연봉보다 높은 연봉을 받는 직원 정보를
직원테이블에서 조회한다.
다중 행 서브쿼리(Multiple-Row Subquery): 여러 값을 한 번에!
다중 행 서브쿼리는 단일 행 서브쿼리와 달리 결과 값으로 여러 개의 행을 반환하는 서브쿼리를 말한다.
주로 메인 쿼리의 조건을 만족하는 여러 값을 한 번에 비교해야 할 때 사용된다.
다중 행 서브쿼리는
IN, ANY, ALL, EXISTS, NOT EXISTS
등의 연산자와 함께 사용된다.
예시: 각 부서별로 가장 높은 연봉을 받는 직원 정보를 찾는다고 가정해 보자.
SELECT
*
FROM
직원
WHERE
(부서, 연봉) IN (
SELECT 부서, MAX(연봉)
FROM 직원
GROUP BY 부서
); - 괄호 안의 서브쿼리
(SELECT 부서, MAX(연봉) FROM 직원 GROUP BY 부서)가 먼저 실행되어 각 부서별 최고 연봉과 부서 정보를 조회한다. - 메인 쿼리는 서브쿼리 결과와
직원테이블의부서와연봉을 비교하여 조건을 만족하는 직원 정보를 조회한다.
상관 서브쿼리(Correlated Subquery): 찰떡궁합 콤비!
상관 서브쿼리는 메인 쿼리의 값을 참조하여 실행되는 서브쿼리이다.
즉, 서브쿼리가 메인 쿼리에 종속되어 매 행마다 다른 결과를 반환할 수 있다.
상관 서브쿼리는 주로 특정 그룹 내에서 조건을 비교하거나, 각 행에 대한 추가적인 정보를 가져올 때 유용하게 사용된다.
예시: 각 직원의 연봉이 속한 부서의 평균 연봉보다 높은지 확인한다고 가정해 보자.
SELECT
*
FROM
직원 as t1
WHERE 연봉 > (SELECT AVG(연봉)
FROM 직원 as t2
WHERE t2.부서= t1.부서);- 메인 쿼리는
직원테이블에서 각 직원 정보를t1이라는 별칭으로 가져온다. - 서브쿼리는
t1.부서값을 참조하여 해당 부서의 평균 연봉을 계산한다. - 메인 쿼리는 각 직원의 연봉(
t1.연봉)이 서브쿼리에서 계산된 평균 연봉보다 높은 경우 해당 직원 정보를 출력한다.
서브쿼리의 성능은?
서브쿼리의 성능은 상황에 따라 크게 다를 수 있다.
최적의 성능을 위해서는 실제 데이터와 환경에서 테스트하고,
필요에 따라 조인이나 다른 기법으로 대체하는 것이 중요하다.
서브쿼리는 유연하고 다양한 조건을 표현할 수 있지만,
잘못 사용하면 성능 저하를 야기할 수 있다.
주요 원인은 다음과 같다.
- 반복적인 실행: 서브쿼리가 각 행마다 실행될 수 있어 불필요한 자원 낭비 초래
- 인덱스 활용 어려움: 서브쿼리 결과는 일반적으로 임시 테이블로 생성되기 때문에 인덱스 활용 어려움
- 복잡한 쿼리: 서브쿼리가 중첩되거나 복잡한 조건을 포함하면 쿼리 최적화가 어려워져 성능 저하
서브쿼리 유형별 성능은 다음과 같다.
- 스칼라 서브쿼리: 단일 값을 반환하며, 일반적으로 가장 빠르다.
- 인라인 뷰: FROM 절에 사용되며, 중간 정도의 성능을 보인다.
- 상관 서브쿼리: 메인 쿼리와 연관되어 있어 일반적으로 가장 느리다.
서브 쿼리를 사용할 때는 이러한 요소들을 고려하여 성능을 최적화하는 것이 중요하다.
필요한 경우, 조인이나 뷰 등 다른 SQL 기능을 사용하는 것을 고려해 보는 것도 좋다.
서브쿼리와 조인, 속도 차이는?
Subquery, Join 차이부터!
서브쿼리는,
말 그대로 쿼리 안에 들어있는 또 다른 쿼리를 말한다.
큰 쿼리 안에서 필요한 데이터를 단계별로 조회하는 방식이다.
조인은,
여러 개의 테이블을 특정 조건에 따라 하나로 합쳐서 데이터를 조회하는 방식이다.
테이블들을 연결하여 원하는 정보를 얻어낸다.
서브쿼리, 조인 장단점 비교
조인의 장점
- 한 번의 실행으로 모든 데이터를 가져올 수 있다.
- 대부분의 데이터베이스 시스템에서 최적화가 잘 되어 있다.
- 대량의 데이터를 처리할 때 더 효율적이다.
서브쿼리의 특징
- 때로는 더 직관적이고 읽기 쉬울 수 있다.
- 특정 상황에서는 더 효율적일 수 있다 (예: 특정 조건을 만족하는 소량의 데이터를 찾을 때).
- 중첩될 경우 성능이 크게 저하될 수 있다.
서브쿼리 vs. JOIN 성능 비교
- 일반적인 경우: JOIN이 서브쿼리보다 성능이 좋다. JOIN은 데이터베이스 최적화 엔진이 한 번에 두 개의 테이블을 스캔하여 연결하기 때문에 더 효율적이다.
- 복잡한 조건(쿼리 복잡도): 서브쿼리는 복잡한 조건을 표현하기에 유용하며, JOIN으로 표현하기 어려운 경우 서브쿼리를 사용하는 것이 좋다. 즉, 복잡한 조건에는 서브쿼리가 더 직관적일 수 있다.
- 데이터 분포: 데이터 분포에 따라 어떤 방식이 더 효율적인지 달라질 수 있다. 데이터 볼륨이 큰 대량의 데이터의 경우, 조인이 더 효율적이다.
- 인덱스 활용 유무: 인덱스는 데이터베이스에서 특정 데이터를 빠르게 찾을 수 있도록 도와주는 일종의 “목차”이다. 조인은 인덱스를 효율적으로 활용하여 빠른 검색 속도를 제공하는 반면, 서브쿼리는 인덱스를 효과적으로 활용하지 못하는 경우가 많아 속도가 느려질 수 있다.
서브쿼리가 유리한 경우
데이터 양이 적다면, 서브쿼리도 충분히 빠른 성능을 보여줄 수 있다.
오히려 조인보다 간결하게 쿼리를 작성할 수 있어 유리할 수 있다.
- 단순 존재 여부 확인 (EXISTS 사용)
- 집계 함수를 이용한 계산
- 소량의 데이터 처리
조인이 유리한 경우
데이터 양이 많아질수록 서브쿼리는 치명적인 약점을 드러낸다.
서브쿼리는 매번 새로운 쿼리를 실행하여 결과를 가져오기 때문에
데이터 양이 많아질수록 그만큼 많은 시간이 소요된다.
반면, 조인은 테이블을 한 번만 읽어와 처리하기 때문에 대용량 데이터 처리에 훨씬 효율적이다.
- 대량의 데이터 처리
- 여러 테이블의 데이터를 결합해야 할 때
- 복잡한 조건이나 관계를 표현할 때
결론
일반적으로 조인은 서브 쿼리에 비해 속도가 빠르다.
하지만 쿼리의 복잡도에 따라 달라질 수 있다.
그렇기 때문에 최적의 선택을 위해서는,
실제 데이터와 환경에서 테스트를 진행해보고
데이터베이스의 구조와 데이터의 양에 따라 적절한 방법을 선택하는 것이 좋다.
✨ 기억해야 할 핵심
- 조인: 대용량 데이터 처리, 복잡한 쿼리에 유리
- 서브쿼리: 소규모 데이터 처리, 간단한 쿼리, 가독성 향상에 유리
서브쿼리에서 ORDER BY와 GROUP BY
서브쿼리에서의 ORDER BY
일반적으로 서브쿼리 내에서
ORDER BY
는 결과의 순서를 보장하지 않기 때문에 의미가 없다.
즉,
ORDER BY
절을 사용하더라도 외부 쿼리에서 예상한 순서대로 결과가 나오지 않을 수 있다.
그래서
EXISTS, IN
연산자와 함께 서브쿼리 결과의 존재 여부만 확인하는 경우에는
ORDER BY
절을 생략해도 무방하다.
ORDER BY
는 보통 외부 쿼리에서 최종 결과를 정렬하는 것이 일반적이다.
하지만
TOP
(MS-SQL),
LIMIT, OFFSET
(MySQL) 등과 함께 사용될 때는 의미가 있을 수 있다.
상위 N개의 데이터만 필요한 경우,
ORDER BY
절과 함께 LIMIT 절(혹은
TOP
)을 사용하여 원하는 데이터를 추출할 수 있다.
사용 예시: 가장 최근에 주문한 한 명의 고객 정보를 가져오는 쿼리
SELECT
*
FROM
고객
WHERE
고객번호 IN (
SELECT 고객번호 FROM 주문
ORDER BY 주문일자 DESC
LIMIT 1
);서브쿼리에서의 GROUP BY
GROUP BY
는 서브쿼리에서 자주 사용되며, 집계 함수와 함께 사용하여 중간 결과를 생성한다.
그룹별로 조건을 추가하고 싶을 때는
HAVING
절을 사용한다.
MS-SQL에서
GROUP BY
절을 사용할 때에는 사용된 컬럼 또는 집계 함수를 적용한 컬럼만 포함되어야 한다.
즉, 반드시
SELECT
절에는
GROUP BY
컬럼만 포함되어야 한다.
사용 예시: 총 주문 횟수가 10회를 초과하는 고객의 정보를 가져오는 쿼리
SELECT
*
FROM
고객
WHERE
고객번호 IN (
SELECT 고객번호
FROM 주문
GROUP BY 고객번호
HAVING COUNT(*) > 10
);서브쿼리에서 ORDER BY와 GROUP BY 함께 사용하기
서브쿼리에서 GROUP BY로 집계한 후 ORDER BY로 정렬할 수 있다.
사용 예시: 연봉이 높은 상위 3개 부서의 직원들 연봉 확인하기
SELECT
직원이름, 연봉
FROM
직원
WHERE
부서번호 IN (
SELECT 부서번호
FROM (
SELECT 부서번호, AVG(연봉) as 부서평균연봉
FROM 직원
GROUP BY 부서번호
ORDER BY 부서평균연봉 DESC
LIMIT 3
) as 상위부서
);마치며..
지금까지 서브쿼리의 개념과 종류, 그리고 실제 예시를 통해
SQL 활용 능력을 한 단계 업그레이드하는 방법을 알아보았다.
서브쿼리를 잘 활용하면 복잡한 데이터에서 원하는 정보만 쏙쏙 골라내서
복잡한 조건의 데이터도 효율적으로 추출하고 분석할 수 있다.
이 글을 읽는 당신께, 이 글로 하여금 데이터를 다루는 능력이 한층 더 발전되길 바란다.