SQL 서브쿼리 완벽 마스터 가이드

초급 개발자를 위한 SQL 서브쿼리 완벽 가이드입니다. WHERE, SELECT, FROM 절 서브쿼리부터 IN, EXISTS, 상관 서브쿼리까지 실무에서 바로 활용할 수 있는 예제와 함께 쉽게 설명합니다. 성능 최적화 팁까지 포함되어 있습니다.

---

목차

1. 서브쿼리란 무엇인가?
2. WHERE 절 서브쿼리
3. SELECT 절 스칼라 서브쿼리
4. FROM 절 인라인 뷰
5. IN과 EXISTS 서브쿼리
6. 상관 서브쿼리와 성능 고려사항

---

1. 서브쿼리란 무엇인가?

시작하며

여러분이 쇼핑몰 데이터베이스에서 "가장 비싼 상품을 구매한 고객의 정보를 찾아야 하는" 상황을 겪어본 적 있나요? 먼저 가장 비싼 상품의 가격을 찾고, 그 다음에 그 가격으로 구매한 고객을 찾아야 합니다.

이렇게 두 번의 쿼리를 실행해야 할까요? 이런 문제는 실제 개발 현장에서 매일 발생합니다.

복잡한 조건으로 데이터를 조회할 때마다 여러 번 쿼리를 실행하면 코드가 복잡해지고, 데이터베이스 왕복 시간도 늘어나 성능이 떨어집니다. 바로 이럴 때 필요한 것이 서브쿼리입니다.

서브쿼리를 사용하면 하나의 쿼리 안에 또 다른 쿼리를 넣어서 복잡한 조건도 한 번에 처리할 수 있습니다.

개요

간단히 말해서, 서브쿼리는 "쿼리 안에 들어있는 또 다른 쿼리"입니다. 마치 수학 문제에서 괄호 안의 계산을 먼저 하듯이, 서브쿼리의 결과를 먼저 계산한 후 바깥쪽 쿼리에서 그 결과를 사용합니다.

왜 서브쿼리가 필요할까요? 실무에서는 단순한 조건만으로 데이터를 조회하는 경우가 드뭅니다.

예를 들어, "평균보다 높은 급여를 받는 직원", "가장 많이 팔린 상품을 구매한 고객", "부서별 최고 연봉자" 같은 복잡한 조건을 처리할 때 서브쿼리는 필수입니다. 기존에는 여러 번의 쿼리를 실행하고 애플리케이션 코드에서 결과를 조합했다면, 이제는 서브쿼리 하나로 데이터베이스에서 모든 계산을 처리할 수 있습니다.

이렇게 하면 네트워크 왕복 시간이 줄어들고 코드도 간결해집니다. 서브쿼리의 핵심 특징은 세 가지입니다.

첫째, 괄호로 감싸서 독립적인 쿼리로 만듭니다. 둘째, 바깥쪽 쿼리가 실행되기 전에 서브쿼리가 먼저 실행됩니다.

셋째, WHERE, SELECT, FROM 등 다양한 위치에서 사용할 수 있습니다. 이러한 특징들이 복잡한 데이터 조회를 간단하게 만들어주는 이유입니다.

코드 예제

-- 기본적인 서브쿼리 예제
-- 평균 급여보다 높은 급여를 받는 직원 찾기
SELECT
    employee_name,
    salary,
    department
FROM employees
WHERE salary > (
    -- 서브쿼리: 전체 직원의 평균 급여 계산
    SELECT AVG(salary)
    FROM employees
)
ORDER BY salary DESC;

설명

이것이 하는 일: 위 코드는 회사의 모든 직원 중에서 평균 급여보다 많이 받는 직원들을 찾아냅니다. 마치 반에서 평균 점수보다 높은 학생들을 찾는 것과 같습니다.

첫 번째 단계로, 괄호 안의 서브쿼리가 먼저 실행됩니다. SELECT AVG(salary) FROM employees는 전체 직원의 급여 평균을 계산합니다.

예를 들어 평균이 5000만원이라면, 이 값이 서브쿼리의 결과가 됩니다. 왜 먼저 실행될까요?

바깥쪽 쿼리가 비교할 기준값이 필요하기 때문입니다. 그 다음으로, 바깥쪽 쿼리가 실행되면서 서브쿼리의 결과(5000만원)를 사용합니다.

WHERE salary > 5000만원과 같이 조건이 완성되고, 이 조건을 만족하는 직원들만 선택됩니다. 데이터베이스는 각 직원의 급여를 5000만원과 비교하면서 필터링합니다.

마지막으로, ORDER BY salary DESC가 실행되어 선택된 직원들을 급여가 높은 순서대로 정렬합니다. 최종적으로 평균보다 많이 받는 직원의 이름, 급여, 부서 정보가 급여 순으로 출력됩니다.

여러분이 이 코드를 사용하면 복잡한 비교 조건을 간단하게 처리할 수 있습니다. 애플리케이션에서 평균을 따로 계산할 필요가 없고, 데이터베이스가 모든 계산을 한 번에 처리해줍니다.

