GROUP BY가 포함된 JOIN UPDATE

들어가며

MySQL에서 JOIN UPDATE는 다른 테이블의 데이터를 참조하여 특정 테이블을 업데이트할 때 사용하는 강력한 기능이다. 그런데 여기에 GROUP BY를 사용하려 하면 갑자기 문법 오류가 발생한다. 왜 그럴까? 이번 글에서는 JOIN UPDATE에서 GROUP BY가 왜 안 되는지, 그리고 이 문제를 해결하기 위한 파생 테이블 , STRAIGHT_JOIN , JOIN_ORDER 힌트 , LATERAL 조인 까지 깊이 있게 다뤄본다. 각 방식의 실행 계획을 직접 비교하여 어떤 차이가 있는지도 확인해보자.

문제 상황: GROUP BY가 포함된 JOIN UPDATE

부서별 직원 수를 departments 테이블에 저장하고 싶다고 가정하자.

-- 먼저 emp_count 칼럼 추가
ALTER TABLE departments ADD emp_count INT;

UPDATE departments d, dept_emp de
	SET d.emp_count = COUNT(*)
WHERE de.dept_no = d.dept_no
GROUP BY de.dept_no;

하지만 이 쿼리는 다음과 같은 에러를 발생시킨다.

Error Code: 1064. You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY de.dept_no' at line 4

왜 안 될까?: JOIN UPDATE의 구조적 한계

JOIN UPDATE(다중 테이블 UPDATE)에서 GROUP BY와 ORDER BY, LIMIT은 사용할 수 없다. 이것은 MySQL 공식 문서에도 명시되어 있다.

“For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.”

그렇다면 왜 이런 제약이 있을까? 이해하려면 UPDATE 문의 동작 방식을 알아야 한다.

단일 테이블 UPDATE의 동작

UPDATE employees 
SET salary = salary * 1.1 
WHERE dept_id = 1
ORDER BY hire_date
LIMIT 10;

단일 테이블 UPDATE는 다음과 같이 동작한다.

1. WHERE 조건에 맞는 레코드를 찾는다
2. ORDER BY로 정렬한다
3. LIMIT 개수만큼 업데이트한다

이 과정은 명확하고 예측 가능 하다. 어떤 레코드가 업데이트될지 정확히 알 수 있다.

다중 테이블 UPDATE의 동작

UPDATE employees e, departments d
SET e.dept_name = d.name
WHERE e.dept_id = d.id;

다중 테이블 UPDATE는 조인 결과 를 기반으로 동작한다.

1. 테이블들을 조인한다
2. 조인 결과의 각 행에 대해 SET 절을 적용한다

여기서 문제가 발생한다. 조인 결과가 항상 일정하지 않다. 옵티마이저가 어떤 테이블을 먼저 읽느냐(드라이빙 테이블)에 따라 조인 순서와 중간 결과가 달라질 수 있다.

GROUP BY가 안 되는 이유

GROUP BY를 사용한 집계(COUNT, SUM 등)는 여러 행을 하나로 묶는 작업 이다. 그런데 UPDATE는 개별 행을 변경하는 작업 이다. 이 두 가지는 개념적으로 충돌한다.

• COUNT(*)는 그룹 단위로 계산된다
• 하지만 UPDATE는 행 단위로 실행된다
• 집계 결과를 “어떤 행에” 적용해야 하는지 모호해진다

JOIN UPDATE에서 GROUP BY, ORDER BY, LIMIT을 사용할 수 없는 것은 문법적 제약이 아니라, 논리적으로 모호한 상황을 방지 하기 위한 설계상의 결정이다.

드라이빙 테이블과 드리븐 테이블

해결책을 알아보기 전에, 조인 성능에 핵심적인 드라이빙/드리븐 테이블 개념을 이해해야 한다.

MySQL의 조인은 기본적으로 Nested Loop Join 방식으로 동작한다.

for each row in 드라이빙_테이블:
    for each row in 드리븐_테이블 where 조인조건:
        // 결과 출력

