[DB/SQL] Server SQL Modes - "Group By" 를 사용할 때, 알아야 할 내용

목차

시작하며

https://earthteacher.tistory.com/203

[MySQL/Oracle] 즐겨찾기가 가장 많은 식당 정보 출력하기

위 문제를 해결하면서, GROUP BY 를 사용할 때 확실히 알고 가야 할점에 대해 알아보자.

문제해결

아래와 같은 Orders 테이블이 있다고 가정해보자

+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 7000    | Jan   |
| 3    | 6000    | Jan   |
+------+---------+-------+

만약 우리가 month 컬럼을 기준으로 revenue 컬럼의 값들 중 가장 큰 값을 반환하고 그 값의 id 필드의 값도 반환하고 싶다면 어떻게 해야 할까?

그래서 만약 아래와 같이 쿼리문을 작성하면 어떻게 될까?

SELECT
    id
   ,month
   ,MAX(revenue) AS revenue
    FROM Orders
    GROUP BY month

revenue 컬럼의 값은 집계함수 MAX를 사용했기 때문에 복수의 컬럼 중 어떤 값을 반환해야 하는지 결정이 되었고, month 컬럼 또한 GROUP BY 절의 기준 컬럼이므로 반환해야 하는 값이 명확히 정해져 있다.

 

하지만 id 컬럼은 어떻게 될까?

GROUP BY 절이 동작하기 직전을 테이블로 표시하면 아래와 같은 상태일 것이다.

+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 8000    | Jan   |
| 3    | 8000    | Jan   |
+------+---------+-------+

다시말해 DBMS 입장에선 어떤 id 컬럼의 값을 반환해야 하는지 알 수 없기 때문에 오류를 반환하게 된다. 이게 기본적인 MySQL의 작동 방식이다.

 

헌데, 프로그래머스에서 위 쿼리를 실행했을 때, 오류가 발생하지 않고 id 컬럼의 값으로 1 혹은 어떤 값들 중 임의 값 하나를 무작위로 반환할것이다.

 

이는 MySQL 내에 존재하는 sql_mode 값 때문이다. 관련한 내용은 공식 문서를 확인해보자.

 

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes

 

먼저 프로그래머스에서 아래 쿼리로 sql_mode 를 조회해보면 null이 반환되는걸 확인할 수 있다.

현 상황에서는 GROUP BY 부분이 궁금한데, MySQL의 SQL모드 중, GROUP BY 절과 연관된 ONLY_FULL_GROUP_BY 옵션이 sql_mode 존재하는지 확인해봤을 때, 위에서 확인해본것처럼 프로그래머스의 MySQL 서버에는 어떠한 값도 존재하지 않는다.

 

ONLY_FULL_GROUP_BY 옵션의 경우, 기본적으로는 켜져있으며(설정값 존재) 이때, GROUP BY 절을 사용할 때 집계 함수 등으로 선택되지 못해 임의의 값 중 어떤 값을 반환해야 할 지 모르는 필드가 존재하면 오류를 반환하게 되어있다. 

 

그런데, 프로그래머스 MySQL 서버의 경우 어떠한 모드도 취하고 있지 않고 있기 때문에 해당 ONLY_FULL_GROUP_BY 옵션이 꺼져있는 상태이다(설정값 존재 X)

 

따라서 MySQL 내부에서 어떤 값을 골라야 할 지 모르는 임의의 값 중 무작위로 하나의 값을 고르게 되고 보통 자동적으로 가장 첫 번째 값을 고르게 되어 있다.

 

따라서, 결국 맨 위 문제에서 GROUP BY 절의 기준이 되는 필드가 아니거나 집계 함수를 통해 어떤 값을 반환해야 할 지 결정되지 않은 컬럼을 SELECT 절에 사용할 경우, 문법적으로 틀린 SQL문이다.