[SQL] 서브쿼리 원리와 방식

🖇  서브쿼리란?
🖇  WHERE 절 서브쿼리
🖇  FROM 절 서브쿼리 (인라인 뷰)

SQL 쿼리를 작성하다 보면, 단일 쿼리로는 원하는 결과를 얻기 어려운 상황이 자주 발생한다.

    이럴 때 유용하게 쓰이는 것이 바로 서브쿼리(Subquery)
    즉, 쿼리 안의 또 다른 쿼리이다.

    특히 서브쿼리를 적절히 활용하면

    JOIN 으로는 표현이 복잡한 조건을 훨씬 간단하게 처리할 수 있기 때문에
    SQL을 더 깊이 이해하고자 한다면 익혀두어야 한다 :)

이 글에서는 서브쿼리의 개념부터 실행 방식, 자주 쓰이는 패턴까지 예제를 통해 이해하기 쉽게 정리해 보았다.

🖇  서브쿼리란?

Subquery

서브 쿼리(Subquery)는
쿼리 안에 포함된 또 다른 쿼리문으로, 중첩 쿼리(Nested Query)라고도 부른다.

• 재사용 가능한 쿼리 구조

• JOIN 없이도 다른 테이블의 결과를 조건으로 활용 가능

• 하나의 SQL 문으로 복잡한 연산을 해결할 수 있음

• 메인 쿼리가 서브쿼리를 포함하는 종속적인 관계
  
  

서브쿼리는 쿼리를 구조화시켜 각 부분이 명확한 특징을 갖고 있다.

여러 번의 쿼리를 수행해야만 얻을 수 있는 결과를 하나의 중첩된 쿼리문으로 간편하게 결과를 얻을 수 있다.

JOIN 이 여러 테이블의 데이터를 가로로 확장해 새로운 컬럼을 함께 보여줄 때 유용다면

💡  서브쿼리는 언제 사용하면 좋을까?

✓  새로운 컬럼이 필요하지 않을때 서브쿼리를 사용한다.

✓  완전히 다른 테이블에서 데이터 값을 조회하여 메인쿼리의 조건으로 사용하고자 할 때 유용하다. (조건 비교)

✓  조건 필터링이나 계산된 값을 가져올 때 유용하며 (값 추출)
✓  새로운 컬럼을 가져오기보다는 기존 컬럼을 필터링하거나 요약할 때 자주 사용한다 (요약 및 필터링)

Subquery 실행 순서

-- 실행 순서: 서브쿼리 ➝ 메인 쿼리
-- 들어갈 서브쿼리를 항상 먼저 추출해보고 사용한다.

SELECT *
FROM 테이블명
WHERE 컬럼명 IN (SELECT 컬럼명
              FROM 테이블명)

서브쿼리의 특징

특징	설명
괄호()로 감싸야 한다	서브쿼리는 항상 괄호로 감싼다
ORDER BY 불가	서브쿼리 내부에서는 ORDER BY 사용 불가
연산자의 오른쪽에 위치	=, IN 등의 비교 연산자 뒤에 위치
세미콜론(;) 사용 X	괄호 안에서 마무리 세미콜론은 쓰지 않는다

---

🖇  WHERE 절 서브쿼리

• 서브쿼리의 결과를 메인 쿼리의 조건으로 사용한다.

• 서브쿼리에서 반환하는 행의 개수에 따라 단일행 서브쿼리와 다중행 서브쿼리로 나눌 수 있다.

WHERE 절에서 서브쿼리를 사용하는 방법을 알아보자.

아래는 customers 테이블에서 가장 작은 주문 금액보다 작은 한도를 가진 고객 조회하는 예제인데

일단 서브쿼리를 사용하지 않고 쿼리문을 작성해 보자.

     1.  먼저 orders 테이블에서 가장 작은 주문 금액을 조회해야 한다.

-- 가장 작은 주문 금액 조회하기

SELECT MIN(price)
FROM orders

     orders 테이블에서 확인한 가장 작은 주문 금액을
     WHERE 절의 조건으로 사용하여

      2.  customers 테이블에서 가장 작은 주문 금액보다 작은 한도를 가진 고객을 조회한다.

-- 가장 작은 주문 금액(61,545원)보다 작은 한도를 가진 고객 조회하기

SELECT *
FROM customers
WHERE creditLimit < 61545
LIMIT 10

customers 테이블에서 가장 작은 주문 금액보다 작은 한도를 가진 고객 조회하기 위해 두 개의 쿼리를 작성했다.


서브쿼리를 사용하면

이렇게 여러 번의 쿼리를 수행해야 얻을 수 있는 결과를 하나의 중첩된 쿼리문으로 간편하게 작성할 수 있게 된다.

단일행 서브쿼리 (Single Row Subquery)

• 서브쿼리의 실행 결과가 항상 1건 이하(1행 이하)인 서브쿼리

• 단일행 비교 연산자 =, !=, >, < 등과 함께 사용한다.

SELECT 컬럼명
FROM 테이블명
WHERE 컬럼명 = (SELECT 컬럼명
              FROM 테이블명 
              WHERE 조건절)

