SQL 서브쿼리와 CTE

 

 

1️⃣ 서브쿼리 (Subquery)

SQL 구문 내에 포함된 쿼리, (메인)쿼리 속 쿼리

 

 

✅ 목적

• 데이터 필터링
• 복잡한 집계
• JOIN

 

 

✅ 위치

• SELECT 문
• FROM 절
• WHERE 절
• HAVING 절
• ORDER BY 절

 

 

✅ 유형

• 메인 쿼리와의 연관성 - 비상관 서브쿼리
  • 아무것도 참조하지 않고, 연계되지 않고, 단독으로 실행되는 서브쿼리
  • 메인 테이블과 조인 조건이 걸리지 않는 서브쿼리
  • 연산자(=, <>, <, >, <=, >=) + [ all / any / in ] 사용한 조건 부여 가능

-- 비상관 서브쿼리
-- (예시) 인도(India)에 없는 모든 도시를 반환
SELECT city_id, city_name
FROM city
WHERE country_id
	<> (SELECT country_id FROM country WHERE country = 'India');

 

⛔ 여기서 잠깐 - MySQL은 특정 쿼리 연산자에 서브 쿼리의 LIMIT을 지원하지 않는다.

 

 

• 메인 쿼리와의 연관성 - 상관 서브쿼리
  • 메인쿼리에 의존적인 쿼리
  • 메인 테이블과 조인 조건이 걸린 서브쿼리

-- 상관 서브쿼리
-- (예시) 서브쿼리로 고객별 영화 대여 횟수 계산 후, 정확히 20편 대여한 고객을 반환
SELECT c.first_name, c.last_name
FROM customer c
WHERE 20 =
	(SELECT count(*) FROM rental r WHERE r.customer_id = c.customer_id);

 

 

• 반환값 - 단일 행 서브쿼리
  • 한 행의 결과값만 반환하는 서브쿼리
  • 메인쿼리의 WHERE 절에 사용할 경우, 단일 행 비교 연산자( =, <>, <, >, <=, >= , !=) 사용

 

 

• 반환값 - 다중 행 서브쿼리
  • 여러 행의 결과값을 반환하는 서브쿼리
  • 메인쿼리의 WHERE 절에 사용할 경우, 다중 행 비교 연산자( IN, ANY, ALL, EXISTS) 사용

ⓒnote.espriter.net/1376

 

 

• 반환값 - 다중 컬럼 서브쿼리
  • 여러 컬럼(열)에 대한 결과 값을 반환하는 서브쿼리
  • WHERE 절에서 하나의 행을 반환 - 단일 행 비교 연산자 사용
  • WHERE 절에서 여러 행을 반환 - 다중 행 비교 연산자 사용
  • 메인 쿼리 컬럼 개수, 유형 = 서브 쿼리에서 반환하는 컬럼 개수, 유형

 

 

 

 

2️⃣ CTE (Common Table Expressions, 공통 테이블 표현식)

쿼리를 통해 만들어낸 임세 데이터 세트

 

⛔ 여기서 잠깐 - CTE vs VIEW
CTE - 생성 권한이 필요 없고, 하나의 쿼리문이 끝날 때까지만 지속되는 일회성 테이블
VIEW - 생성 권한이 필요하며, 사전 정의 필수

 

 

✅ 특징

• 복잡한 쿼리문의 가독성 ⬆️
• 재사용성
• 중복 방지

 

 

✅ 목적

• 데이터 집계
• 서브쿼리 대체
• 임시 테이블 생성

 

 

✅ 구조

-- CTE 구조
WITH table AS (SELECT…)


-- CTE 예시
WITH AmountSum AS (
    SELECT customer_id, sum(Amount) as TotalAmount
    FROM customers
    GROUP BY customer_id
    )
SELECT *
FROM AmountSum;

 

 

 

 

3️⃣ 재귀적 CTE

자기 자신을 참조하여 반복적으로 데이터를 처리

계층적 데이터 구조(ex. 조직도) 처리에 용이

 

 

✅ 구조

-- 재귀적 CTE
WITH RECURSIVE OrgChart AS (
    SELECT EmployeeID, Name, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.Name, e.ManagerID
    FROM Employees e
    INNER JOIN OrgChart o ON e.ManagerID = o.EmployeeID
    )
SELECT * FROM OrgChart;

 

 

 

 

4️⃣ 참고 자료

MySQL :: MySQL 8.0 Reference Manual :: 13.2.15.12 Restrictions on Subqueries

러닝 SQL | 앨런 볼리외 - 교보문고

 

SQL CTE를 잘 활용하려면?

 

GitHub - data-jeong/FromSQLBasicsToPro-MySQL: From SQL Basics To Pro