SQL 조회 문법 총정리 3편 (MySQL/Oracle CTE, 서브쿼리, 고급 집계)

Advanced SQL Query Syntax Part 3 (MySQL/Oracle CTE, Subquery, Advanced Aggregation)

1, 2편에서 사용했던 Users 테이블과 Orders 테이블을 계속 사용합니다.

• MySQL
  MySQL 8.0 버전부터 CTE를 지원하며, 재귀 CTE의 경우 WITH RECURSIVE 키워드를 사용합니다.
• Oracle
  CTE를 지원하며, 재귀 CTE도 WITH 키워드만으로 기본적으로 지원합니다 (재귀 호출 시 컬럼명 명시 권장).
  MATERIALIZE 또는 INLINE 힌트를 사용하여 CTE의 구체화(임시 테이블 생성) 여부를 제어할 수도 있습니다 (성능 튜닝 시 고려).

-- UserTotalAmounts CTE: 사용자별 총 주문 금액 계산
WITH UserTotalAmounts AS (
    SELECT
        user_id,                      -- 사용자 ID
        SUM(amount) AS total_amount   -- 해당 사용자의 총 주문 금액
    FROM Orders
    WHERE user_id IS NOT NULL         -- user_id가 NULL이 아닌 주문만 대상
    GROUP BY user_id                  -- 사용자 ID로 그룹화
),
 
-- AvgTotalAmount CTE: 전체 사용자의 평균 총 주문 금액 계산
AvgTotalAmount AS (
    -- UserTotalAmounts CTE의 결과를 사용하여 평균 계산
    SELECT AVG(total_amount) AS avg_amount
    FROM UserTotalAmounts
)
 
-- 메인 쿼리: 평균 총 주문 금액 이상인 사용자 정보 조회
SELECT
    U.name,                       -- 사용자 이름
    UTA.total_amount              -- 사용자의 총 주문 금액
FROM Users U
JOIN UserTotalAmounts UTA ON U.id = UTA.user_id -- Users 테이블과 UserTotalAmounts CTE를 사용자 ID로 조인
WHERE UTA.total_amount >= (SELECT avg_amount FROM AvgTotalAmount);
-- AvgTotalAmount CTE의 결과를 서브쿼리로 사용하여 조건 비교

-- OrdersByProduct CTE: 상품별 총 주문액과 주문 건수 계산
WITH OrdersByProduct AS (
    SELECT
        product,                      -- 상품명
        SUM(amount) AS total_product_amount, -- 상품별 총 주문액
        COUNT(id) AS order_count      -- 상품별 주문 건수
    FROM Orders
    GROUP BY product
),
 
-- UsersWithHighOrderCountProduct CTE: 특정 주문 건수(예: 2건) 이상인 상품을 주문한 사용자 ID 조회
UsersWithHighOrderCountProduct AS (
    SELECT DISTINCT -- 중복 제거
        O.user_id
    FROM Orders O
    JOIN OrdersByProduct OP ON O.product = OP.product -- Orders와 OrdersByProduct CTE 조인
    WHERE OP.order_count >= 2 AND O.user_id IS NOT NULL -- 주문 건수가 2 이상인 상품을 주문한 사용자
)
 
-- 메인 쿼리: 해당 사용자들의 이름과 이메일 조회
SELECT
    U.name,
    U.email
FROM Users U
JOIN UsersWithHighOrderCountProduct UHCOP ON U.id = UHCOP.user_id; -- Users와 두 번째 CTE 조인

(Apple, Banana가 2건 이상 주문되었고, 해당 상품을 주문한 사용자들)

• MySQL
  WITH RECURSIVE cte_name AS (anchor_member UNION ALL recursive_member)
• Oracle
  WITH cte_name (col1, col2, ..) AS (anchor_member UNION ALL recursive_member) (컬럼명 명시 권장)