이제 위에서 작성한 두 쿼리를 서브쿼리로 합쳐보자.

-- 가장 작은 주문 금액보다 작은 한도를 가진 고객 조회하기

SELECT *
FROM customers
WHERE creditLimit < (SELECT MIN(price)
                    FROM orders)

    서브쿼리의 실행 결과(orders 테이블에서 가장 작은 주문 금액 조회)가
    1행이기 때문에 단일행 서브쿼리라고 할 수 있다.

이처럼 서브쿼리는 조건을 유동적으로 만들 때 매우 유용하다 :)

다중행 서브쿼리 (Multiple Row Subquery)

• 서브쿼리의 실행 결과가 여러 건(여러 행)을 반환하는 서브쿼리

• 다중 비교 연산자 IN, NOT IN, ANY, ALL 등과 함께 사용한다.

SELECT 컬럼명
FROM 테이블명
WHERE 컬럼명 IN (SELECT 컬럼명
						 FROM 테이블명
						 WHERE 조건절)

다중행 서브쿼리를 사용해서

orders 테이블에서 미국(USA)에 사는 고객들의 주문번호를 조회해 보자.

-- 미국(USA)에 사는 고객들의 주문번호 조회하기

SELECT orderId
FROM orders 
WHERE customerId IN (SELECT customerId
                    FROM customers
                    WHERE country = 'USA')
                    

서브쿼리의 실행 결과(orders 테이블에서 미국에 사는 고객 번호 조회)가
여러 행이기 때문에 단일행 서브쿼리라고 할 수 있다.

---

🖇  FROM 절 서브쿼리 (인라인 뷰)

쿼리 결과를 하나의 테이블처럼 사용하는 기법으로,

서브쿼리를 마치 가상의 테이블(View)처럼 FROM 절에서 사용한다.

• 데이터 추출 결과를 FROM 절에서 하나의 테이블처럼 사용하고 싶을 때 사용

• FROM 절에 서브쿼리를 사용하면 특정 조건식을 갖는 데이터 추출결과를 테이블처럼 사용할 수 있다.

  e.g.,  기존 메인 단일쿼리로는 테이블에서 "고객별 마지막 제품의 구매일" 을 확인할 수 있었다면, 인라인 뷰를 사용하면 "고객별 마지막 제품의 구매일이 현재일을 기준으로 며칠 지났는지" 도 확인할 수 있다.
  ->   아래에서 예제를 통해 살펴볼 것이다 😊

• 뷰(view)처럼 작동하여 인라인 뷰(inline view)라고 부른다.

[참고]
뷰(view)란?  쿼리문으로 생성하는 가상 테이블이다.

인라인 뷰 사용하기

인라인 뷰를 사용하여
'현재일 - 마지막 주문일' 계산하는 예제를 살펴보자.

이번에도 마찬가지로 먼저, 단일 쿼리로 쿼리를 작성해 보았다.

-- 고객별 마지막 제품의 구매일 조회하기

SELECT customerId
      , MAX(orderDate) AS max_date
FROM orders 
GROUP BY 1

이제

FROM 절에서 서브쿼리를 사용하는 인라인 뷰와 julianday() 를 사용하여
고객별 마지막 제품의 구매일자가 현재일을 기준으로 며칠 지났는지 확인해 보자.

julianday 함수
:  pandassql과 SQLite에서 날짜 차이를 구할 때 사용되는 함수

[참고]
MySQL 등과 같은 DBMS에서는 DATEDIFF 등의 날짜와 관련된 함수를 사용한다.

-- 고객별 마지막 제품의 구매일자가 현재일을 기준으로 며칠 지났는지 확인하기

SELECT *
    , CAST(julianday('now') - julianday(max_date) AS INT) AS date_diff
FROM (SELECT customerId
            , MAX(orderDate) AS max_date
      FROM orders 
      GROUP BY 1) AS main

실제로 데이터 분석 시,
고객의 최근 구매일 기준 재구매율 분석이나 이탈 고객 탐지 등에 자주 활용된다고 한다.

지금까지 하나의 쿼리 안에 포함된 또 다른 쿼리, 서브쿼리에 대해 알아보았다.

상황	사용 이유
JOIN 없이 값 비교가 필요할 때	간단하게 한 컬럼 값 기준 필터링 가능
특정 조건에 따라 연산 결과를 메인 쿼리에 적용하고 싶을 때	WHERE 절에서 조건 기반의 비교 연산 최적화
SELECT 된 결과 자체를 새로운 테이블처럼 활용하고 싶을 때	FROM 절에서 인라인 뷰처럼 사용

서브쿼리를 사용하다 보면

WHERE, FROM, HAVING, SELECT 등 다양한 절에서 유연하게 활용되며,

복잡한 조건을 구조화하고, 재사용 가능하면서도 간결한 쿼리 작성을 가능하게 해준다는 것을 느끼게 될 것이다.

이 글이 서브쿼리를 이해하는 데에 도움이 되었기를 바란다.