또한 데이터가 변경되어도 항상 최신 평균값을 기준으로 조회되므로 정확성이 보장됩니다.

실전 팁

💡 서브쿼리는 반드시 괄호로 감싸야 합니다. 괄호를 빼먹으면 문법 오류가 발생하니 주의하세요.

💡 서브쿼리가 너무 복잡해지면 WITH 절(CTE)을 사용하는 것이 더 읽기 좋습니다. 특히 같은 서브쿼리를 여러 번 사용할 때 유용합니다.

💡 서브쿼리의 결과가 한 개의 값이 나올지, 여러 개의 값이 나올지 미리 확인하세요. WHERE 절에서는 보통 단일 값 비교에 사용됩니다.

💡 서브쿼리를 작성할 때는 먼저 서브쿼리만 따로 실행해보세요. 결과가 예상대로 나오는지 확인한 후 바깥쪽 쿼리와 합치면 디버깅이 쉬워집니다.

💡 성능이 중요하다면 서브쿼리 대신 JOIN을 사용할 수 있는지 검토해보세요. 경우에 따라 JOIN이 더 빠를 수 있습니다.

---

2. WHERE 절 서브쿼리

시작하며

여러분이 온라인 서점 데이터베이스에서 "베스트셀러 1위 책보다 비싼 책들을 모두 찾아야 하는" 상황이라고 상상해보세요. 먼저 베스트셀러 1위 책의 가격을 알아야 하고, 그 다음에 그보다 비싼 책을 찾아야 합니다.

이런 조건부 조회는 실무에서 가장 흔하게 사용됩니다. 특정 기준값을 먼저 찾고, 그 값을 기준으로 다른 데이터를 필터링해야 하는 경우가 매우 많기 때문입니다.

두 번의 쿼리로 나누면 데이터가 중간에 변경될 위험도 있습니다. 바로 이럴 때 WHERE 절 서브쿼리가 빛을 발합니다.

조건절 안에 서브쿼리를 넣어서 동적인 기준값을 만들고, 그 기준으로 데이터를 정확하게 필터링할 수 있습니다.

개요

간단히 말해서, WHERE 절 서브쿼리는 "조건을 비교할 기준값을 다른 쿼리로 찾아오는 것"입니다. 고정된 값(예: salary > 5000)이 아니라, 계산된 값(예: salary > 평균급여)을 사용할 수 있게 해줍니다.

왜 WHERE 절 서브쿼리가 필요할까요? 실무에서는 비교 기준이 항상 변하기 때문입니다.

예를 들어, "어제 가장 많이 팔린 상품보다 재고가 적은 상품", "우리 팀 평균 성과보다 높은 직원", "가장 최근 주문보다 오래된 미처리 주문" 같은 조건들은 모두 동적입니다. 이런 경우에 WHERE 절 서브쿼리는 필수입니다.

전통적인 방법에서는 먼저 기준값을 조회하고, 그 값을 변수에 저장한 후, 두 번째 쿼리에서 사용했습니다. 이제는 서브쿼리 하나로 모든 과정이 원자적으로 처리되어 데이터 일관성이 보장됩니다.

WHERE 절 서브쿼리의 핵심 특징은 두 가지입니다. 첫째, 서브쿼리는 반드시 단일 값을 반환해야 합니다(여러 값이면 IN이나 ANY 사용).

둘째, 비교 연산자(=, >, <, >=, <=, !=)와 함께 사용되어 조건을 만듭니다. 이러한 특징들이 동적이면서도 정확한 데이터 필터링을 가능하게 합니다.

코드 예제

-- WHERE 절 서브쿼리 실전 예제
-- 자기 부서의 평균 급여보다 많이 받는 직원 찾기
SELECT
    e.employee_name,
    e.salary,
    e.department,
    d.dept_name
FROM employees e
JOIN departments d ON e.department = d.dept_id
WHERE e.salary > (
    -- 서브쿼리: 해당 부서의 평균 급여
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department
)
ORDER BY e.department, e.salary DESC;

설명

이것이 하는 일: 위 코드는 각 부서에서 자기 부서의 평균보다 높은 급여를 받는 직원들을 찾습니다. 마치 각 반에서 그 반의 평균 점수보다 높은 학생들을 찾는 것과 같습니다.

첫 번째로, 바깥쪽 쿼리가 employees 테이블의 각 행을 하나씩 검사하기 시작합니다. 예를 들어 김철수 직원(개발부)의 행을 처리한다고 해봅시다.

왜 이렇게 하나씩 처리할까요? 각 직원마다 서브쿼리를 실행해서 그 직원의 부서 평균과 비교해야 하기 때문입니다.

그 다음으로, 김철수의 행을 처리할 때 서브쿼리가 실행됩니다. WHERE department = e.department에서 e.department는 김철수의 부서(개발부)를 가리킵니다.

따라서 서브쿼리는 개발부의 평균 급여만 계산합니다. 이렇게 바깥쪽 쿼리의 값을 참조하는 서브쿼리를 '상관 서브쿼리'라고 부릅니다.