드라이빙 테이블 (Driving Table)은 조인의 기준이 되는 외부 루프의 테이블이고, 드리븐 테이블 (Driven Table)은 내부 루프에서 검색되는 테이블이다.

───────────────────────────────────────────────────────────
                     Nested Loop Join                 
───────────────────────────────────────────────────────────
                                                     
  드라이빙 테이블 (Driving Table)                      
  ┌─────┐                                            
  │  A  │  →  외부 루프: 각 행을 순차적으로 읽음          
  │  B  │                                            
  │  C  │     for each row in 드라이빙:               
  └─────┘         드리븐 테이블에서 조인 조건 검색         
      │                                                   
      ▼                                                   
  드리븐 테이블 (Driven Table)                             
  ┌─────────┐                                             
  │ A - 1   │                                             
  │ A - 2   │  ←  내부 루프: 인덱스로 빠르게 검색          
  │ B - 1   │                                             
  │ C - 1   │                                             
  │ C - 2   │                                             
  └─────────┘                                             
                                                          
───────────────────────────────────────────────────────────

왜 드라이빙 테이블 선택이 중요한가?

• 드라이빙 테이블은 풀 스캔 또는 인덱스 레인지 스캔으로 읽힌다
• 드리븐 테이블은 드라이빙 테이블의 각 행마다 조인 조건으로 검색된다
• 드리븐 테이블에 적절한 인덱스가 있으면 빠르게 검색된다

일반적인 최적화 원칙

• 결과 행 수가 적은 테이블을 드라이빙으로 선택
• 드리븐 테이블의 조인 칼럼에는 인덱스가 있어야 함
• 파생 테이블(임시 테이블)은 인덱스가 없으므로 드라이빙으로 선택하는 것이 유리할 수 있음

이 쿼리를 다른 방식으로 작성하여 해결할 수 있다.

1. 서브쿼리를 활용한 파생 테이블

가장 기본적인 해결책은 서브쿼리로 미리 집계한 결과 를 파생 테이블(Derived Table)로 만들어 조인하는 것이다.

UPDATE departments d,
       (SELECT de.dept_no, COUNT(*) AS emp_count
        FROM dept_emp de
        GROUP BY de.dept_no) dc -- 부서별 인원수 표를 만들어놓은 뒤 조인
SET d.emp_count = dc.emp_count
WHERE dc.dept_no = d.dept_no;

이 쿼리의 실행 과정은 다음과 같다.

1. 서브쿼리가 먼저 실행되어 부서별 직원 수를 계산한다
2. 그 결과가 임시 테이블(파생 테이블 dc)로 생성된다
3. departments와 파생 테이블을 조인한다
4. 조인 결과에 따라 UPDATE를 수행한다

여기서 GROUP BY는 서브쿼리 내부에서 실행되므로 JOIN UPDATE의 제약에 걸리지 않는다.

2. STRAIGHT_JOIN으로 조인 순서 강제

옵티마이저가 선택한 조인 순서가 비효율적일 수 있다. 이때 STRAIGHT_JOIN 을 사용하면 조인 순서를 강제할 수 있다.

UPDATE (SELECT de.dept_no, COUNT(*) AS emp_count
        FROM dept_emp de
        GROUP BY de.dept_no) dc
       STRAIGHT_JOIN departments d ON dc.dept_no = d.dept_no
SET d.emp_count = dc.emp_count;

STRAIGHT_JOIN이란?

STRAIGHT_JOIN 은 MySQL 전용 키워드로, FROM 절에 명시된 순서대로 조인을 수행 하도록 강제한다.

-- 일반 조인: 옵티마이저가 순서 결정
SELECT * FROM A INNER JOIN B ON A.id = B.id;

-- STRAIGHT_JOIN: A → B 순서 강제
SELECT * FROM A STRAIGHT_JOIN B ON A.id = B.id;

STRAIGHT_JOIN은 FROM 절에 명시된 모든 테이블 의 조인 순서를 고정한다. 일부만 고정하고 나머지는 옵티마이저에게 맡기는 것이 불가능하다.

3. JOIN_ORDER 옵티마이저 힌트

