서브쿼리 | Notion

서브쿼리는 하나의 SELECT 쿼리 안에 포함된 또다른 SELECT 쿼리이다.

특정 컬럼의 평균값을 구해서 그 평균값보다 큰 데이터만 가져오는 것 처럼 쿼리 한번으로 힘든 경우 사용할 수 있다.

서브쿼리가 포함된 쿼리에서 가장 바깥의 쿼리는 메인쿼리라고 한다.

서브쿼리가 포함된 쿼리에서는 서브쿼리를 먼저 실행 후 메인쿼리를 실행하게 된다.

물론 쓰는 곳에 따라 다르며, SELECT 절이면 SELECT 직전에, WHERE 이면 WHERE 직전에 실행된다.

여기서도 논리적으로만 따졌을때이며, 물리적인 실행 순서는 옵티마이저의 결정에 따라 다를 수 있다.

스칼라 서브쿼리
- 가장 기본적인 서브쿼리로, 단일 컬럼, 단일 행 서브쿼리이다.
- 단일 컬럼과 단일 값을 비교할 때 사용할 수 있으며, 서브쿼리는 무조건 하나의 값만 반환해야 한다.
- 특정 주문을 한 고객과 같은 도시에 사는 모든 고객을 조회한다고 했을 때 아래와 같이 적을 수 있다.

-- 서브쿼리 없이 해결할 때
SELECT u.address
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id = 1;
-- 이 쿼리의 실행결과가 '서울' 일때
SELECT name, address
FROM users
WHERE address = '서울';
-- 이런식으로 두번 나눠서 쿼리를 하게 되면 첫번째 쿼리의 결과가 나와야 다음 쿼리가 가능해서 번거롭고,
-- 두 쿼리가 트랜잭션 없이 단독으로 실행된다면 두번째 쿼리가 부정확할수도 있다.

-- 서브쿼리로 해결할 때
SELECT name, address
FROM users
WHERE address = (SELECT u.address
								 FROM users u
								 JOIN orders o ON u.user_id = o.user_id
								 WHERE o.order_id = 1);
-- 이렇게 두개의 쿼리를 하나로 합쳐 애플리케이션과 DB 간의 통신 횟수를 줄여 성능상 이점을 얻을 수 있다.
-- 이런식으로 '=', '>', '<=' 같은 단일 값 비교 연산자들과 같이 사용하는 서브쿼리가 스칼라 서브쿼리이다.

다중 행 서브쿼리
- 단일 컬럼, 다중 값 서브쿼리이다.
- IN, ANY, ALL 같은 연산자와 함께 사용해 하나의 컬럼이 특정 값 목록 중 하나인지 검사할 수 있다.
- IN (값1, 값, …) : 괄호 안에 들어있는 값과 하나라도 일치하면 참이 된다. IN 앞에는 컬럼명을 적어준다.
- 예를 들어 IN (1, 2) 라면 1 혹은 2 를 포함하는 데이터 이라고 해석할 수 있다.
- ANY (값1, 값2, .. ): 기본적으로 IN 과 같은 의미이지만, 비교 연산자, 서브쿼리와 많이 사용된다.
- 컬럼 > ANY (서브쿼리) : 서브쿼리가 반환한 값 중 최소한 하나보다만 커도 참이다. 즉 최솟값보다 크면 참이다.
- 컬럼 < ANY (서브쿼리) : 위와 동일하지만, 반대이므로 최대값보다 작으면 무조건 참이다.
- 컬럼 = ANY (서브쿼리) : IN과 완전히 동일한 의미이다. 문법적으로 컬럼 다음에 ‘=’이 들어가는 것만 차이가 있다.
- ALL (값1, 값2, …) : IN, ANY와 다르게 모든 컬럼과 비교하는 연산자 이다.
- 컬럼 > ALL (서브쿼리) : 서브쿼리가 반환한 값 모두보다 커야 참이다. 즉 최댓값보다 커야 참이다.
- 컬럼 < ALL (서브쿼리) : 반대이므로 최솟값보다 커야 참이다.

