(SQL) null과 관련된 조건식 _ coalesce, nullif, ifnull

25 Jul 2022 in 가이던스 on SQL

(SQL) NULL과 관련된 조건식 _ coalesce, nullif, ifnull

✋🏾 Google BigQuery을 이용하여 쓰인 포스팅입니다.

---

1. NULL과 관련된 조건식 _ coalesce, nullif, ifnull 란?

BigQuery 조건식 중에 null을 이용하는 조건식은 대표적으로 3가지가 있다.

• COALESCE(expr1,expr2,[expr3…….)
  
  • 인수로 들어간 표현식(expr) 중에 null이 아닌 첫번째 표현식 값을 반환한다.
    
  • 값이 반환되면 나머지 표현식은 평가되지 않는다.
    
  • 입력 표현식에는 모든 데이터 타입이 입력 가능하다.
    
    
• NULLIF(expr1, expr2)
  
  • expr1과 expr2가 값이 같으면, null을 반환하고 그렇지 않으면 expr1을 반환한다.
    
  • expr1과 expr2는 같은 상위 데이터 타입에 속해야하며, 비교 가능해야 한다.
    
    
• IFNULL(expr1, expr2)
  
  • expr1이 null이면 expr2를 반환한다. 그렇지 않으면 expr1을 반환한다.
    
  • expr1과 expr2에는 모든 데이터 타입이 입력될 수 있다.
    
  • COALESCE(expr1,expr2)와 같은 표현이다.
    
    

---

2. 예시

예시 데이터셋은 아래와 같다.

-- 예시 데이터 셋 제작 코드 
WITH ex_table AS 
( 
    SELECT 30 AS col1, 37 AS col2 , 55 AS col3
  UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
  UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
  UNION ALL SELECT 3 AS col1, 3 AS col2 , null AS col3 
)

SELECT 
  co11
  ,co12
  ,col3
FROM ex_table
;

위 예시 데이터에 null과 관련된 조건식( coalesce,nullif,ifnull)을 넣고 결과를 비교해보자
출력 결과는 테이블 맨 우측에 위치한 result 칼럼에서 확인할 수 있다.

• COALESCE 구문 예시
  

/*
coalesce 예시
*/
WITH ex_table AS ( 
SELECT 30 AS col1, 37 AS col2 , 55 AS col3
UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
UNION ALL SELECT 3 AS col1, 3 AS col2 , null AS col3
)

SELECT
  *,
  coalesce(col1,col2,col3) as result_coalesce
FROM ex_table;

• NULLIF 구문 예시
  
  

/*
nullif 예시
*/
WITH ex_table AS ( 
SELECT 30 AS col1, 37 AS col2 , 55 AS col3
UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
UNION ALL SELECT 3 AS col1, 2 AS col2 , null AS col3 
)

SELECT
  col1,
  col2,
 nullif(col1,col2) as result_nullif -- nullif는 expr 총 2개만 비교 가능
FROM ex_table

• IFNULL 구문 예시
  
  

/*
ifnull 예시
*/
WITH ex_table AS ( 
SELECT 30 AS col1, 37 AS col2 , 55 AS col3
UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
UNION ALL SELECT 3 AS col1, 3 AS col2 , null AS col3
 )

SELECT
  col1,
  col2,
 ifnull(col1,col2) as result_ifnull
FROM ex_table

---

3. 정리

이처럼 null과 관련된 조건식 3가지를 알아보았다.

나는 특히 실무에서 IFNULL 구문을 주로 활용한다.
(거의 IFNULL만 사용한다)

데이터 추출시 LEFT JOIN을 이용하여 테이블끼리 매칭하고 집계하는 경우가 많은데,
이 때 매칭이 안되는 집계 결과(null)를 0으로 바꿔줄때 많이 사용한다.

ex) IFNULL(t2.won,0) AS won

위의 케이스처럼 IFNULL을 LEFT JOIN과 같이 활용하면 매우 유용하니 한번 활용해봐도 좋을 것 같다.

---