[DB] 서브쿼리

개바리여리 2025. 6. 19. 22:21

서브쿼리란 다른 쿼리 내부에 포함되는 부수적인 쿼리 같은 개념이다.

메인 쿼리(Main Query) 내부에 중첩되어 실행되는 쿼리

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

이렇게 메인 쿼리내부에 부수적인 쿼리가 들어가는 개념이다.

서브쿼리는

쿼리를 구조화 시켜서 각 부분을 명확히 구분이 가능하다.

또한 복잡한 SQL문을 작성할때 보다 더 간결한 가독성을 가진 쿼리를 작성할 수 있게 된다.

서브쿼리의 종류

스칼라 서브쿼리	결과가 1개의 값인 서브쿼리 (예: SELECT (SELECT MAX(age) FROM users) )
단일 행 서브쿼리	결과가 1개의 행인 서브쿼리 (예: WHERE salary = (SELECT MAX(salary) FROM emp) )
다중 행 서브쿼리	결과가 여러 행일 수 있는 서브쿼리 (예: WHERE dept_id IN (SELECT id FROM dept) )
다중 열 서브쿼리	결과가 2개 이상의 열인 서브쿼리 (예: WHERE (id, name) IN (SELECT id, name FROM ...) )
상관 서브쿼리	서브쿼리가 메인 쿼리의 값을 참조할 때 (예: WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id) )

위치에 따른 종류

중첩 서브쿼리

where 문에 위치하는 서브쿼리

SELECT name
FROM employees
WHERE dept_id = (
    SELECT id
    FROM departments
    WHERE name = 'IT'
);

T 부서 소속 직원만 조회하는 중첩 서브쿼리이다.

SELECT product_name
FROM products
WHERE price > ALL (SELECT price FROM out_of_stock_products);

상품 중 서브쿼리에서 반환된 모든 재고 없는 상품의 가격보다 클 경우에만 해당 상품을 선택합니다. 즉, 재고 없는 상품 중 가장 비싼 상품보다도 더 비싼 상품만 선택

All -> 서브쿼리가 반환하는 모든 값과 비교해서 조건이 참인 경우를 찾을 때 씁니다.

인라인 뷰

FROM 절에 쓰이는 서브쿼리 반드시 별칭(alias) 붙여야 함

서브쿼리 결과를 임시 테이블처럼 사용

SELECT dept_id, avg_salary
FROM (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) AS sub;

스칼라 서브쿼리

select절에서 사용되는 서브쿼리 항상 1개의 값만 반환해야 함 (스칼라값)

딴 테이블에서 어떠한 값을 가져올때 쓰임

SELECT name,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

서브쿼리 사용 시 주의할 점

1. 비교연산자 사용시에 서브쿼리의 결과값이 1개 값만 나와여 정확한 데이터를 추출가능하다.

-- 올바름
WHERE salary = (SELECT MAX(salary) FROM employees)

-- 오류 발생 (결과가 2개 이상이면)
WHERE salary = (SELECT salary FROM employees WHERE dept_id = 10)

2. 서브쿼리의 비효율적 사용으로 인덱스 무력화

SELECT name
FROM users
WHERE id NOT IN (
  SELECT user_id
  FROM blacklist
);
--blacklist.user_id에 NULL이 1개라도 포함되면 → users.id NOT IN (...) 조건이 전체적으로 false로 계산 불가
--그래서 DB는 모든 행에 대해 비교하며 인덱스를 포기함 → 풀 테이블 스캔 발생

아래와 같이 수정될 수 있다.

-- LEFT JOIN + IS NULL 패턴
SELECT u.name
FROM users u
LEFT JOIN blacklist b ON u.id = b.user_id
WHERE b.user_id IS NULL;

이 방식은 user_id에 대하여 인덱스가 있을경우 인덱스 탐색이 가능해진다.

또한 아래와 같은 쿼리는 성능이 기하급수적으로 안좋아질 수 있다.

SELECT name
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE dept_id = e.dept_id
);

상관 서브쿼리(Correlated Subquery) 인하여

바깥 쿼리의 e.dept_id 값이 안쪽 서브쿼리로 전달되어, 바깥 쿼리의 각 행마다 서브쿼리가 다시 실행됩니다. 즉, 직원 수가 많으면 많을수록 서브쿼리가 반복적으로 실행되면서 성능 저하를 일으킬 수 있습니다.

아래와 같이 수정한다면 성능이 좋아질 수 있다.

-- WITH절(CTE) 사용
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept_id
)
SELECT e.name
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;

with절을 통해 평균급여를 한번만 계산하여 전체 탐색은 JOIN + 필터 조건으로 최적화 가능

그리고 개인적으로 내가 생각했을때 유지보수 하면서 많이 봤던 상관 서브쿼리 중에서 select 절에서 쓰는 쿼리를 많이 봤다. 그러면 데이터 튜플만큼 서브쿼리가 실행되어 버린다.

SELECT name,
       (SELECT AVG(salary)
        FROM employees
        WHERE dept_id = e.dept_id) AS dept_avg
FROM employees e;

이런 것들은 e를 참조하기 때문에 e 의 튜플만큼 서브쿼리도 실행된다. 하지만

SELECT name,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

이런 경우는 성능이 괜찮을 수 있다.

왜냐하면 이건 스칼라 서브쿼리이다.

비상관 서브쿼리이기 때문에 (항상 같은 값이 나온다.) 그래서 한번만 실행하고 메모리에서 캐싱해서 사용이 가능하기 때문.