세 번째 단계로, 서브쿼리가 반환한 개발부 평균 급여(예: 6000만원)와 김철수의 급여를 비교합니다. 김철수의 급여가 7000만원이라면 조건을 만족하므로 결과에 포함됩니다.

이 과정이 모든 직원에 대해 반복됩니다. 마지막으로, 조건을 만족하는 직원들이 부서별로 그룹화되고, 각 부서 내에서 급여가 높은 순서대로 정렬됩니다.

최종적으로 "부서 평균을 넘는 고성과자" 목록이 만들어집니다. 여러분이 이 코드를 사용하면 부서별 상대 평가를 쉽게 구현할 수 있습니다.

각 부서의 평균을 따로 계산하고 비교하는 복잡한 로직 없이, SQL 한 번으로 처리됩니다. 또한 인사 평가, 성과 분석, 이상치 탐지 등 다양한 실무 상황에 응용할 수 있습니다.

실전 팁

💡 서브쿼리가 여러 개의 값을 반환하면 에러가 발생합니다. 반드시 AVG, MAX, MIN 같은 집계 함수를 사용해서 단일 값을 보장하세요.

💡 상관 서브쿼리(바깥쪽 테이블을 참조하는 서브쿼리)는 성능이 느릴 수 있습니다. 바깥쪽 행마다 서브쿼리가 실행되기 때문입니다. 데이터가 많다면 JOIN이나 윈도우 함수를 고려하세요.

💡 서브쿼리가 NULL을 반환할 수 있다면 COALESCE를 사용해서 기본값을 지정하세요. 예: WHERE salary > COALESCE((SELECT AVG(salary)...), 0)

💡 디버깅할 때는 서브쿼리를 SELECT 절에도 넣어서 어떤 값과 비교하고 있는지 확인해보세요. 예상과 다른 결과가 나올 때 유용합니다.

💡 NOT EXISTS를 사용하면 "~가 없는" 조건을 쉽게 만들 수 있습니다. 예: "주문 이력이 없는 고객" 같은 조건에 활용하세요.

---

3. SELECT 절 스칼라 서브쿼리

시작하며

여러분이 직원 목록을 조회하면서 각 직원의 정보와 함께 "그 직원이 속한 부서의 전체 인원수"도 같이 보여줘야 하는 상황이라고 해봅시다. 각 직원마다 부서 인원을 따로 계산해야 할까요?

이런 요구사항은 실무 리포트에서 매우 흔합니다. 개별 데이터와 함께 관련된 집계 정보를 같이 보여줘야 하는 경우가 많습니다.

예를 들어 상품 목록에 각 상품의 총 판매량, 게시글 목록에 각 게시글의 댓글 수 같은 경우입니다. 바로 이럴 때 SELECT 절 스칼라 서브쿼리가 완벽한 해결책입니다.

결과의 각 행마다 계산된 값을 추가 컬럼으로 보여줄 수 있습니다.

개요

간단히 말해서, 스칼라 서브쿼리는 "SELECT 절에 들어가서 추가 컬럼처럼 동작하는 서브쿼리"입니다. 스칼라(scalar)는 "단일 값"을 의미하며, 각 행마다 하나의 값을 계산해서 반환합니다.

왜 SELECT 절 서브쿼리가 필요할까요? 실무에서는 기본 테이블의 데이터와 함께 계산된 정보를 함께 보여줘야 하는 경우가 많습니다.

예를 들어, "직원 목록 + 각 직원의 프로젝트 수", "상품 목록 + 각 상품의 평균 평점", "고객 목록 + 각 고객의 총 구매액" 같은 리포트를 만들 때 필수적입니다. 기존에는 JOIN을 사용하거나 여러 번의 쿼리로 데이터를 가져온 후 애플리케이션에서 조합했습니다.

이제는 스칼라 서브쿼리로 모든 정보를 한 번의 쿼리에서 가져올 수 있습니다. 특히 1:N 관계에서 N 쪽의 집계 정보를 가져올 때 유용합니다.

스칼라 서브쿼리의 핵심 특징은 세 가지입니다. 첫째, 반드시 단일 행, 단일 컬럼(하나의 값)을 반환해야 합니다.

둘째, SELECT 절에서 일반 컬럼처럼 사용되며 별칭을 붙일 수 있습니다. 셋째, 각 행마다 독립적으로 실행되어 서로 다른 값을 반환할 수 있습니다.

이러한 특징들이 복잡한 리포트를 간단하게 만들어줍니다.

코드 예제

-- SELECT 절 스칼라 서브쿼리 실전 예제
-- 부서 목록과 각 부서의 직원 수, 평균 급여 조회
SELECT
    d.dept_id,
    d.dept_name,
    d.location,
    -- 스칼라 서브쿼리: 각 부서의 직원 수
    (SELECT COUNT(*)
     FROM employees e
     WHERE e.department = d.dept_id) AS employee_count,
    -- 스칼라 서브쿼리: 각 부서의 평균 급여
    (SELECT AVG(salary)
     FROM employees e
     WHERE e.department = d.dept_id) AS avg_salary
