sungyup's.

PostgreSQL / SQL Basics / 2.8 Subquery

2.8Subquery

What’s a Subquery?

  • ‘Toys’ department의 모든 제품들보다 더 비싼 제품들의 이름과 가격을 보여주는 쿼리

    • 이 문제는 2단계로 해결할 수 있음
    sql
    SELECT MAX(price)
FROM products
WHERE department = 'Toys';
    • 위의 쿼리로 Toys department의 가장 비싼 제품의 가격을 구한 후,
    sql
    SELECT name, price
FROM products
WHERE price > (앞선 쿼리 결과값)

⇒ 이런 두 단계의 쿼리를 한번에 작성하는 방법이 Subquery를 이용하는 것!

  • 값이 쿼리로 계산이 필요한 곳에 ( )를 넣고 안에 쿼리를 넣음
    • subquery가 끝날때는 ;를 쓰지 않음
sql
SELECT name, price
FROM products
WHERE price > (
	SELECT MAX(price) FROM products
	WHERE department = 'Toys'
);

Subquery의 핵심은 쿼리 결과의 모양을 이해하는 것

⇒ Subquery가 쓰일 수 있는 곳이 애초에 조회하고자 하는 데이터 모수, 조회 결과 값, 조회 조건 등 쿼리의 모든 곳으로 아주 다양함

sql
SELECT p1.name, 
  -- A source of a value
	(SELECT COUNT(name) FROM products)
-- A source of rows
FROM (SELECT * FROM products) AS p1
JOIN (SELECT * FROM products) AS p2 ON p1.id = p2.id
-- A source of a column
WHERE p1.id IN (SELECT id FROM products);

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

Subqueries in a SELECT

Single Value를 가져오는 Subquery

sql
SELECT name, price, 
(여기에 들어가는 subquery는 single value)
FROM products;

예를 들면 아래와 같은 식

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

Subqueries in a FROM

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

  1. 데이터를 반환하는 FROM 안의 서브쿼리

⇒ 단, 반드시 반환된 데이터를 alias로 재명명한 AS 구문을 포함해야함

⇒ 또, 반환한 데이터의 column 안에서만 밖에서 SELECT 가능

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

(참고로, 위의 subquery는 딱히 필요한 상황에 쓴것이 아니고 예제일 뿐)

  1. Value를 return하는 FROM 안의 서브쿼리
sql
SELECT *
FROM (
	SELECT MAX(price) FROM products
) AS p;

Subqueries in a JOIN clause

ON 구문과 연결이 되는 것을 전제로 하는 다양한 데이터를 반환하는 서브쿼리를 쓸 수 있음

  • FROM 때와 마찬가지로 alias 써야함
sql
SELECT first_name
FROM users
JOIN (
	SELECT user_id FROM orders
	WHERE product_id = 3
) AS o
ON o.user_id = users.id;

(마찬가지로 아주 유용한 서브쿼리는 아니지만, 예시)

Subqueries with WHERE

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

sql
SELECT id
FROM orders
WHERE product_id IN (
	SELECT id
	FROM products
	WHERE price / weight > 5;
);
  • WHERE 다음에 오는 쿼리는 연산자에 따라 single value 또는 single column이 될 수 있으며, 이에 따라 subquery를 구성해야함
    • 예를 들어, WHERE product_id > (subquery)면 subquery의 결과가 single value여야할 것
    • 또는, WHERE product_id IN (subquery)면 subquery의 결과는 single column이어야함

A New Where Operator

ALL, SOME 을 통해 새로운 Where 구문 작성 가능

  • < ALL, ≥SOME, ≥ANY, <>ALL 등
sql
SELECT name, department, price
FROM products
WHERE price > ALL (
	SELECT price
	FROM products
	WHERE department = 'Industrial'
);

Some Operator

  • SOME은 사실 ANY의 alias임(상호대체 가능)
sql
SELECT name, department, price
FROM products
WHERE price > SOME (
  SELECT price
  FROM products
  WHERE department = 'Industrial'
);

Correlated Subqueries

Q. Show the name, department, and price of the most expensive product in each department

  • alias를 써서, Inner Query가 Outer Query에 접근하는 등의 관계로 해결
sql
SELECT name, department, price
FROM products AS p1
WHERE p1.price = (
	SELECT MAX(price)
	FROM products AS p2
	WHERE **p1.department = p2.department**
)

SELECT without FROM

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

⇒ 이 때 subquery는 딱 한개 값만 반환해야함

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

위의 예시만 보면 쓸 일이 없어보이지만, 실제로는 비율 등 계산에 쓰임

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

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

PreviousSorting
PostgreSQL
NextDistinct
PostgreSQL