1.8Subquery

서브쿼리(Subquery)란, 하나의 SQL 문 안에 또 다른 쿼리를 중첩해서 사용하는 것을 말한다. 복잡한 조건이나 값을 계산해서 한 줄로 표현하고 싶을 때 쓰인다.

예를 들어, 'Toys' department의 모든 제품들보다 더 비싼 제품들의 이름과 가격을 보여주는 쿼리를 조회하고 싶다고 하자. 이 문제는 다음과 같이 2단계로 해결할 수 있다.

sql
-- 1단계: Toys 부서에서 가장 비싼 가격 찾기
SELECT MAX(price)
FROM products
WHERE department = 'Toys';

sql
-- 2단계: 해당 가격보다 비싼 가격 찾기
SELECT name, price
FROM products
WHERE price > (앞선 쿼리 결과값);

이런 식으로 쿼리의 결과를 또 다른 쿼리 안에 넣는 방식이 바로 서브쿼리다.

sql
SELECT name, price
FROM products
WHERE price > (
	SELECT MAX(price) FROM products
	WHERE department = 'Toys'
);

• 값이 쿼리로 계산이 필요한 곳에 ( )를 넣고 안에 쿼리를 넣는다.
• 서브쿼리 안에는 ;를 쓰지 않는다.

서브쿼리의 핵심: "결과 모양" 이해하기

서브쿼리는 쓰일 수 있는 곳이 조회하고자 하는 데이터 모수, 조회 결과 값, 조회 조건 등 쿼리의 모든 곳으로 아주 다양하다. 그 자리에서 무슨 값을 기대하는지(single value인지, row set인지 등)에 따라 구성 방식이 달라진다.

sql
SELECT p1.name, 
  -- Value로 쓰이는 서브쿼리
	(SELECT COUNT(name) FROM products)
-- 테이블(row set)로 쓰이는 서브쿼리
FROM (SELECT * FROM products) AS p1
JOIN (SELECT * FROM products) AS p2 ON p1.id = p2.id
-- 조건에 쓰이는 서브쿼리
WHERE p1.id IN (SELECT id FROM products);

안에 들어가는 Inner Query가 많은 열의 많은 행을 가지는 결과를 가져오는지, 하나의 값을 가져올지 등을 이해해야 한다.

SELECT 절 안의 서브쿼리

단일 값(Single Value)을 가져오는 서브쿼리를 SELECT 절에 넣을 수 있다.

sql
SELECT name, price,(
	SELECT MAX(price) FROM products
)
FROM products
WHERE price > 867;

• 모든 행에 대해 MAX(price) 값을 함께 보여준다.
• 여러 행을 반환하는 서브쿼리는 넣을 수 없다.

FROM 절 안의 서브쿼리

다양한 구조의 데이터를 반환하는 서브쿼리가 모두 가능하다.

1. 테이블처럼 쓰이는 서브쿼리

(참고로, 아래 예시는 반드시 필요한 상황에서 쓴 서브쿼리가 아니라 예시일 뿐이다.)

sql
SELECT name, price_weight_ratio
FROM (
	SELECT name, 
	price / weight AS price_weight_ratio
	FROM products
) AS p
WHERE price_weight_ratio > 5;

• 반드시 반환된 데이터를 alias로 재명명한 AS 구문을 포함해야 한다.
• SELECT에서 사용할 수 있는 컬럼은 안쪽 쿼리에서 정의된 컬럼만이다.
  • 위의 예시에선 name, price_weight_ratio가 서브쿼리에서 정의되었기 때문에 밖에선 이 두 컬럼만 조회할 수 있다.

2. 단일 값만 반환하는 경우

sql
SELECT *
FROM (
	SELECT MAX(price) FROM products
) AS p;

JOIN 절 안의 서브쿼리

sql
SELECT first_name
FROM users
JOIN (
	SELECT user_id FROM orders
	WHERE product_id = 3
) AS o
ON o.user_id = users.id;

• 역시 AS로 별칭을 반드시 붙여야 한다.
• 동적으로 JOIN 조건을 줄 때 유용하다.

WHERE 절 안의 서브쿼리

앞선 용례들보다 훨씬 자주 쓰이는 용례다.

sql
SELECT id
FROM orders
WHERE product_id IN (
	SELECT id
	FROM products
	WHERE price / weight > 5;
);

• 서브쿼리의 반환 형태에 따라 사용 가능한 연산자가 달라진다:

원하는 필터가 어떤 조건이냐에 따라 사용하는 연산자가 다를 것이고, 이에 따라 서브쿼리의 반환 형태도 다를 것이다.

새로운 WHERE 연산자: ALL, SOME, ANY

ALL, SOME 등 집합 비교 연산자로 서브쿼리의 결과를 조건으로 활용할 수 있다.

• < ALL, ≥SOME, ≥ANY, <>ALL 등

sql
SELECT name, department, price
FROM products
WHERE price > ALL (
	SELECT price
	FROM products
	WHERE department = 'Industrial'
);

SOME(=ANY)

sql
SELECT name, department, price
FROM products
WHERE price > SOME (
  SELECT price
  FROM products
  WHERE department = 'Industrial'
);

• SOME은 사실상 ANY의 alias다.(상호대체 가능)

Correlated Subqueries (상관 서브쿼리)

Outer Query(바깥 쿼리)의 값을 Subquery(안쪽 쿼리)가 참조하는 경우.

예: 각 부서에서 가장 비싼 상품의 이름, 부서, 가격을 조회

sql
SELECT name, department, price
FROM products AS p1
WHERE p1.price = (
	SELECT MAX(price)
	FROM products AS p2
	WHERE p1.department = p2.department
)

FROM 없이 SELECT

FROM을 쓰지 않고, subquery를 대신 쓰는 구문이다.

sql
SELECT (
	SELECT MAX(price) FROM products
);

• 위의 예시만 보면 쓸 일이 없어보이지만, 실제로는 비율 등 단일 값을 계산할 때 유용하다.

sql
SELECT (
	SELECT MAX(price) FROM products
) / (
	SELECT AVG(price) FROM products
)

또, 여러 값들을 나열하는데 쓰임