FROM departments d
ORDER BY employee_count DESC;

설명

이것이 하는 일: 위 코드는 모든 부서의 기본 정보와 함께, 각 부서에 몇 명의 직원이 있고 평균 급여가 얼마인지를 한 번에 보여줍니다. 마치 학교의 각 반 정보와 함께 반별 학생 수, 평균 점수를 보는 것과 같습니다.

첫 번째로, 바깥쪽 쿼리가 departments 테이블의 첫 번째 부서(예: 개발부)를 읽습니다. 이때 dept_id, dept_name, location 같은 기본 정보를 가져옵니다.

그런데 employee_count와 avg_salary는 departments 테이블에 없는 컬럼입니다. 이 값들은 어떻게 채워질까요?

그 다음으로, 첫 번째 스칼라 서브쿼리 (SELECT COUNT(*) FROM employees...)가 실행됩니다. 이 서브쿼리는 현재 처리 중인 부서(개발부)에 속한 직원 수를 셉니다.

WHERE e.department = d.dept_id에서 d.dept_id는 바깥쪽 쿼리의 현재 부서를 가리킵니다. 예를 들어 개발부에 직원이 15명이라면, 15라는 값이 employee_count 컬럼에 들어갑니다.

세 번째 단계로, 두 번째 스칼라 서브쿼리 (SELECT AVG(salary)...)가 같은 방식으로 실행됩니다. 개발부 직원들의 평균 급여를 계산해서 avg_salary 컬럼에 채워 넣습니다.

예를 들어 평균이 6500만원이라면 이 값이 들어갑니다. 마지막으로, 이 과정이 모든 부서에 대해 반복됩니다.

각 부서마다 두 개의 서브쿼리가 실행되어 직원 수와 평균 급여를 계산합니다. 최종적으로 부서의 기본 정보와 집계 정보가 하나의 결과 테이블로 합쳐져서, 직원 수가 많은 부서 순서대로 정렬되어 출력됩니다.

여러분이 이 코드를 사용하면 대시보드나 리포트를 만들 때 매우 편리합니다. 한 번의 쿼리로 모든 정보를 가져올 수 있어서 애플리케이션 코드가 간단해집니다.

또한 각 부서의 상세 정보와 통계를 동시에 볼 수 있어서 의사결정에 도움이 됩니다. 실무에서는 고객별 주문 통계, 상품별 판매 실적, 게시글별 상호작용 지표 등 다양하게 활용할 수 있습니다.

실전 팁

💡 스칼라 서브쿼리는 반드시 단일 값을 반환해야 합니다. COUNT, AVG, MAX, MIN, SUM 같은 집계 함수를 사용하면 안전합니다.

💡 서브쿼리가 결과를 못 찾으면 NULL이 반환됩니다. 직원이 한 명도 없는 부서의 경우를 대비해 COALESCE로 0을 기본값으로 지정하세요.

💡 스칼라 서브쿼리가 많으면 성능이 느려질 수 있습니다. 바깥쪽 행마다 여러 번 실행되기 때문입니다. 대량 데이터라면 LEFT JOIN이나 윈도우 함수를 고려하세요.

💡 복잡한 스칼라 서브쿼리는 가독성을 위해 WITH 절로 분리하는 것이 좋습니다. 쿼리가 길어지면 유지보수가 어려워집니다.

💡 스칼라 서브쿼리에 인덱스를 활용하세요. WHERE 절에 사용되는 컬럼(department 같은)에 인덱스가 있으면 성능이 크게 향상됩니다.

---

4. FROM 절 인라인 뷰

시작하며

여러분이 "부서별 평균 급여가 5000만원 이상인 부서"만 찾아야 하는 상황이라고 생각해보세요. 문제는 WHERE 절에서는 집계 함수(AVG)를 직접 사용할 수 없다는 것입니다.

HAVING을 써야 할까요? 하지만 더 복잡한 조건이 필요하다면?

이런 문제는 집계 결과를 다시 필터링하거나 조인해야 할 때 발생합니다. 집계된 데이터를 마치 일반 테이블처럼 다루고 싶지만, SQL의 실행 순서 때문에 제약이 많습니다.

바로 이럴 때 FROM 절 인라인 뷰가 해결책이 됩니다. 서브쿼리의 결과를 임시 테이블처럼 만들어서 자유롭게 조회하고 조인할 수 있습니다.

개요

간단히 말해서, 인라인 뷰는 "FROM 절에 들어가는 서브쿼리로, 그 결과가 임시 테이블처럼 동작하는 것"입니다. 마치 엑셀에서 피벗 테이블을 만들고, 그 결과를 다시 다른 시트에서 참조하는 것과 비슷합니다.

왜 FROM 절 인라인 뷰가 필요할까요? 실무에서는 데이터를 단계적으로 가공해야 하는 경우가 많습니다.

예를 들어, "월별 매출을 먼저 집계하고, 그 중 1억 이상인 월만 선택해서 평균을 구하는" 같은 복잡한 분석을 할 때입니다. 또한 여러 테이블의 집계 결과를 서로 조인해야 할 때도 필수입니다.

