[SQL] ORDER BY를 했을때 NULL이 있으면 어떻게 정렬될까?

결론부터

DBMS마다 다르다.
PostgreSQL, Oracle은 NULL을 마지막으로 정렬하고, MySQL과 SQLite는 NULL을 첫번째로 정렬한다. DESC를 적용하면 그 반대로 정렬된다.

	ASC	DESC
NULL 처음	MySQL, SQLite	PostgreSQL, Orcle
NULL 마지막	PostgreSQL, Orcle	MySQL, SQLite

참고 : https://learnsql.com/blog/how-to-order-rows-with-nulls/

How ORDER BY and NULL Work Together in SQL

아래 작성한 쿼리는 MySQL 문법을 기준으로 작성하였습니다.

ORDER BY

ORDER BY는 SQL에서 특정 기준으로 정렬할 때 사용된다. 아래 쿼리는 table_name이라는 테이블을 가져올 때, column1을 기준으로 오름차순 정렬해서 가져오라는 뜻이다.

SELECT *
FROM table_name
ORDER BY column1

만약 column1의 Data Type이 숫자라면 1,2,3 순으로 정렬될 것이고, Character이라면 알파벳순으로 사전식 배열로 정렬될 것이고, Date라면 현재를 기준으로 가장 오래된 날짜부터 가장 최신 날짜 순으로 정렬 될 것이다.

아래 쿼리는 release_year 컬럼을 기준으로 오름차순 정렬한 쿼리의 결과이다.

release_year 기준으로 오름차순 정렬

ASC, DESC

오름차순과 내림차순

	ASC(오름차순)	DESC(내림차순)
숫자	작은 수에서 큰 수으로	큰 수에서 작은 수로
문자	사전식 배열 순으로(알파벳 순으로)	사전식 배열 역순으로(알파벳 역순으로)
날짜	오래된 날짜에서 최신 날짜으로	최신 날짜에서 오래된 날짜으로

위의 쿼리에서 생략했지만, ORDER BY 구문 마지막에 ASC가 생략되어 있다. 생략하지 않고 쓰면 아래의 쿼리처럼 나타낼 수 있다. ASC는 ascending의 약자로 오름차순을 의미한다.

SELECT *
FROM table_name
ORDER BY column1 ASC

만약 내림차순으로 정렬하고 싶으면 ASC 자리에 DESC를 사용하면 된다. DESC는 descending의 약자로 내림차순을 의미한다.

SELECT *
FROM table_name
ORDER BY column1 DESC

아래 쿼리는 release_year 컬럼을 기준으로 내림차순 정렬한 쿼리의 결과이다.

release_year 기준으로 내림차순 정렬

두 개 이상의 기준으로 정렬하기

두 개 이상의 기준을 이용하여 테이블을 정렬할 수 있다. 아래 쿼리는 두 개의 기준을 이용하여 정렬하는 쿼리이다. 기준을 ",(쉼표)"로 구분하면된다. 두 개의 기준 모두 오름차순으로 정렬하라는 의미이다.

SELECT *
FROM table_name
ORDER BY column1, column2

column1을 기준으로 먼저 오름차순 정렬한 후, column1이 같은 값인 행끼리 column2를 기준으로 정렬한다. 이렇게 두 개의 컬럼으로 정렬할 때에는 두 기준의 순서가 중요하다. 만약 두 기준의 순서가 바뀌면 정렬 결과도 달라진다.

예시

테이블 예시

4개의 컬럼을 가진 아래의 table을 type과 genres를 기준으로 오름차순 정리하는 쿼리는 다음과 같다.

SELECT *
FROM table_name
ORDER BY type, genres

먼저 type을 기준으로 오름차순 정렬한다. type은 character이므로 알파벳순으로 사전식 배열로 오름차순 정렬한다.

type 컬럼을 기준으로 오름차순 정렬

이후, type 값이 같은 값끼리 genres를 기준으로 오름차순 정렬한다.

genres 컬럼을 기준으로 오름차순 정렬

여기서 만약 ratings가 높은 순으로 정렬하고 싶다면, 다음과 같이 쿼리를 짜면 된다. ratings는 높은 숫자가 제일 위로 가도록 내림차순 정렬해야 하기 때문에 컬럼 뒤에 DESC를 붙여준다.

SELECT *
FROM table_name
ORDER BY type, genres, ratings DESC

 

만약 컬럼 값으로 NULL이 있다면?

