쿼리 안의 쿼리: 서브쿼리

값 하나를 돌려주는 서브쿼리

값 하나가 필요한 자리에는 한 행, 한 컬럼을 반환하는 서브쿼리를 넣을 수 있습니다.

SELECT name,
       price,
       ROUND((SELECT AVG(price) FROM products), 2) AS average_price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC
LIMIT 20;

(SELECT AVG(price) FROM products)는 숫자 하나를 만들고, 바깥 쿼리는 그 숫자를 상수처럼 사용합니다. WHERE price > AVG(price)라고 바로 쓸 수 없는 이유도 여기에 있습니다. WHERE가 평가될 때는 집계값이 아직 없지만, 서브쿼리는 먼저 끝나므로 그 결과를 비교에 쓸 수 있습니다.

목록을 돌려주는 서브쿼리

SELECT first_name, last_name, email, country
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE status = 'refunded'
)
ORDER BY last_name
LIMIT 20;

안쪽 쿼리가 고객 ID 목록을 만들고, IN이 바깥 행의 ID가 그 목록에 있는지 검사합니다. 즉 환불된 주문을 한 고객만 찾는 쿼리입니다.

EXISTS: 맞는 게 하나라도 있는지 묻기

SELECT c.first_name, c.last_name, c.country
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.total > 1000
)
ORDER BY c.last_name
LIMIT 20;

EXISTS는 서브쿼리가 한 행이라도 반환하면 참이 됩니다. SELECT 1은 선택한 값이 아니라 행의 존재만 확인한다는 뜻을 드러내는 관례입니다.

이 서브쿼리는 바깥 쿼리의 c.id를 참조하므로 상관(correlated) 서브쿼리입니다. 한 번 만든 목록을 비교하는 대신, 고객마다 “연관된 행이 있는가”를 다시 묻습니다. 이런 존재 여부를 확인할 때 가장 읽기 쉬운 형태입니다.

NOT IN 함정

직원 120명 가운데 110명에게는 직접 보고하는 직원이 없습니다. 아래 쿼리는 이 110명을 찾으려는 시도입니다.

-- Employees nobody reports to. 110 of the 120 qualify,
-- and this query finds none of them.
SELECT first_name, last_name, position
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

하지만 결과는 오류 없이 0행으로 나옵니다.

manager_id는 nullable이고, 조직도 맨 위 열 명은 거기가 NULL입니다. 그래서 안쪽 쿼리는 NULL이 섞인 목록을 돌려주고, x NOT IN (1, 2, NULL)은 "x가 1과 다르고, 2와 다르고, 이 알 수 없는 값과도 다른가?"를 묻게 됩니다. 마지막 부분이 모름이니 전체가 모름이 되고, 그래서 어떤 x에 대해서도 행이 남지 않습니다. 목록 어딘가에 NULL이 하나만 있어도 결과가 통째로 비어버립니다.

IN은 목록에 NULL이 있어도 실제 값과 일치하는 행을 반환합니다. 반면 NOT IN은 모든 비교가 참이어야 하므로 NULL 하나만 있어도 결과를 판단할 수 없게 됩니다.

서브쿼리에 WHERE manager_id IS NOT NULL을 넣어도 고칠 수 있습니다. 다만 NOT EXISTS는 NULL을 비교하지 않으므로, 이런 조건을 빼먹을 가능성이 없어 보통 더 안전합니다.

SELECT e.first_name, e.last_name, e.position
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM employees m
  WHERE m.manager_id = e.id
)
ORDER BY e.last_name
LIMIT 20;

NOT EXISTS는 값이 아니라 행의 존재를 묻기 때문에 NULL이 비교에 끼어들 자리가 없습니다. 기본값을 NOT EXISTS로 잡아두면 조용히 틀리는 답 한 부류가 통째로 사라집니다.

테이블로 쓰이는 서브쿼리

여러 행과 여러 컬럼을 반환하는 쿼리는 FROM 절에서 테이블처럼 사용할 수 있습니다. 이를 파생 테이블이라고 합니다.

SELECT category, products, average
FROM (
  SELECT cat.name AS category,
         COUNT(*) AS products,
         ROUND(AVG(p.price), 2) AS average
  FROM products p
  JOIN categories cat ON cat.id = p.category_id
  GROUP BY cat.id
)
WHERE products >= 10
ORDER BY average DESC;

집계 결과를 기준으로 다시 필터링할 때 사용할 수 있는 방식입니다. 안쪽 쿼리에서 그룹을 만들고, 바깥 쿼리에서 그 결과를 일반 테이블처럼 필터링합니다. HAVING 대신 별칭으로 조건을 작성할 수 있다는 장점도 있습니다.

파생 테이블은 중첩할 수 있지만 깊어질수록 읽기 어렵습니다. 다음 레슨에서는 이를 위에서 아래로 읽을 수 있는 CTE로 바꿉니다.

행마다 서브쿼리 하나

SELECT p.name,
       p.price,
       (SELECT COUNT(*) FROM reviews r WHERE r.product_id = p.id) AS reviews,
       (SELECT ROUND(AVG(r.rating), 2) FROM reviews r WHERE r.product_id = p.id) AS avg_rating
FROM products p
ORDER BY avg_rating DESC, reviews DESC
LIMIT 20;

여기까지로 할 수 있는 것

다음 레슨에서는 중첩된 쿼리를 단계별로 읽기 쉽게 만드는 CTE와, 행을 합치지 않고 계산하는 윈도우 함수를 살펴봅니다.