전통적인 방법에서는 임시 테이블을 CREATE하거나, 뷰를 만들거나, 여러 번의 쿼리로 나눠서 처리했습니다. 이제는 인라인 뷰 하나로 복잡한 다단계 처리를 하나의 쿼리에서 해결할 수 있습니다.

코드도 간결하고 성능도 좋습니다. 인라인 뷰의 핵심 특징은 네 가지입니다.

첫째, FROM 절에 위치하며 괄호로 감싸고 반드시 별칭을 붙여야 합니다. 둘째, 서브쿼리의 결과가 마치 테이블처럼 동작해서 JOIN, WHERE 등을 자유롭게 사용할 수 있습니다.

셋째, 집계된 데이터를 다시 필터링하거나 조인할 수 있습니다. 넷째, 복잡한 쿼리를 논리적 단계로 나눠서 가독성을 높입니다.

이러한 특징들이 복잡한 데이터 분석을 가능하게 만듭니다.

코드 예제

-- FROM 절 인라인 뷰 실전 예제
-- 부서별 통계를 먼저 계산하고, 평균 급여가 높은 부서 정보 조회
SELECT
    d.dept_name,
    d.location,
    stats.emp_count,
    stats.avg_salary,
    stats.max_salary
FROM departments d
JOIN (
    -- 인라인 뷰: 부서별 통계를 임시 테이블처럼 생성
    SELECT
        department,
        COUNT(*) AS emp_count,
        AVG(salary) AS avg_salary,
        MAX(salary) AS max_salary
    FROM employees
    GROUP BY department
) stats ON d.dept_id = stats.department
WHERE stats.avg_salary >= 5000
ORDER BY stats.avg_salary DESC;

설명

이것이 하는 일: 위 코드는 먼저 각 부서의 통계(직원 수, 평균 급여, 최고 급여)를 계산하고, 그 중에서 평균 급여가 5000만원 이상인 부서들만 선택해서 부서 상세 정보와 함께 보여줍니다. 마치 먼저 반별 성적표를 만들고, 그 중 평균이 높은 반들만 골라서 자세히 보는 것과 같습니다.

첫 번째로, 괄호 안의 인라인 뷰(서브쿼리)가 실행됩니다. SELECT department, COUNT(*), AVG(salary), MAX(salary) FROM employees GROUP BY department는 employees 테이블의 모든 직원을 부서별로 그룹화하고, 각 그룹의 통계를 계산합니다.

결과는 마치 "부서별_통계"라는 임시 테이블이 만들어진 것처럼 동작합니다. 이 임시 테이블에는 department, emp_count, avg_salary, max_salary 네 개의 컬럼이 있습니다.

그 다음으로, 이 인라인 뷰에 'stats'라는 별칭이 붙습니다. 별칭은 필수입니다.

왜냐하면 바깥쪽 쿼리에서 이 임시 테이블을 참조할 때 이름이 필요하기 때문입니다. 이제 stats를 마치 실제 테이블처럼 JOIN, WHERE 등에서 사용할 수 있습니다.

세 번째 단계로, departments 테이블과 stats 인라인 뷰가 조인됩니다. `JOIN ...

ON d.dept_id = stats.department`는 부서의 기본 정보(이름, 위치)와 통계 정보를 연결합니다. 예를 들어 개발부의 dept_name, location과 개발부의 emp_count, avg_salary가 하나의 행으로 합쳐집니다.

마지막으로, WHERE stats.avg_salary >= 5000 조건이 적용됩니다. 집계된 결과(avg_salary)를 조건으로 사용할 수 있는 이유는 인라인 뷰가 이미 집계를 완료했기 때문입니다.

조건을 만족하는 부서들만 선택되고, 평균 급여가 높은 순서대로 정렬되어 최종 결과가 출력됩니다. 여러분이 이 코드를 사용하면 복잡한 분석 리포트를 만들 때 매우 유용합니다.

집계 결과를 다시 필터링하고, 원본 테이블의 상세 정보와 결합할 수 있습니다. 실무에서는 "매출 상위 10개 상품의 상세 정보", "활동이 많은 사용자의 프로필", "성과가 좋은 팀의 구성원" 같은 분석에 활용할 수 있습니다.

또한 여러 집계 결과를 서로 조인해서 더 복잡한 분석도 가능합니다.

실전 팁

💡 인라인 뷰에는 반드시 별칭(AS 또는 공백 후 이름)을 붙여야 합니다. 별칭 없이는 문법 오류가 발생합니다.

💡 인라인 뷰 안에서 필요한 컬럼만 선택하세요. 불필요한 컬럼까지 포함하면 메모리와 처리 시간이 낭비됩니다.

💡 복잡한 인라인 뷰는 WITH 절(CTE)로 바꾸면 가독성이 훨씬 좋아집니다. 특히 인라인 뷰를 여러 번 참조할 때 유용합니다.

💡 인라인 뷰의 결과에 인덱스는 없습니다. 성능이 중요하다면 결과 크기를 줄이고, 가능하면 조인 조건에 사용되는 컬럼을 바깥쪽에서 인덱스로 활용하세요.