결론은 위에서 말했듯이, DBMS마다 NULL을 정렬하는 방식이 다르다. 이 글은 MySQL 문법을 기준으로 진행되므로 MySQL의 NULL 정렬 방법을 참고하면 된다.

참고로 NULL은 '해당 행의 컬럼값이 존재하지 않는다'를 의미한다. 숫자 0과는 다른 의미이다. 0과 NULL의 차이는 값의 존재유무이다. 숫자 0이 적혀 있다면 그것은 0이라는 데이터가 있는 것이고, NULL은 아예 값이 존재하지 않음을 의미한다.

0과 NULL

MySQL에서는 기본적으로 NULL 값이 포함된 컬럼을 기준으로 정렬하면 NULL 값을 위로 정렬한다.

NULL이 포함된 컬럼을 기준으로 오름차순 정렬

DESC를 사용하면 역순으로 NULL값은 가장 아래로 정렬된다.

NULL이 포함된 컬럼을 기준으로 내림차순 정렬

정렬은 유지하면서 NULL값은 뒤로 보내고 싶은데...

DESC처럼 전부 역순으로 순서를 뒤집는 것이 아닌, 오름차순 정렬을 유지하면서 NULL 값을 뒤로 보내고 싶다면 MySQL에서는 다음과 같은 방법을 사용하면 된다.

두 개의 기준으로 정렬 (모든 타입의 컬럼에 이용 가능)

오름차순 유지하면서 NULL만 뒤로 보내기

두 가지 이상의 기준으로 정렬하는 방법을 응용한 방법이다. MySQL말고 SQLite나 PostgreSQL에서는 이러한 기능을 제공하는 예약어가 있지만 MySQL에서는 그러한 기능을 제공하지 않아서 이 방법을 사용해야 한다.

위의 방법을 나눠서 봐보자. 

정렬 기준의 첫번째 기준인 'director IS NULL' 은 director 값이 NULL 인지 확인하는 조건문이다. 이 조건문이 정렬 기준으로 사용되면 NULL 값인 경우에는 1로 NULL 값이 아닌 경우에는 0으로 인식하여 정렬한다. 따라서, 해당 기준을 오름차순으로 정렬하면 NULL인 행은 모두 아래로 보낼 수 있다.

Not Null : 0, Null : 1

해당 기준을 이용하여 정렬 후, director를 기준으로 오름차순 정렬하면 오름차순은 유지하면서 NULL값은 모두 아래로 보낼 수 있다.

COALSCE 함수 이용(모든 타입의 컬럼에 이용 가능)

COALSCE 함수는 NULL값을 특정 값으로 대체하는 함수이다. 아래 쿼리는 column1의 NULL 값을 2021라는 값으로 대체한다.

SELECT COALSCE(column1, 2021)

이 함수를 이용하여 오름차순 정렬을 유지하면서 NULL 값을 뒤로 보낼 수 있다. 해당 컬럼의 데이터 타입이 숫자라면 그 컬럼의 가장 큰 숫자에 1을 더해서 NULL값을 대체한다음 오름차순 정렬을 진행하면 된다. 

SELECT *
FROM table_name
ORDER BY COALESCE(column1, (SELECT MAX(column1) + 1 FROM table_name))

마이너스 연산자  "-" 이용 (숫자 타입 컬럼에만 이용 가능)

숫자 타입의 컬럼을 기준으로 정렬할 때 오름차순 정렬을 유지하면서 NULL 값을 뒤로 보내기 위해서 마이너스 연산자 "-" 를 사용할 수 있다. column1의 값을 역순으로 변경하여 정렬하는 것이다.

SELECT *
FROM table_name
ORDER BY -column1 DESC

아래 사진과 같이 정렬이 이루어진다. 숫자의 정렬은 유지되고, NULL 값만 아래로 보낼 수 있는 것을 알 수 있다.

마이너스 연산자를 이용한 NULL 아래로 보내기

 

SQL 공부한 내용을 정리하기 위해 작성하는 글입니다. 아래의 데이터를 python을 이용하여 SQL에 저장하여 실습에 사용하였습니다. 

작업환경
MySQL Server 8.2.0
MySQL Workbench 8.0.34
Python 3.11.2
사용한 데이터 : Netflix Movies and TV Shows(kaggle) 

데이터 출처 :https://www.kaggle.com/datasets/shivamb/netflix-shows

Netflix Movies and TV Shows