• MySQL

  -- EmployeeHierarchy CTE: 직원 계층 구조 조회 (MySQL은 RECURSIVE 명시)
  WITH RECURSIVE EmployeeHierarchy AS (
      -- Anchor Member: 계층의 시작점 (Alice)
      SELECT
          id,          -- 직원 ID
          name,        -- 직원 이름
          manager_id,  -- 매니저 ID
          0 AS level   -- 계층 레벨 (시작은 0)
      FROM Employees
      WHERE name = 'Alice' -- 시작 직원 이름 지정
      UNION ALL -- Anchor 결과와 Recursive 결과를 합침
      -- Recursive Member: 하위 직원들을 재귀적으로 찾아 연결
      SELECT
          e.id,        -- 현재 직원 ID
          e.name,      -- 현재 직원 이름
          e.manager_id,-- 현재 직원의 매니저 ID
          eh.level + 1 -- 이전 레벨에 1을 더함
      FROM Employees e
      INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id -- 현재 직원의 매니저 ID와 이전 계층의 직원 ID를 조인 (재귀 호출)
      WHERE eh.level < 5 -- 최대 재귀 깊이 제한 (선택 사항, 무한 루프 방지)
  )
  -- 최종 결과 조회: 계층 레벨과 ID 순으로 정렬
  SELECT * FROM EmployeeHierarchy ORDER BY level, id;

• Oracle

  -- EmployeeHierarchy CTE: 직원 계층 구조 조회 (Oracle은 컬럼 리스트 명시 권장)
  WITH EmployeeHierarchy (id, name, manager_id, lvl) AS ( -- CTE 컬럼명 명시
      -- Anchor Member: 계층의 시작점 (Alice)
      SELECT
          id,
          name,
          manager_id,
          0 AS lvl    -- 계층 레벨 (시작은 0)
      FROM Employees
      WHERE name = 'Alice' -- 시작 직원 이름 지정
      UNION ALL -- Anchor 결과와 Recursive 결과를 합침
      -- Recursive Member: 하위 직원들을 재귀적으로 찾아 연결
      SELECT
          e.id,
          e.name,
          e.manager_id,
          eh.lvl + 1  -- 이전 레벨에 1을 더함
      FROM Employees e
      INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id -- 현재 직원의 매니저 ID와 이전 계층의 직원 ID를 조인 (재귀 호출)
      WHERE eh.lvl < 5 -- 최대 재귀 깊이 제한 (선택 사항)
  )
  -- 최종 결과 조회: 계층 레벨과 ID 순으로 정렬
  SELECT * FROM EmployeeHierarchy ORDER BY lvl, id;

• SELECT 절 (스칼라 서브쿼리)
  반드시 단일 행, 단일 컬럼의 값(스칼라 값)만을 반환.
• FROM 절 (인라인 뷰)
  서브쿼리의 결과를 하나의 테이블처럼 사용. 반드시 별칭 지정.
• WHERE 절 / HAVING 절
  조건을 비교하기 위한 값이나 값의 목록을 반환.

SELECT
    U.name, -- 사용자 이름
    U.email, -- 사용자 이메일
    -- 스칼라 서브쿼리: 현재 행의 U.id를 참조하여 해당 사용자의 총 주문 횟수 계산
    (SELECT COUNT(O.id)
      FROM Orders O
      WHERE O.user_id = U.id) AS order_count -- 계산된 주문 횟수를 order_count 컬럼으로 표시
FROM Users U;

인라인 뷰는 서브쿼리의 결과를 테이블처럼 사용하기 때문에, 메인 쿼리에서 조인, 필터링 등 다양한 작업을 수행할 수 있습니다.

-- 메인 쿼리: 주문 정보와 사용자 이름 조회
SELECT
    U.name,         -- 사용자 이름
    UserAvgOrders.product, -- 인라인 뷰에서 가져온 상품명
    UserAvgOrders.amount   -- 인라인 뷰에서 가져온 주문 금액
FROM Users U
-- 인라인 뷰 (UserAvgOrders): 사용자별 평균 주문 금액보다 큰 주문들만 필터링
JOIN (
    SELECT
        O.user_id,    -- 사용자 ID
        O.product,    -- 상품명
        O.amount,     -- 주문 금액
        -- 각 사용자별(PARTITION BY O.user_id) 평균 주문 금액을 계산하는 윈도우 함수
        AVG(O.amount) OVER (PARTITION BY O.user_id) AS avg_user_amount
    FROM Orders O
    WHERE O.user_id IS NOT NULL
) UserAvgOrders ON U.id = UserAvgOrders.user_id -- Users 테이블과 인라인 뷰(UserAvgOrders)를 사용자 ID로 조인
WHERE UserAvgOrders.amount > UserAvgOrders.avg_user_amount; -- 조건: 주문 금액이 해당 사용자 평균 주문 금액보다 큰 경우

