2. 5장 정렬할 때 null 처리하기

Q. EMP 테이블의 결과를 COMM 기준으로 정렬할 때 / 필드가 NULL을 허용한다 / 이 때 NULL을 마지막에 정렬할지를 지정하는 방법이 필요하다

A-1. 원하는 데이터 형태에 따라 NULL값이 허용된 열을 기준으로 오름차순 또는 내림차순으로 정할 수 있다

A-2. 특정 RDBMS가 NULL값을 정렬하는 방법에 따라 NULL값이 허용된 열을 기준으로 오름차순 또는 내림차순으로 정할 수 있다

이러한 해법은 NULL값을 허용하는 열에, NULL이 아닌 값이 포함된 경우, 요청한 내용에 따라 오름차순 또는 내림차순으로 정렬되도록 한다.

모든 NULL값을 마지막으로 정렬하려는 경우 CASE WHEN THEN END 구문을 사용하여 열을 조건부로 정렬할 수 있다.

case: MySQL / Postgre / Oracle

• 값이 NULL일 때 CASE식을 사용하여 플래그를 수행한다
• 두 개의 값을 지니는 플래그를 갖는 것이다

플래그란?

• 논리 조건을 두는 것이다.
  HTML tag를 사용할 때 style='display:none'으로 두면 코드는 남아있지만 그 코드의 내용은 출력되지 않는다.
• SQL에서도 삭제 여부•사용 여부•시작일 ~ 종료일 등 상태 코드에 쓰인다

/* NULL이 아닌 COMM을 우선 오름차순으로 정렬하고, 모든 NULL을 마지막에 출력하기 */

# ename, sal, comm을 출력할 것이다
select ename, sal, comm
	# 아래의 결과로부터(결과적으로 alias가 x로 붙은 테이블로부터)
    from(
    	# ename, sal, comm, case구문을 출력할 것이다
        select ename, sal, comm,
        		# 만약 comm의 값이 null이라면 대신에 0을 넣는다
                case when comm is null then 0
                # 그렇지 않다면 1을 넣어준다
                else 1
                # 이 case 구문의 칼럼명은 is_null으로 할 것이다
                end as is_null
            from emp
        ) x # 이 산출물의 별명은 x라고 지어놓을 것이다
        # 위의 결과물로부터 ename, sal, comm을 출력할 것이며
        # 정렬은 is_null칼럼을 기준으로 내림차순 정렬한다
        # 그 후 comm값으로 오름차순 정렬한다
        order by is_null desc, comm
>>
ENAME	SAL		COMM
TURNER	1500	0
ALLEN	1600	300
WARD	1250	500
MARTIN	1250	1400
SCOTT	3000	 - 
FORD	3000	 - 
MILLER	1300	 - 
ADAMS	1100	 - 
JAMES	950	 	 - 
JONES	2975	 - 
CLARK	2450	 - 
BLAKE	2850	 - 
KING	5000	 - 
SMITH	800	 	 - 

FROM절의 쿼리만 따로 실행해보겠다.

select ename, sal, comm,
			case when comm is null then 0
			else 1
			end as is_null
		from emp
>>
ENAME	SAL		COMM	IS_NULL
KING	5000	 - 		0
BLAKE	2850	 - 		0
CLARK	2450	 - 		0
JONES	2975	 - 		0
SCOTT	3000	 - 		0
FORD	3000	 - 		0
SMITH	800	 	 - 		0
ALLEN	1600	300		1
WARD	1250	500		1
MARTIN	1250	1400	1
TURNER	1500	0		1
ADAMS	1100	 - 		0
JAMES	950	 	 - 		0
MILLER	1300	 - 		0

이 산출물을 다시 ORDER BY is_null DESC, comm으로 정렬한 것이다.

이번엔 NULL을 처음에 나타낸 후, NULL이 아닌 'COMM'은 오름차순 해보겠다.

select ename, sal, comm
    from(
        select ename, sal, comm,
                case when comm is null then 0
                else 1
                end as is_null
            from emp
        ) x
        order by is_null, comm

구조는 모두 동일하며 마지막 ORDER BY구문의 정렬조건만 바뀌었다.
is_null을 오름차순으로 정렬하여 NULL값 즉, 0이 가장 먼저 출력되도록 했다.

case: Oracle Only

Oracle만 가능한 방식이 있다.
ORDER BY절에 NULLS FIRST 또는 NULLS LAST를 사용해주면 된다.

/* nulls last이므로 모든 NULL은 마지막에 나타내고, NULL이 아닌 COMM을 오름차순 정렬 */
select ename, sal, comm
	from emp
    order by comm nulls last
>>
ENAME	SAL		COMM
TURNER	1500	0
ALLEN	1600	300
WARD	1250	500
MARTIN	1250	1400
SCOTT	3000	 - 
FORD	3000	 - 
MILLER	1300	 - 
ADAMS	1100	 - 
JAMES	950	 	 - 
JONES	2975	 - 
CLARK	2450	 - 
BLAKE	2850	 - 
KING	5000	 - 
SMITH	800	 	 -

진짜 되네...

• NULLS FIRST는 NULL값부터 먼저 나오도록 해놓겠다는 것
  당연히 NULLS LAST는 반대가 되겠다

• 나머지 NULL이 아닌 값은 DESC 또는 ASC를 활용해서 정렬해주면 되겠다

Oracle과 같이 같은 열에서 NULL이 아닌 값을 수정하지 않고도 NULL값을 처음 또는 마지막으로 정렬하는 방법을 제공하지 않는 한, 보조 열이 필요하다.

case: Not Oracle

select ename, sal, comm, case
		when comm is null then 0
        else 1
        end as is_null
        from emp
>>
ENAME	SAL		COMM	IS_NULL
KING	5000	 - 		0
BLAKE	2850	 - 		0
CLARK	2450	 - 		0
JONES	2975	 - 		0
SCOTT	3000	 - 		0
FORD	3000	 - 		0
SMITH	800	 	 - 		0
ALLEN	1600	300		1
WARD	1250	500		1
MARTIN	1250	1400	1
TURNER	1500	0		1
ADAMS	1100	 - 		0
JAMES	950	 	 - 		0
MILLER	1300	 - 		0

여기서 'IS_NULL'이라는 추가 열의 목적은 NULL값을 식별하여 처음 또는 마지막으로 모두 정렬하는 것이다.

이 'IS_NULL'에서 반환한 값을 사용하면, 'COMM' 정렬과 별개로 NULL을 처음 또는 마지막으로 쉽게 정렬할 수 있게 된다.

• 전체적으로 쿼리에 쓰인 'IS_NULL'은 플래그의 역할을 했다
• 정렬에 필요한 상태코드로 쓰인 형태로 보여진다

---