-- 카테고리가 DAILY 인 소설을 주문한 주문 데이터를 조회한다면 아래와 같이 하나로 조회할 수 있다.
SELECT o.order_id
FROM orders o
WHERE o.novel_id IN (SELECT novel_id FROM novels WHERE category = 'DAILY');
-- 만약 서브쿼리의 결과가 1,2,3 이라면 IN (1, 2, 3) 이 되고,
-- orders 중 novel_id가 1, 2, 3 중 하나인 데이터들은 전부 조회되게 된다.
-- IN 연산자도 NOT IN (1, 2, 3) 으로 쓰면 1, 2, 3이 아닌 데이터를 조회할 수 있다.

-- 만약 카테고리가 DAILY 인 소설들의 가격 중 가장 싼 가격보다 비싼 소설들을 가져온다면 아래처럼 쓸 수 있다.
SELECT title, author_id
FROM novels
WHERE price > ANY (SELECT price FROM novels WHERE category = 'DAILY');
-- 서브쿼리가 먼저 실행되면서 DAILY 카테고리인 소설의 가격들을 전부 조회한다.
-- 그 후 ANY 조건에 따라 price 가 서브쿼리의 결과값들 중 최솟값보다 큰 데이터들만 조회된다.

-- 만약 반대로 DAILY인 소설들의 가격 중 가장 비싼 가격보다 더 비싼 소설들을 가져온다면 아래처럼 쓸 수 있다.
SELECT title, author_id
FROM novels
WHERE price > ALL (SELECT price FROM novels WHERE category = 'DAILY');
-- ALL 뺴고는 전부 같으며, ALL은 모든 값보다 커야 하므로 최댓값보다 큰 데이터만 가져올 수 있다.

-- 사실 이런 ANY, ALL은 MIN, MAX와 대부분 대체가 가능하다.
-- 그리고 MIN, MAX가 더 직관적이고 이해하기 편하다고 생각하는 경우가 많아 보통 ANY, ALL은 많이 사용하지 않는다.
SELECT title, author_id
FROM novels
WHERE price > (SELECT MIN(price) FROM novels WHERE category = 'DAILY'):
-- 이렇게 대체가 가능하다.

다중 컬럼 서브쿼리

여러 컬럼, 여러 값 서브쿼리이다.
예를 들어, 주문 데이터 중 특정 고객과 동일한 고객이면서 주문 처리상태가 PENDING 인 데이터를 가져올 때 사용할 수 있다.
보통 (user_id, status) 처럼 튜플 비교를 활용하게 된다.

-- order_id가 1인 주문을 한 고객과 동일한 user_id이면서 처리상태도 동일한 주문을 가져온다면 아래와 같이 쓸 수 있다.
SELECT order_id
FROM orders
WHERE (user_id, status) = (SELECT user_id, status
													 FROM orders
													 WHERE order_id = 1)
AND order_id != 1; -- 자기 자신은 제외

-- 만약 쿼리의 결과가 여러 행일 수도 있는 경우, IN과 함께 쓸수도 있다.
-- 각 고객별로 최초로 주문한 날짜의 주문 id를 구한다면 아래와 같이 쓸 수 있다.
SELECT order_id
FROM orders
WHERE (user_id, order_date) IN (SELECT user_id, MIN(order_date)
															  FROM orders
															  GROUP BY user_id);

상관 서브쿼리

위에서 나온 서브쿼리들과는 달리 동적으로 서브쿼리의 결과를 바꿀 수 있는 쿼리이다.
보통 메인쿼리의 컬럼 값을 서브쿼리에서 사용함으로써 각 행별로 서브쿼리가 다른 결과를 낼 수 있도록 한다.
상관 서브쿼리는 각 행별로 기준값이 달라질 수 있기 때문에 메인쿼리의 행 수 만큼 서브쿼리가 실행된다.
이렇듯 상관 서브쿼리는 딱 한번만 실행되는 것이 아니라 여러번 실행될 수 있기 때문에 성능적으로 비효율적일수도 있다.
보통 JOIN으로 재작성이 가능하다면 JOIN으로 쓰는게 옵티마이저가 더 효율적으로 실행하기 때문에, JOIN으로 가능하다면 JOIN으로 쓰는 것이 좋다.
하지만 JOIN으로 작성하기 너무 복잡하거나 아래처럼 EXISTS 를 활용해 더 효율적으로 실행이 가능할 때는 상관 서브쿼리를 사용하는게 좋을 수 있다.