MySQL 8.0부터는 STRAIGHT_JOIN의 단점을 보완한 옵티마이저 힌트 가 추가되었다.

UPDATE /*+ JOIN_ORDER(dc, d) */
       (SELECT de.dept_no, COUNT(*) AS emp_count
        FROM dept_emp de
        GROUP BY de.dept_no) dc
       INNER JOIN departments d ON dc.dept_no = d.dept_no
SET d.emp_count = dc.emp_count;

조인 순서 힌트 종류

힌트	설명
JOIN_FIXED_ORDER	STRAIGHT_JOIN과 동일. FROM 절 순서대로 강제
JOIN_ORDER(t1, t2, …)	지정한 순서대로 조인 권고 (강제는 아님)
JOIN_PREFIX(t1, t2)	처음에 조인될 테이블 지정
JOIN_SUFFIX(t1)	마지막에 조인될 테이블 지정

STRAIGHT_JOIN vs JOIN_ORDER

-- STRAIGHT_JOIN: 키워드 방식, 모든 테이블 순서 고정
SELECT STRAIGHT_JOIN * FROM A, B, C, D WHERE ...;

-- JOIN_ORDER: 힌트 방식, 지정한 테이블만 순서 지정
SELECT /*+ JOIN_ORDER(A, C) */ * FROM A, B, C, D WHERE ...;
-- A와 C의 순서만 고정, B와 D는 옵티마이저가 결정

JOIN_ORDER의 장점:

• 일부 테이블의 순서만 지정하고 나머지는 옵티마이저에게 위임 가능
• 힌트 문법이므로 무시되어도 쿼리 실행에 문제 없음
• 가독성이 더 좋음

옵티마이저 힌트는 권고 사항이다. 옵티마이저가 더 나은 실행 계획을 찾으면 힌트를 무시할 수도 있다. 반면 STRAIGHT_JOIN은 강제 사항이다.

4. LATERAL 조인 (MySQL 8.0.14+)

지금까지의 방법은 모두 서브쿼리를 먼저 실행하고 그 결과를 파생 테이블로 만드는 방식이었다. LATERAL 조인 은 완전히 다른 접근 방식이다.

UPDATE departments d
INNER JOIN LATERAL (
    SELECT de.dept_no, COUNT(*) AS emp_count
    FROM dept_emp de
    WHERE de.dept_no = d.dept_no  -- 외부 테이블 참조
) dc ON dc.dept_no = d.dept_no
SET d.emp_count = dc.emp_count;

LATERAL이란?

일반적인 파생 테이블(서브쿼리)은 독립적 으로 실행된다. 외부 쿼리의 테이블을 참조할 수 없다.

-- 이건 에러! 서브쿼리에서 외부의 d를 참조할 수 없음
SELECT *
FROM departments d,
     (SELECT COUNT(*) AS cnt 
      FROM dept_emp de 
      WHERE de.dept_no = d.dept_no) sub;  -- 에러 발생

하지만 LATERAL 키워드를 붙이면, 서브쿼리가 외부 테이블의 각 행을 참조 할 수 있게 된다.

-- LATERAL을 붙이면 가능!
SELECT *
FROM departments d,
     LATERAL (SELECT COUNT(*) AS cnt 
              FROM dept_emp de 
              WHERE de.dept_no = d.dept_no) sub;

LATERAL의 동작 방식

LATERAL 서브쿼리는 for-each 루프 처럼 동작한다.

for each row in departments d:
    execute (SELECT COUNT(*) FROM dept_emp WHERE dept_no = d.dept_no)
    // 각 부서마다 서브쿼리 실행

LATERAL은 “상관 서브쿼리(Correlated Subquery)의 조인 버전”이라고 이해하면 쉽다. 외부 테이블의 값을 참조하여 행마다 다른 결과를 생성할 수 있다.

실행 계획으로 비교하기

실제로 각 방식의 실행 계획을 비교해보자. 테스트 환경은 departments 9건, dept_emp 10,000건이다.

1. 기본 파생 테이블