💡 인라인 뷰를 중첩해서 사용할 수 있지만, 너무 깊게 중첩하면 읽기 어려워집니다. 2단계를 넘어가면 WITH 절 사용을 고려하세요.

---

5. IN과 EXISTS 서브쿼리

시작하며

여러분이 "최근 한 달 안에 주문한 적이 있는 고객"을 찾아야 하는 상황이라고 해봅시다. 주문 테이블에는 수천 개의 주문이 있고, 같은 고객이 여러 번 주문했을 수도 있습니다.

어떻게 중복 없이 고객 목록을 찾을까요? 이런 "존재 여부" 확인은 실무에서 매우 흔합니다.

"결제 완료된 주문이 있는 고객", "프로젝트에 참여 중인 직원", "리뷰가 있는 상품" 같은 조건들은 모두 다른 테이블의 데이터 존재 여부를 확인해야 합니다. 바로 이럴 때 IN과 EXISTS 서브쿼리가 완벽한 해결책입니다.

두 방법 모두 "~에 속하는" 또는 "~가 존재하는" 조건을 쉽게 표현할 수 있습니다.

개요

간단히 말해서, IN 서브쿼리는 "값이 서브쿼리 결과 목록에 포함되는지 확인"하고, EXISTS 서브쿼리는 "조건을 만족하는 행이 존재하는지 확인"합니다. 두 방법은 비슷해 보이지만 작동 방식과 성능이 다릅니다.

왜 IN과 EXISTS가 필요할까요? 실무에서는 두 테이블 사이의 관계를 확인하는 경우가 매우 많습니다.

예를 들어, "A를 한 사람", "B가 있는 항목", "C에 해당하는 데이터" 같은 조건들입니다. JOIN을 사용할 수도 있지만, 중복 문제나 복잡한 조건 때문에 서브쿼리가 더 명확하고 안전한 경우가 많습니다.

전통적인 방법에서는 JOIN을 사용하고 DISTINCT로 중복을 제거했습니다. 이제는 IN이나 EXISTS로 더 직관적으로 표현할 수 있습니다.

특히 NOT IN, NOT EXISTS를 사용하면 "~가 없는" 조건도 쉽게 만들 수 있습니다. IN과 EXISTS의 핵심 차이는 세 가지입니다.

첫째, IN은 값 목록과 비교하고, EXISTS는 행의 존재 여부만 확인합니다. 둘째, IN은 서브쿼리가 모든 결과를 반환하지만, EXISTS는 첫 번째 매칭을 찾으면 즉시 멈춥니다.

셋째, EXISTS는 상관 서브쿼리로 사용되며, 바깥쪽 테이블과 연결됩니다. 이러한 차이가 상황에 따라 성능과 정확성에 영향을 줍니다.

코드 예제

-- IN과 EXISTS 비교 예제
-- 방법 1: IN 서브쿼리 - 주문한 적 있는 고객 찾기
SELECT customer_id, customer_name, email
FROM customers
WHERE customer_id IN (
    -- 주문 테이블에서 모든 고객 ID 목록 가져오기
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
);

-- 방법 2: EXISTS 서브쿼리 - 같은 결과, 다른 방식
SELECT c.customer_id, c.customer_name, c.email
FROM customers c
WHERE EXISTS (
    -- 각 고객마다 최근 주문이 있는지 확인
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
);

설명

이것이 하는 일: 두 쿼리 모두 최근 30일 안에 주문한 적이 있는 고객들의 정보를 찾습니다. 결과는 같지만 내부적으로 다르게 동작합니다.

마치 같은 목적지에 가는 두 가지 다른 경로와 같습니다. 첫 번째 방법(IN)의 동작 방식입니다.

먼저 서브쿼리가 완전히 실행되어 최근 30일간 주문한 모든 고객 ID를 수집합니다. 예를 들어 [101, 102, 105, 107] 같은 목록이 만들어집니다.

DISTINCT를 사용한 이유는 같은 고객이 여러 번 주문했을 수 있기 때문입니다. 그 다음, customers 테이블의 각 행을 읽으면서 customer_id가 이 목록에 있는지 확인합니다.

목록에 있으면 결과에 포함됩니다. 두 번째 방법(EXISTS)의 동작 방식입니다.

customers 테이블의 각 고객(예: 김철수, ID 101)을 하나씩 처리합니다. 김철수를 처리할 때 서브쿼리가 실행되는데, WHERE o.customer_id = c.customer_id에서 c는 바깥쪽의 김철수를 가리킵니다.

따라서 "김철수의 최근 30일 주문이 있는가?"를 확인합니다. 중요한 점은 EXISTS는 결과가 하나라도 있으면 즉시 TRUE를 반환하고 멈춘다는 것입니다.

모든 주문을 다 찾을 필요가 없습니다. 성능 차이를 이해해봅시다.

IN 방식은 서브쿼리가 모든 결과를 먼저 수집해야 합니다. 주문이 수만 건이면 메모리에 큰 목록을 만들어야 합니다.

