[MySQL] group by와 only_full_group_by 모드 완벽 이해하기

MySQL을 사용하다 보면 아래 오류 메시지를 만날 때가 있습니다.

this is incompatible with sql_mode=only_full_group_by

 

MySQL에서 기본적으로 활성화되어 있는 'ONLY_FULL_GROUP_BY' 모드가 내가 작성한 SQL문과 충돌하면서 발생하는 오류 메시지인데요. 왜 이런 오류가 발생하는지, group by를 사용할 때 주의할 점은 무엇인지, 어떻게 이런 오류를 해결할 수 있는지 알아보겠습니다.

 

1. ONLY_FULL_GROUP_BY 모드란?

MySQL의 ONLY_FULL_GROUP_BY 모드는 표준 SQL 가이드라인에 부합하지 않는 (GROUP BY절이 포함된) 쿼리를 방지하기 위한 설정입니다. 이 모드를 활성화되면, GROUP BY 절에 포함되지 않은 비집계 컬럼을 SELECT 절에서 사용할 수 없습니다. 

# 부서 아이디(department_id)에 속한 직원 수(COUNT(emp_id))를 계산하는 쿼리
SELECT department_id, first_name, COUNT(emp_id) AS employee_count
FROM employees
GROUP BY department_id;

 

위 쿼리를 실행하면 익숙한 'this is incompatible with sql_mode=only_full_group_by' 오류 메시지가 출력됩니다. first_name이라는 비집계 컬럼이 GROUP BY에 명시되지 않았는데, SELECT 절에 포함했기 때문이죠. 그렇다면 왜 이런 오류가 출력되는 것일까요?

 

먼저 제대로 실행되는 쿼리를 다시 작성해 봅시다.

SELECT department_id, COUNT(emp_id) AS employee_count
FROM employees
GROUP BY department_id;

 

위 쿼리를 실행하면, 실행 순서에 따라 GROUP BY 절이 SELECT보다 먼저 실행됩니다. 이때, 1) 그룹핑 기준 컬럼과 2) 집계 함수에 사용되는 컬럼으로 구성된 임시 테이블이 메모리에 생성됩니다. 여기서 그룹핑 기준 컬럼은 GROUP BY 절에 포함된 department_id이고, 집계 함수 COUNT에 사용되는 컬럼은 emp_id 이므로 아래와 같은 임시 테이블이 생성되는 것이죠. 

| department_id | emp_id |
|---------------|--------|
| 1             | 134    |
| 2             | 135    |
| 2             | 166    |
| 3             | 167    |

 

이 임시 테이블을 가지고 SELECT 문에 명시된 내용을 처리하게 됩니다. 이때, GROUP BY 절에 포함하지 않은 컬럼, 예를 들면 first_name과 같은 컬럼명을 SELECT에 포함하면 어떻게 될까요? 각 부서별로 여러 직원이 있을텐데, 그 중 어떤 직원의 first_name을 표시줘야할지 판단이 애매하게 됩니다. 따라서 표준 SQL에서 이런 것들을 막아두는 것이죠.

 

그럼 ONLY_FULL_GROUP_BY 모드는 무엇일까요? 이 모드를 해제하면 그룹핑 기준 컬럼과 집계함수에 이용되는 컬럼 외에도 모든 컬럼을 임시 메모리에 저장하게 됩니다. 따라서 SELECT 절에서 사용할 수 있게 되는 것이죠. 다만, 이 경우 출력되는 결과는 의미없는 데이터인 경우가 많으며, 대부분 오해를 불러일으키는 결과물을 출력하므로 주의해야 합니다.

 

2. 해결방법

이런 문제를 해결하기 위해 주로 다음과 같은 3가지 방법을 사용합니다.

방법 1. GROUP BY 절에 모든 비집계 컬럼을 포함한다

SELECT department_id, first_name, COUNT(emp_id) AS employee_count
FROM employees
GROUP BY department_id, first_name;

 

위와 같이 GROUP BY 절에 first_name 컬럼을 포함하면, 임시 메모리에 저장되는 테이블에도 first_name이 같이 들어가기 때문에 SELECT 절에서 사용이 가능합니다. 하지만 이 경우에 부서별/이름별 직원 수가 출력되므로, 부서별로 같은 이름을 가진 직원 외에는 모두 직원 수가 1로 출력되는 결과를 가져오게 됩니다.

 

이런 방법은 그룹핑하려는 컬럼들 사이의 관계가 1:1인 경우에 효과적인데요. 방법 2에서 설명하는 예시에서는 방법 1을 똑같이 유효하게 사용할 수 있습니다.

 

 

방법 2. 비집계 컬럼 대신 적절한 집계 함수를 넣어서 쿼리를 작성합니다

만약, first_name 대신 dept_name 처럼 그룹핑 기준인 department_id와 1:1 관계를 맺는 컬럼을 가져오는 경우에는 어떨까요?

이때 아래처럼 적절한 집계 함수를 넣어서 쿼리를 작성할 수 있습니다.

SELECT department_id, MIN(dept_name), COUNT(emp_id) AS employee_count
FROM employees
GROUP BY department_id;

 

각 department_id에 매칭되는 부서명(dept_name)은 모두 동일할 것이므로, 그 중 아무 값이나 가져와도 무방합니다. 이 경우 dept_name에 MIN 같은 집계함수를 적용하면 GROUP BY 절에 dept_name을 포함하지 않고도 오류를 해결할 수 있습니다.

 

이때 아래와 같은 결과를 얻을 수 있습니다.

| department_id | dept_name | employee_count |
|---------------|-----------|----------------|
| 1             | HR        | 10             |
| 2             | Finance   | 15             |
| 3             | IT        | 7              |

 

 

방법 3. ONLY_FULL_GROUP_BY 모드를 비활성화 합니다

먼저 MySQL 기준으로 ONLY_FULL_GROUP_BY 모드 활성화 여부는 아래와 같이 체크할 수 있습니다.

SELECT @@sql_mode;

 

이때 출력되는 결과에 ONLY_FULL_GROUP_BY가 포함되어있다면 활성화된 상태입니다. 활성화/비활성화는 아래와 같이 진행합니다.

# 모드 활성화
SET sql_mode = 'ONLY_FULL_GROUP_BY';

# 모드 비활성화
SET sql_mode = '';

 

 

3. ONLY_FULL_GROUP_BY 장단점: 모드를 해제해야 할까?

결론부터 말씀드리면 ONLY_FULL_GROUP_BY 모드는 해제하지 않는 것이 좋습니다. 물론, 이 모드가 어떻게 작동하는지 다 아는 상태라면 특정 조건에서 유연하게 쿼리를 작성하면서 더 간결한 쿼리를 작성할 수도 있습니다. 동시에 정확한 결과값을 출력할수도 있고요. 

 

하지만 데이터의 정확한 출력이 무엇보다 중요하므로, 일관성을 보장해주는 이런 모드는 해제하지 않는 것이 좋다는 의견입니다. 실수를 방지하고 예상치 못한 결과를 출력하는 것을 막아주기 때문이죠. 또한, ONLY_FULL_GROUP_BY 모드를 해제하고 작성한 쿼리가 다른 환경에서는 동작하지 않을 수 있기 때문에 협업의 관점에서도 이 모드는 유지해주는게 좋을 것 같습니다.