EXPLAIN ANALYZE
UPDATE departments d,
       (SELECT de.dept_no, COUNT(*) AS emp_count
        FROM dept_emp de
        GROUP BY de.dept_no) dc
SET d.emp_count = dc.emp_count
WHERE dc.dept_no = d.dept_no;

-> Update d (immediate)  (actual time=6.02..6.02 rows=0 loops=1)
    -> Nested loop inner join  (cost=9537 rows=93033) (actual time=6..6.01 rows=9 loops=1)
        -> Table scan on d  (cost=1.15 rows=9) (actual time=0.0236..0.0315 rows=9 loops=1)
        -> Index lookup on dc using <auto_key0> (dept_no=d.dept_no)  (cost=3107..3134 rows=103) (actual time=0.664..0.664 rows=1 loops=9)
            -> Materialize  (cost=3107..3107 rows=10337) (actual time=5.97..5.97 rows=9 loops=1)
                -> Group aggregate: count(0)  (cost=2073 rows=10337) (actual time=0.67..5.95 rows=9 loops=1)
                    -> Covering index scan on de using idx_dept_no  (cost=1039 rows=10337) (actual time=0.0075..5.19 rows=10000 loops=1)

이를 해석하면

-> Nested loop inner join
    -> Table scan on d (rows=9)                    ← 드라이빙: departments
    -> Index lookup on dc using <auto_key0>        ← 드리븐: 파생 테이블 (자동 인덱스)
        -> Materialize
            -> Group aggregate: count(0)
                -> Covering index scan on de using idx_dept_no (rows=10000)

분석:

• 옵티마이저가 departments(9건)를 드라이빙 으로 선택
• 파생 테이블에 <auto_key0> 자동 인덱스가 생성 됨
• 서브쿼리는 1번만 실행 되어 Materialize됨

2. STRAIGHT_JOIN / JOIN_ORDER

-- STRAIGHT_JOIN (파생 테이블 → departments 순서 강제)
EXPLAIN ANALYZE
UPDATE (SELECT de.dept_no, COUNT(*) AS emp_count
        FROM dept_emp de
        GROUP BY de.dept_no) dc
       STRAIGHT_JOIN departments d ON dc.dept_no = d.dept_no
SET d.emp_count = dc.emp_count;

-- JOIN_ORDER 힌트 사용
EXPLAIN ANALYZE
UPDATE /*+ JOIN_ORDER(dc, d) */
       (SELECT de.dept_no, COUNT(*) AS emp_count
        FROM dept_emp de
        GROUP BY de.dept_no) dc
       INNER JOIN departments d ON dc.dept_no = d.dept_no
SET d.emp_count = dc.emp_count;

STRAIGHT JOIN 의 실행계획은

-> Update d (buffered)  (actual time=6.59..6.59 rows=0 loops=1)
    -> Nested loop inner join  (cost=6857 rows=10337) (actual time=6.57..6.59 rows=9 loops=1)
        -> Table scan on dc  (cost=3107..3239 rows=10337) (actual time=6.56..6.56 rows=9 loops=1)
            -> Materialize  (cost=3107..3107 rows=10337) (actual time=6.56..6.56 rows=9 loops=1)
                -> Group aggregate: count(0)  (cost=2073 rows=10337) (actual time=0.764..6.54 rows=9 loops=1)
                    -> Covering index scan on de using idx_dept_no  (cost=1039 rows=10337) (actual time=0.0235..5.71 rows=10000 loops=1)
        -> Single-row index lookup on d using PRIMARY (dept_no=dc.dept_no)  (cost=0.25 rows=1) (actual time=0.00296..0.00297 rows=1 loops=9)

다음과같고,

JOIN_ORDER 의 실행계획은