반면 EXISTS는 각 고객마다 조건을 만족하는 행을 하나만 찾으면 되므로, 인덱스가 있다면 매우 빠릅니다. 특히 한 고객이 여러 번 주문한 경우 EXISTS가 훨씬 효율적입니다.

마지막으로 NULL 처리의 차이도 중요합니다. IN은 NULL 값이 있으면 예상치 못한 결과를 낼 수 있습니다.

특히 NOT IN에서 서브쿼리 결과에 NULL이 하나라도 있으면 전체 결과가 빈 집합이 됩니다. 반면 EXISTS는 NULL에 영향을 받지 않아 더 안전합니다.

여러분이 이 코드를 사용할 때는 상황에 맞게 선택하세요. 서브쿼리 결과가 작고 명확하다면 IN이 더 읽기 쉽습니다.

하지만 대량 데이터나 복잡한 조건, 특히 "~가 없는" 조건(NOT EXISTS)에는 EXISTS가 더 안전하고 빠릅니다. 실무에서는 "활동한 사용자", "재고가 있는 상품", "완료되지 않은 작업" 같은 조건에 활용할 수 있습니다.

실전 팁

💡 NOT IN 사용 시 주의하세요. 서브쿼리에 NULL이 있으면 결과가 항상 빈 집합입니다. NOT EXISTS를 사용하는 것이 더 안전합니다.

💡 EXISTS 서브쿼리에서는 SELECT 절에 뭘 써도 상관없습니다. SELECT 1, SELECT * 모두 같은 결과입니다. 존재 여부만 확인하기 때문입니다.

💡 대량 데이터에서는 EXISTS가 보통 더 빠릅니다. 특히 서브쿼리 테이블에 적절한 인덱스가 있을 때 성능 차이가 큽니다.

💡 IN은 서브쿼리 대신 값 목록을 직접 쓸 수 있습니다. WHERE status IN ('완료', '진행중', '대기') 같은 식으로 고정된 값을 확인할 때 편리합니다.

💡 복잡한 조건에는 EXISTS가 더 유연합니다. 여러 컬럼을 동시에 비교하거나, AND/OR 조건을 자유롭게 추가할 수 있습니다.

---

6. 상관 서브쿼리와 성능 고려사항

시작하며

여러분이 "각 부서에서 급여가 가장 높은 직원"을 찾아야 하는 상황이라고 해봅시다. 단순히 전체 최고 급여자가 아니라, 부서마다 1등을 찾아야 합니다.

이런 "그룹별 최상위" 문제는 어떻게 해결할까요? 이런 요구사항은 실무에서 매우 흔하지만, 처리하기 까다롭습니다.

"카테고리별 인기 상품", "팀별 최고 성과자", "월별 최대 매출일" 같은 분석을 할 때마다 마주치는 문제입니다. 잘못 작성하면 성능이 크게 떨어질 수 있습니다.

바로 이럴 때 상관 서브쿼리가 강력한 도구가 되지만, 동시에 성능 함정도 될 수 있습니다. 언제 사용하고 언제 피해야 하는지 아는 것이 중요합니다.

개요

간단히 말해서, 상관 서브쿼리는 "바깥쪽 쿼리의 각 행마다 다른 값으로 실행되는 서브쿼리"입니다. 마치 반복문처럼 바깥쪽 행마다 서브쿼리가 반복 실행되며, 바깥쪽 테이블의 값을 참조합니다.

왜 상관 서브쿼리를 이해해야 할까요? 앞에서 본 많은 예제들(SELECT 절 스칼라 서브쿼리, EXISTS 등)이 사실 상관 서브쿼리입니다.

매우 강력하고 표현력이 좋지만, 성능 문제의 주범이기도 합니다. 바깥쪽 행이 10만 개라면 서브쿼리도 10만 번 실행되기 때문입니다.

언제 사용하고 언제 대안을 찾아야 하는지 아는 것이 실무에서 매우 중요합니다. 전통적인 방법에서는 상관 서브쿼리를 무조건 피했습니다.

하지만 최신 데이터베이스는 쿼리 옵티마이저가 발전해서 자동으로 최적화하는 경우도 많습니다. 또한 윈도우 함수나 LATERAL JOIN 같은 대안도 있습니다.

상관 서브쿼리의 핵심 특징과 주의사항은 다섯 가지입니다. 첫째, 바깥쪽 테이블의 컬럼을 참조하므로 독립적으로 실행할 수 없습니다.

둘째, 바깥쪽 행마다 반복 실행되므로 데이터가 많으면 느려질 수 있습니다. 셋째, 인덱스가 매우 중요합니다.

서브쿼리의 WHERE 절에 사용되는 컬럼에 인덱스가 있어야 합니다. 넷째, 때로는 JOIN이나 윈도우 함수가 더 빠른 대안입니다.

다섯째, EXPLAIN PLAN으로 실행 계획을 확인해야 합니다. 이러한 이해가 성능 좋은 쿼리를 작성하는 핵심입니다.

코드 예제