각 사용자의 평균 주문액을 초과하는 주문들만 나옴. 예시 데이터에 따라 결과가 달라질 수 있음

• 단일 행 서브쿼리
  = , > , < 등과 사용.
• 다중 행 서브쿼리
  IN, NOT IN, ANY, ALL, EXISTS 등과 사용.
  ANY: 서브쿼리 결과 중 하나라도 조건을 만족하면 TRUE. (예: < ANY는 최솟값보다 작으면 TRUE)
  ALL: 서브쿼리 결과 모두 조건을 만족해야 TRUE. (예: > ALL은 최댓값보다 크면 TRUE)
• NOT IN 사용 시 주의점
  서브쿼리 결과에 NULL이 포함되어 있고, 메인 쿼리의 비교 컬럼도 NULL이 가능하면 예상치 못한 결과(주로 아무것도 선택되지 않음)가 나올 수 있습니다.
  서브쿼리 내에서 WHERE column IS NOT NULL 조건을 추가하거나 COALESCE 등을 사용하는 것이 안전합니다.

SELECT
    name, -- 사용자 이름
    age   -- 사용자 나이
FROM Users
WHERE age = (SELECT MAX(age) FROM Users);
-- 조건: 나이가 (Users 테이블의 최대 나이)와 같은 경우

SELECT DISTINCT -- 중복된 사용자 이름 제거
    U.name -- 사용자 이름
FROM Users U
-- 조건: 사용자 ID가 (아래 서브쿼리 결과 목록: 'Apple' 또는 'Banana'를 주문한 사용자 ID들)에 포함되는 경우
WHERE U.id IN (
    SELECT O.user_id
    FROM Orders O
    WHERE O.product IN ('Apple', 'Banana') AND O.user_id IS NOT NULL
);

SELECT
    O.id AS order_id,
    O.product,
    O.amount
FROM Orders O
WHERE O.amount > ANY ( -- 조건: 주문 금액이 (홍길동의 주문 금액들 중 '어떤 것'보다라도) 큰 경우
    SELECT amount
    FROM Orders
    WHERE user_id = (SELECT id FROM Users WHERE name = '홍길동')
)
AND O.user_id != (SELECT id FROM Users WHERE name = '홍길동'); -- 홍길동 본인의 주문은 제외

(홍길동의 주문 금액들 중 최솟값보다 큰 다른 주문들이 조회됨)

SELECT
    U.name,  -- 사용자 이름
    U.email  -- 사용자 이메일
FROM Users U
-- 조건: 다음 서브쿼리의 결과가 하나라도 존재하면 TRUE (상관 서브쿼리)
WHERE EXISTS (
    SELECT 1 -- 실제 값을 가져올 필요 없이 존재 여부만 체크
    FROM Orders O
    WHERE O.user_id = U.id -- 외부 쿼리 Users 테이블의 현재 행 U.id를 참조
);

• MySQL
  GROUP BY ... WITH ROLLUP. 결과에서 집계된 행의 그룹핑 컬럼 값은 NULL로 표시됩니다.
• Oracle
  GROUP BY ROLLUP(col1, col2, ...). GROUPING(col) 함수를 사용하여 해당 컬럼이 집계로 인해 NULL이 된 것인지 (결과 1), 아니면 원래 값이 NULL인지 (결과 0) 구분할 수 있습니다.

