[PostgreSQL] 서브 쿼리(Sub Query)의 개념 & 문법

PostgreSQL에서의 서브쿼리(subquery)는 하나의 쿼리 안에서 또 다른 쿼리를 실행하는 방법으로, 복잡한 쿼리를 단순화하거나 중간 결과를 계산할 때 매우 유용하다.

1. 요약

• PostgreSQL의 서브쿼리는 FROM, WHERE, SELECT, EXISTS 등 다양한 절에서 사용 가능
• 별칭을 반드시 지정해야 하며, 성능 문제를 고려해야 함
• 복잡한 서브쿼리는 CTE로 변환해 가독성과 효율성을 높이는 것이 좋음
• 서브쿼리와 조인의 장단점을 이해하고, 적절한 시점에 사용

2. 1. 서브쿼리의 기본 개념

• 서브쿼리란?
  • SQL 쿼리 내부에서 실행되는 또 다른 SQL 쿼리
  • 서브쿼리는 메인 쿼리에서 사용할 데이터를 반환하거나 조건을 생성하는 데 사용
  • PostgreSQL에서는 서브쿼리를 SELECT, FROM, WHERE, 또는 다른 SQL 절 내에서 사용할 수 있습니다.

3. 2. 서브쿼리의 주요 위치와 작성 예시

3.1. (1) FROM 절

From절에서 서브쿼리를 사용하면 서브쿼리 결과를 테이블처럼 다룰 수 있기 때문에 PostgreSQL의 서브쿼리는 Alias를 반드시 사용해야 한다.

 

예제1. 고객별 총 주문 금액 조회

      

      

      

    
    

      <sql />
    
/* ************************************************************************
* Description
*   - 서브쿼리(CUST_TOT_AMT): 고객별 총 주문 금액(SUM(order_amount))을 계산
*   - 외부쿼리: 총 주문 금액이 100 이상인 고객을 필터링
* ************************************************************************ */
SELECT CUST_TOT_AMT.customer_id
     , CUST_TOT_AMT.total_amount
FROM (
  SELECT customer_id
       , SUM(order_amount) AS total_amount
    FROM orders
   GROUP BY customer_id
   ) AS CUST_TOT_AMT
WHERE CUST_TOT_AMT.total_amount > 100
;

3.2. (2) WHERE 절에서 서브쿼리

WHERE 절에서 서브쿼리는 조건으로 사용되며, 주로 IN, EXISTS, 또는 비교 연산자와 함께 사용된다.

 

예제: 최근 1년 동안 주문이 있는 고객 조회

      

      

      

    
    

      <sql />
    
/* *******************************************************
* Description
* - 서브쿼리: 최근 1년 동안 주문이 발생한 고객 ID를 반환
* - 외부 쿼리: 해당 고객 ID를 이용해 고객 정보를 조회
******************************************************* */
SELECT customer_id
     , customer_name
  FROM customers
  WHERE customer_id IN (
          SELECT DISTINCT customer_id
          FROM orders
          WHERE order_date >= NOW() - INTERVAL '1 year'
  )
;

3.3. (3) SELECT 절에서 서브쿼리

SELECT 절에서 서브쿼리는 계산된 값을 추가 컬럼으로 삽입하거나, 관련 데이터를 가져올 때 사용된다.

 

예제: 각 고객의 주문 개수 추가

      

      

      

    
    

      <sql />
    
/* ***************************************************************
* Description
* - 서브쿼리: 특정 고객의 주문 개수를 계산
* - 외부 쿼리: order_count를 고객 테이블에 추가 컬럼으로 표시
***************************************************************** */
SELECT customer_id,
       customer_name,
       (SELECT COUNT(*)
          FROM orders
          WHERE orders.customer_id = customers.customer_id) AS order_count
FROM customers
;

 

3.4. (4) EXISTS 절에서 서브쿼리

EXISTS는 서브쿼리가 조건을 만족하는지 확인하는 데 사용되며, 조건이 만족되면 TRUE, 아니면 FALSE를 반환한다.

 

예제: 최소 한 번 이상 주문한 고객 조회

      

      

      

    
    

      <sql />
    
/* ***************************************************************
* Description
* - 서브쿼리: 해당 고객의 주문이 존재하는지 확인
* - 외부 쿼리: 주문이 있는 고객만 조회
***************************************************************** */
SELECT customer_id, customer_name
FROM customers
WHERE EXISTS (
  SELECT 1
  FROM orders
  WHERE orders.customer_id = customers.customer_id
);

---

4. 3. PostgreSQL 서브쿼리의 특징

1. 별칭(Alias)은 필수
   • FROM 절에서 서브쿼리를 사용할 경우 별칭(Alias)을 반드시 지정
2. 성능 최적화
   • 서브쿼리는 성능을 저하시킬 수 있으므로, 가능한 경우 조인(JOIN)이나 공통 테이블 표현식(CTE)으로 대체
3. 스칼라 서브쿼리
   • 서브쿼리가 단일 값을 반환할 경우, 스칼라 서브쿼리로 사용 가능
4. 다중 행 반환
   • 서브쿼리가 다중 행을 반환할 경우, IN, ANY, ALL 등을 사용하여 조건 구성 가능

---

5. 4. 서브쿼리와 CTE(Common Table Expression)의 비교

PostgreSQL에서도 서브쿼리와 CTE는 유사한 작업을 수행하지만, CTE는 가독성과 재사용성 면에서 더 유리하다.

 

CTE 예제. 고객 및 카드 종류별 총 주문 금액 계산

      

      

      

    
    

      <sql />
    
WITH TOT_AMT AS (
  SELECT customer_id
       , card_type_id
       , SUM(order_amount) AS total_amount
    FROM orders
    GROUP BY customer_id
           , card_type_id
)
SELECT customer_id
     , card_type_id
  FROM TOT_AMT
  WHERE total_amount > 100;

• CTE의 장점
  • 여러 단계의 복잡한 쿼리를 가독성 높게 작성 가능
  • 한 번 정의된 CTE를 여러 번 참조 가능

6. 5. 서브쿼리 사용 시 주의사항

1. 성능
   • 서브쿼리의 과도한 사용은 성능을 저하시킬 수 있으므로 필요한 경우만 사용
   • 대규모 데이터에서 서브쿼리 대신 조인(JOIN)을 사용하는 것이 성능 면에서 더 나을 수 있음
2. 중첩 제한
   • 너무 많은 중첩은 읽기 어렵고 유지보수가 어렵기에 최대한 간단하게 작성하는 것을 추천
3. 최적화 도구 활용
   • PostgreSQL의 EXPLAIN 및 ANALYZE 명령을 사용하여 쿼리 성능을 분석하고 최적화가 필요

 

6.0.1. Ref.

https://cloud.google.com/spanner/docs/reference/postgresql/subqueries

Subqueries in PostgreSQL  |  Spanner  |  Google Cloud

 

https://www.postgresql.org/docs/current/functions-subquery.html

9.24. Subquery Expressions