-> Update d (buffered)  (actual time=6.52..6.52 rows=0 loops=1)
    -> Nested loop inner join  (cost=6857 rows=10337) (actual time=6.5..6.51 rows=9 loops=1)
        -> Table scan on dc  (cost=3107..3239 rows=10337) (actual time=6.48..6.48 rows=9 loops=1)
            -> Materialize  (cost=3107..3107 rows=10337) (actual time=6.48..6.48 rows=9 loops=1)
                -> Group aggregate: count(0)  (cost=2073 rows=10337) (actual time=0.687..6.47 rows=9 loops=1)
                    -> Covering index scan on de using idx_dept_no  (cost=1039 rows=10337) (actual time=0.0236..5.65 rows=10000 loops=1)
        -> Single-row index lookup on d using PRIMARY (dept_no=dc.dept_no)  (cost=0.25 rows=1) (actual time=0.00303..0.00304 rows=1 loops=9)

다음과 같다.

둘 다 실행계획이 거의 유사한데 이를 해석하면

-> Nested loop inner join
    -> Table scan on dc                            ← 드라이빙: 파생 테이블
        -> Materialize
            -> Group aggregate: count(0)
                -> Covering index scan on de using idx_dept_no (rows=10000)
    -> Single-row index lookup on d using PRIMARY  ← 드리븐: departments (PK 사용!)

분석:

• 파생 테이블이 드라이빙 으로 변경됨
• departments에서 PRIMARY 인덱스 를 사용하여 검색
• 서브쿼리는 여전히 1번만 실행

3. LATERAL 조인

EXPLAIN ANALYZE
UPDATE departments d
INNER JOIN LATERAL (
    SELECT de.dept_no, COUNT(*) AS emp_count
    FROM dept_emp de
    WHERE de.dept_no = d.dept_no
) dc ON dc.dept_no = d.dept_no
SET d.emp_count = dc.emp_count;

실행계획은 다음과 같고

-> Update d (immediate)  (actual time=6.29..6.29 rows=0 loops=1)
    -> Nested loop inner join  (cost=7.1 rows=9) (actual time=0.744..6.29 rows=9 loops=1)
        -> Invalidate materialized tables (row from d)  (cost=1.15 rows=9) (actual time=0.0251..0.0337 rows=9 loops=1)
            -> Table scan on d  (cost=1.15 rows=9) (actual time=0.0249..0.033 rows=9 loops=1)
        -> Index lookup on dc using <auto_key0> (dept_no=d.dept_no)  (cost=0.842..1.13 rows=2) (actual time=0.694..0.694 rows=1 loops=9)
            -> Materialize (invalidate on row from d)  (cost=0.55..0.55 rows=1) (actual time=0.693..0.693 rows=1 loops=9)
                -> Aggregate: count(0)  (cost=0.45 rows=1) (actual time=0.691..0.691 rows=1 loops=9)
                    -> Covering index lookup on de using idx_dept_no (dept_no=d.dept_no)  (cost=0.35 rows=1) (actual time=0.00472..0.658 rows=1111 loops=9)

이를 해석하면

-> Nested loop inner join
    -> Invalidate materialized tables (row from d)
        -> Table scan on d (rows=9)                ← 드라이빙: departments
    -> Index lookup on dc using <auto_key0>
        -> Materialize (invalidate on row from d)  ← ⚠️ 행마다 재실행!
            -> Aggregate: count(0)
                -> Covering index lookup on de (rows=1111, loops=9)  ← loops=9!

분석:

• Invalidate materialized tables : 외부 행이 바뀔 때마다 서브쿼리 재실행
• loops=9 : 서브쿼리가 9번 반복 실행 됨
• 각 루프에서 약 1,111건씩 스캔

실행 계획 비교 요약

방식	드라이빙	드리븐 인덱스	서브쿼리 실행	특징
기본 파생 테이블	departments	auto_key0	1번	옵티마이저가 결정
STRAIGHT_JOIN	파생 테이블	PRIMARY	1번	PK 인덱스 활용
JOIN_ORDER	파생 테이블	PRIMARY	1번	STRAIGHT_JOIN과 동일
LATERAL	departments	auto_key0	N번 (loops)	행마다 재실행

핵심 차이점

────────────────────────────────────────────────────────────────
              일반 파생 테이블 vs LATERAL 파생 테이블             