-- 만약 각 상품별로 자신이 속한 카테고리의 평균 가격보다 가격이 클 경우에만 선택한다면 아래와 같이 쓸 수 있다.
SELECT p1.product_id, p1.name, p1.category, p1.price
FROM products p1
WHERE price >= (SELECT AVG(price) 
								FROM products p2 
								WHERE p2.category = p1.category);
-- 이렇게 하면 상품들중 자신이 속한 카테고리의 평균 가격보다 높은 상품들만 가져올 수 있다.
-- 대략적으로는 아래와 같이 실행된다.
-- 먼저 메인쿼리의 한 행을 읽는다.
-- 그 후 서브쿼리가 필요로 하는 메인쿼리의 값을 전달한다.
-- 전달받은 값을 기준으로 서브쿼리가 실행되고, 결과를 반환한다.
-- 결과를 통해 WHERE 절을 평가한다.
-- 이런 흐름이 각 행별로 반복된다.

-- 두번째로 만약 현재까지 한번이라도 주문된적이 있는 상품을 찾는다면
-- orders 테이블에 존재하는 product_id들을 이용해 products에서 상품을 찾을 수 있다.
SELECT product_id, name
FROM products
WHERE product_id IN (SELECT DISTINCT product_id FROM orders);
-- 이렇게 서브쿼리에서 DISTINCT 까지 사용해서 한번이라도 주문된 상품들을 구할 수 있다.
-- 하지만 만약 서브쿼리의 결과가 많아질경우 메모리에 저장해놓고 메인쿼리를 돌리며 비교해야 하기때문에
-- IN 연산은 너무 많은 결과와 비교하기에는 무리가 있다.
-- 그래서 EXISTS 연산자를 활용하면 좀 더 효율적으로 만들 수 있다.
SELECT p.product_id, p.name
FROM products p
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.product_id = p.product_id);
-- EXISTS 는 조건이 참이 되는순간 스캔을 멈추지만, IN을 사용하면 전체를 스캔해야 할 수도 있다.
-- 그래서 존재 여부만 확인한다면 EXISTS 가 더 효율적이며,
-- 이렇게 상관 서브쿼리와 같이 활용해 각 상품별로 주문 데이터에 존재하는지만 검사해서 조회할 수 있다.
-- 보통 IN은 서브쿼리 결과가 작다면 직관적이고 빠를 수 있고,
-- EXISTS 상관 서브쿼리는 서브쿼리에서 쓰이는 테이블이 크고 메인쿼리에서 쓰이는 테이블이 작을 때 효율적이다.

SELECT 서브쿼리

서브쿼리는 단순히 조건절 뿐만 아니라 SELECT에 포함하는 일종의 컬럼 처럼 쓸수도 있다.
SELECT 절에 포함되야 하므로 단일값을 반환하는 스칼라 서브쿼리만 가능하다.

-- 독립적으로 실행되는 비상관 서브쿼리
SELECT name, (SELECT AVG(price) FROM products) AS avg_price
FROM products;
-- 물론 이런 쿼리는 좀 더 효율적으로 쓸수도 있겠지만 서브쿼리를 쓴다면 이런식으로 쓸수도 있다.

-- 메인쿼리와 상호작용 하는 상관 서브쿼리
-- 예를 들어 상품들을 조회하면서 각 상품별로 몇번의 주문이 있었는지 구할때 아래와 같이 쓸 수 있다.
SELECT
	p.product_id,
	p.name,
	(SELECT COUNT(*) FROM orders o WHERE o.product_id = p.product_id) AS order_count
FROM
	products p;

주의사항

서브쿼리들은 대체로 JOIN 으로 해결할 수 있는 경우도 많다.
또 서브쿼리는 계층적으로 서브쿼리 → 메인쿼리 순서로 실행되어야 하는 경우가 많지만, JOIN은 좀 더 유연하기 때문에 효율적으로 실행될 수 있다.
그러므로 JOIN으로 해결이 가능하면 JOIN으로 해결하고, 정말 복잡해지거나 EXISTS 같은걸 활용하는게 더 낫다고 판단되면 서브쿼리를 사용하는 것이 좋다.
물론 요즘에는 옵티마이저에 따라 서브쿼리를 쓰더라도 내부적으로 JOIN으로 변환하는 경우도 있다. 하지만 명시적으로 JOIN 을 쓸 수 있으면 쓰는 것이 좋다.