MySQL NULL 정렬 (ORDER BY)

SQL에서 `NULL`은 '값이 없음'을 의미하지만, 이는 단순히 비어 있다는 의미가 아니라 '값이 아직 정해지지 않았거나 알 수 없는 상태'를 뜻합니다. 이처럼 `NULL` 값은 비교나 정렬에서 특별한 처리를 필요로 하며, DBMS마다 `NULL` 정렬 처리 방식이 다릅니다.

DBMS마다 다른 NULL 정렬 처리 방식

DBMS	오름차순 (ASC) 정렬 시 NULL 위치	내림차순 (DESC) 정렬 시 NULL 위치
MySQL	맨 위 (가장 작은 값 취급)	맨 아래 (가장 큰 값 취급)
PostgreSQL	맨 아래	맨 위
Oracle	맨 아래	맨 위
SQL Server	맨 위 (기본 설정 시)	맨 아래 (기본 설정 시)

 

표준 ANSI 에 따르면 `NULL` 값을 어떻게 취급할지에 대해 정의하지 않아 데이터베이스마다 다르게 취급할 수 있습니다.

MySQL은 `NULL` 값을 가장 작은 값으로 취급하는데, `NULL`을 허용하는 컬럼에 ORDER BY절을 통해 오름차순(ASC) 정렬을 수행하면 최상단에 `NULL` 값을 위치하게 할 수 있습니다.

NULL 값을 허용하는 컬럼에서 NULL을 가장 큰 값으로 처리하는 방법

MySQL에서 NULL은 가장 작은 값으로 취급되기 때문에, `ORDER BY [컬럼명] ASC`을 사용하면 `NULL` 값이 먼저 출력됩니다.

하지만 NULL을 가장 큰 값으로 취급하고 싶을 때는 `ORDER BY [대상 컬럴명] IS NULL ASC, [대상 컬럼명] ASC`를 사용하면 됩니다.

SELECT *
FROM TBL_MY_TABLE
ORDER BY NAME IS NULL ASC, NAME ASC;

동작 흐름

• `[대상 컬럼명] IS NULL`은 해당 컬럼이 NULL이면 1, 아니면 0을 반환
• 따라서 먼저 `[대상 컬럼명] IS NULL ASC`로 정렬하면 NULL이 아닌 값이 먼저 나옴
• 이후 `[대상 컬럼명] ASC` 정렬로 실제 값 기준 정렬을 수행

즉, 첫 번째 정렬 기준으로 `NULL` 여부를 확인해 `NULL`을 뒤로 밀고, 두 번째 기준으로는 실제 값을 정렬하는 방식입니다. 이 방식은 MySQL에서 `NULLS LAST` 효과를 흉내 내는 패턴으로 자주 사용된다고 합니다.

정렬 시 주의사항

ORDER BY로 정렬을 수행할 때 주의할 점은, ORDER BY로 정렬을 효과적으로 사용하려면 정렬하려는 컬럼들에 인덱스가 어떻게 구성되어 있는지도 확인해야 합니다.

 

MySQL 인덱스는 B-Tree 구조로 항상 오름차순으로 정렬되어 있기 때문에 (별도로 정렬 순서를 바꾸지 않는 이상) ORDER BY 절에 사용되는 모든 컬럼이 오름차순이거나 내림차순이어야 인덱스를 사용할 수 있습니다. (아직 부족한 경험으로 모든 컬럼이 오름차순이거나 내림차순으로 되는 쿼리가 흔치 않은 것 같습니다.)

 

이때 인덱스가 여러 컬럼(`COL_1`, `COL_2`, `COL_3` 순서)으로 구성되어 있다면, 모든 컬럼이 정렬 조건에 사용되지 않더라도 반드시 앞에 있는 `COL_1` 컬럼의 왼쪽부터(left most) 일치해야 합니다. (순서도 일치해야 합니다.)

 

그 외에도, GROUP BY 절과 함께 ORDER BY절이 사용될 때는 1) 둘 다 인덱스를 타거나, 둘 다 인덱스를 못타고, 인덱스를 타기 위해서는 2) GROUP BY 절과 ORDER BY 절에 있는 모든 컬럼의 순서가 동일해야 한다는 제약도 있지만 NULL 처리 관련 글의 범위를 벗어나므로 RealMySQL 8.0의 64~65p를 참고하면 좋을 것 같습니다.

마치며

MySQL로 되어있는 데이터베이스에서 작업을 하다, NULL 값에 대한 정렬 순서를 바꿔야 했는데, 이번 글로 정리를 해봤습니다. 정렬 결과에 영향을 줄 수 있는 `NULL` 처리 방식은 데이터베이스마다 다르기 때문에 간단한 쿼리로 한 번은 꼭 확인하고 쓰는 것이 예상치 못한 정렬 오류를 방지하기 위해서라도 필요하다고 생각합니다.

참고

• https://ismydream.tistory.com/158
• RealMySQL 8.0