────────────────────────────────────────────────────────────────
                                                                
  [일반 파생 테이블] - 1번 실행                                   
                                                                
  1. 서브쿼리 독립 실행 (전체 데이터 집계)                        
     ┌───────────────────────┐                                    
     │ dept_no │ emp_count   │                                    
     │   d001  │   1,111     │                                    
     │   d002  │   1,111     │                                    
     │   ...   │    ...      │                                    
     └───────────────────────┘                                    
                                                                
  2. 결과를 departments와 조인                                   
                                                                
────────────────────────────────────────────────────────────────
                                                                
  [LATERAL 파생 테이블] - N번 실행 (loops)                        
                                                                
  departments의 각 행에 대해 서브쿼리 반복:                       
                                                                
  d001 → (SELECT COUNT(*) WHERE dept_no='d001') → 1,111        
  d002 → (SELECT COUNT(*) WHERE dept_no='d002') → 1,111        
  d003 → (SELECT COUNT(*) WHERE dept_no='d003') → 1,111        
  ...  → (9번 반복)                                             
                                                                
────────────────────────────────────────────────────────────────

LATERAL 주의사항: 외부 테이블의 행 수만큼 서브쿼리가 반복 실행된다. 부서가 9개면 9번, 1,000개면 1,000번 실행된다. 전체 집계 후 조인하는 경우에는 일반 파생 테이블이 훨씬 효율적이다.

언제 어떤 방식을 사용할까?

일반 파생 테이블 (기본 선택)

• 전체 데이터를 집계한 후 조인하는 경우
• 대부분의 GROUP BY + JOIN UPDATE 상황

STRAIGHT_JOIN / JOIN_ORDER

• 옵티마이저가 비효율적인 조인 순서를 선택할 때
• 파생 테이블을 드라이빙으로 사용하고 싶을 때
• 드리븐 테이블에 좋은 인덱스(PK 등)가 있을 때

LATERAL

• 외부 테이블의 값에 따라 조건부 집계 가 필요할 때
• 각 행마다 TOP-N 을 조회해야 할 때
• 외부 테이블의 행 수가 적을 때

-- LATERAL이 유용한 예: 각 부서의 최근 입사자 1명
SELECT d.*, latest.*
FROM departments d
INNER JOIN LATERAL (
    SELECT e.emp_no, e.hire_date
    FROM employees e
    JOIN dept_emp de ON e.emp_no = de.emp_no
    WHERE de.dept_no = d.dept_no
    ORDER BY e.hire_date DESC
    LIMIT 1  -- 각 부서마다 1명만!
) latest ON TRUE;

마치며

JOIN UPDATE에서 GROUP BY를 사용할 수 없는 것은 단순한 문법 제약이 아니라, 집계와 행 단위 변경이라는 두 가지 상충되는 개념 을 조화시키기 어렵기 때문이다.

핵심 내용을 정리하면 다음과 같다.

문제의 원인:

• JOIN UPDATE는 행 단위 변경, GROUP BY는 그룹 단위 집계
• 집계 결과를 “어떤 행에” 적용할지 모호함
• MySQL은 이런 모호한 상황을 문법적으로 차단

해결 방법:

• 파생 테이블 : 서브쿼리에서 미리 집계 후 조인 (가장 일반적)
• STRAIGHT_JOIN : 조인 순서 강제 (FROM 절 순서대로)
• JOIN_ORDER 힌트 : 조인 순서를 힌트로 권고 (MySQL 8.0+)
• LATERAL 조인 : 행마다 서브쿼리 실행 (MySQL 8.0.14+)

성능 관점:

• 일반 파생 테이블은 서브쿼리를 1번 실행
• LATERAL은 외부 테이블 행 수만큼 N번 실행
• 전체 집계에는 파생 테이블, 행별 조건부 처리에는 LATERAL

실행 계획은 EXPLAIN ANALYZE 로 확인하고, 상황에 맞는 방식을 선택하자.

---

References

• Real MySQL 8.0
• MySQL 8.0 Reference Manual - UPDATE Statement
• MySQL 8.0 Reference Manual - Optimizer Hints
• MySQL 8.0 Reference Manual - Lateral Derived Tables