-- 상관 서브쿼리 예제와 최적화 비교
-- 방법 1: 상관 서브쿼리 - 각 부서의 최고 급여자 찾기
SELECT e1.employee_name, e1.department, e1.salary
FROM employees e1
WHERE e1.salary = (
    -- 상관 서브쿼리: e1의 부서에서 최고 급여 찾기
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

-- 방법 2: 윈도우 함수 - 더 효율적인 대안
SELECT employee_name, department, salary
FROM (
    SELECT
        employee_name,
        department,
        salary,
        -- 윈도우 함수: 부서별로 급여 순위 매기기
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
    FROM employees
) ranked
WHERE rank = 1;

설명

이것이 하는 일: 두 쿼리 모두 각 부서에서 가장 높은 급여를 받는 직원을 찾습니다. 결과는 같지만 성능은 크게 다를 수 있습니다.

마치 같은 목적지를 도보와 자동차로 가는 차이와 같습니다. 첫 번째 방법(상관 서브쿼리)의 동작 방식입니다.

employees 테이블의 첫 번째 직원(예: 김철수, 개발부, 7000만원)을 읽습니다. 그 다음 서브쿼리 SELECT MAX(e2.salary) FROM employees e2 WHERE e2.department = e1.department가 실행됩니다.

여기서 e1.department는 김철수의 부서(개발부)를 가리킵니다. 따라서 서브쿼리는 "개발부의 최고 급여"를 계산합니다.

예를 들어 8000만원이 반환됩니다. 김철수의 급여(7000만원)가 최고 급여(8000만원)와 같지 않으므로 결과에 포함되지 않습니다.

이 과정이 모든 직원(예: 1000명)에 대해 반복됩니다. 성능 문제를 이해해봅시다.

직원이 1000명, 부서가 10개라면 서브쿼리는 1000번 실행됩니다. 각 실행마다 employees 테이블을 스캔해서 MAX를 계산합니다.

만약 department 컬럼에 인덱스가 없다면 매번 전체 테이블 스캔이 발생합니다. 1000명 × 1000명 = 100만 번의 행 읽기가 발생할 수 있습니다.

하지만 department에 인덱스가 있다면 각 서브쿼리가 해당 부서 행만 빠르게 찾을 수 있어 성능이 크게 향상됩니다. 두 번째 방법(윈도우 함수)의 동작 방식입니다.

먼저 내부 쿼리가 실행되어 모든 직원을 부서별로 나누고(PARTITION BY department), 각 부서 내에서 급여 순으로 정렬합니다(ORDER BY salary DESC). 그 다음 ROW_NUMBER()가 각 직원에게 부서 내 순위를 매깁니다.

1위가 최고 급여자입니다. 중요한 점은 이 모든 과정이 한 번의 테이블 스캔으로 완료된다는 것입니다.

바깥쪽 쿼리는 단순히 rank = 1인 행만 필터링합니다. 성능 비교를 해봅시다.

윈도우 함수는 테이블을 한 번만 읽고, 정렬 한 번으로 모든 부서의 순위를 매깁니다. 직원이 1000명이면 1000번의 행 읽기면 충분합니다.

상관 서브쿼리보다 훨씬 효율적입니다. 단, 윈도우 함수는 정렬이 필요하므로 메모리를 사용합니다.

대부분의 경우 윈도우 함수가 더 빠르지만, 데이터 특성에 따라 다를 수 있으므로 EXPLAIN으로 실행 계획을 확인하는 것이 중요합니다. 여러분이 상관 서브쿼리를 사용할 때는 이런 점들을 체크하세요.

첫째, 서브쿼리의 WHERE 절에 사용되는 컬럼에 인덱스가 있는가? 둘째, 바깥쪽 테이블의 행 수가 많지 않은가?

셋째, 윈도우 함수나 JOIN으로 대체할 수 있는가? 실무에서는 EXPLAIN PLAN을 실행해서 실제 성능을 측정하고, 필요하면 리팩토링하는 것이 좋습니다.

"그룹별 최상위", "카테고리별 인기 항목", "기간별 최대값" 같은 분석에 이 지식을 활용할 수 있습니다.

실전 팁

💡 상관 서브쿼리의 성능은 인덱스에 달려있습니다. WHERE 절에 사용되는 컬럼에 반드시 인덱스를 만드세요.

💡 EXPLAIN PLAN 또는 EXPLAIN ANALYZE로 실행 계획을 확인하세요. 예상보다 많은 행을 스캔하고 있다면 최적화가 필요합니다.

💡 "그룹별 최상위 N개" 문제는 윈도우 함수가 거의 항상 더 빠릅니다. ROW_NUMBER, RANK, DENSE_RANK를 활용하세요.

💡 상관 서브쿼리를 디버깅할 때는 서브쿼리를 SELECT 절에 추가해서 어떤 값과 비교하고 있는지 확인해보세요.

💡 최신 데이터베이스는 상관 서브쿼리를 자동으로 JOIN으로 변환하기도 합니다. 하지만 명시적으로 최적화된 쿼리를 작성하는 것이 더 안전합니다.

---