• MySQL (WITH ROLLUP 사용)

  SELECT
      -- 이름이 NULL이면 '전체 총계', 상품명이 NULL이고 이름이 있으면 '사용자별 소계'로 표시
      IF(U.name IS NULL, '전체 총계', U.name) AS user_name,
      IF(O.product IS NULL, IF(U.name IS NULL, '', '사용자별 소계'), O.product) AS product_name,
      SUM(O.amount) AS total_sales -- 주문 총액
  FROM Orders O
  JOIN Users U ON O.user_id = U.id
  GROUP BY U.name, O.product WITH ROLLUP -- 이름과 상품별로 그룹화하고 ROLLUP 적용
  ORDER BY U.name, O.product; -- 정렬 (NULL은 기본적으로 처음에 올 수 있음)

• Oracle (ROLLUP 및 GROUPING 함수 사용)

  SELECT
      -- GROUPING(U.name)이 1이면 U.name이 ROLLUP에 의해 집계된 것이므로 '전체 총계'
      CASE WHEN GROUPING(U.name) = 1 THEN '전체 총계' ELSE U.name END AS user_name,
      CASE
          -- GROUPING(O.product)이 1이면 O.product가 ROLLUP에 의해 집계된 것
          WHEN GROUPING(O.product) = 1 THEN
              -- GROUPING(U.name)도 1이면 (즉, 전체 총계 행이면) 빈 문자열, 아니면 (사용자별 소계 행이면) '사용자별 소계'
              CASE WHEN GROUPING(U.name) = 1 THEN '' ELSE '사용자별 소계' END
          ELSE O.product -- 집계된 것이 아니면 상품명 그대로
      END AS product_name,
      SUM(O.amount) AS total_sales -- 주문 총액
  FROM Orders O
  JOIN Users U ON O.user_id = U.id
  GROUP BY ROLLUP(U.name, O.product) -- 이름과 상품별로 그룹화하고 ROLLUP 적용
  ORDER BY U.name NULLS LAST, O.product NULLS LAST; -- NULLS LAST로 총계/소계 행들을 마지막에 정렬

(결과는 여러 행으로 나타나며, user_name 또는 product_name이 '전체 총계' 또는 '사용자별 소계'인 행들이 소계/총계를 나타냅니다.)

• MySQL
  GROUP_CONCAT(DISTINCT expression ORDER BY expression ASC/DESC SEPARATOR str_val). 기본 구분자는 콤마(,)입니다. 반환되는 문자열의 최대 길이는 group_concat_max_len 시스템 변수에 의해 제한됩니다.
• Oracle
  LISTAGG(measure_expr, delimiter) WITHIN GROUP (ORDER BY order_by_clause). 구분자를 반드시 지정해야 합니다. 반환 문자열 최대 길이는 4000 바이트 (VARCHAR2) 또는 32767 바이트 (CLOB - 추가 처리 필요) 입니다.

• MySQL

  SELECT
      U.name, -- 사용자 이름
      -- 주문한 상품들을 상품명 오름차순으로 정렬하여 ', '로 구분하여 하나의 문자열로 만듦 (중복 상품은 한 번만)
      GROUP_CONCAT(DISTINCT O.product ORDER BY O.product ASC SEPARATOR ', ') AS products_ordered
  FROM Users U
  JOIN Orders O ON U.id = O.user_id -- Users와 Orders 조인
  GROUP BY U.name; -- 사용자 이름으로 그룹화

• Oracle

  SELECT
      U.name, -- 사용자 이름
      -- 주문한 상품들을 상품명 오름차순으로 정렬하여 ', '로 구분하여 하나의 문자열로 만듦 (중복 상품은 한 번만)
      LISTAGG(DISTINCT O.product, ', ') WITHIN GROUP (ORDER BY O.product ASC) AS products_ordered
  FROM Users U
  JOIN Orders O ON U.id = O.user_id -- Users와 Orders 조인
  GROUP BY U.name; -- 사용자 이름으로 그룹화

다음 편에서는 오늘 다루지 못한 나머지 고급 집계 함수(CUBE, GROUPING SETS), 강력한 문자열 처리를 위한 정규표현식, 윈도우 함수의 더 깊이 있는 활용법(분석 함수, 프레임 절 심화), 그리고 데이터를 행에서 열로, 또는 열에서 행으로 변환하는 피벗팅과 언피벗팅 기법에 대해 자세히 알아보겠습니다.

• SQL Tutorial↗
• Mode Analytics SQL Tutorial↗
• SQLZoo↗