[DB] NULL vs. Zero(0)

수치 데이터를 다룰 때 NULL과 0이 나오는데 NULL과 0은 의미가 다르며 다룰 때 주의해야 합니다. 특히 수학적 통계적으로는 이 차이는 매우 중요합니다. NULL과 0의 차이를 공부해서 기억하는 것이 좋습니다.

1. DB(SQL을 사용하는 것들)에서 NULL과 0은 조건문, 비교문 등 산술연산 등에서 어떤 차이가 있는가?
2. 나누기 계산을 할 때 분모가 0인 경우 DB는 에러를 일으킵니다. 이 에러가 나지 않도록 하는 방법에는 뭐가 있는가? 그리고 null propagation은 무엇인가?
3. BigQuery 에서 NULL, Inf, 0은 무엇인가?

산술식?

데이터 표시 방식을 수정하거나 계산을 수행할 때 가정 시나리오를 조사할 필요가 있다. 산술식은 열 이름, 상수 값 및 산술연산자를 포함할 수 있다.

산술 연산자

SQL에서 사용할 수 있는 산술 연산자는 더하기(+), 빼기(-), 곱하기(*), 나누기(/)이다. FROM절을 제외한 SQL의 모든 절에서 산술 연산자를 사용할 수 있다. DATE 및 TIMESTAMP와 같은 date 타입은 더하기, 빼기 연산자만 사용할 수 있다.

나누기 연산자

다음과 같은 SQL에서 나누기 연산자의 예외사항을 다룬다.

• 숫자/0 = error
• 0/숫자 = 0
• 숫자/null = null
• 0/null = null

0 vs. NULL

	0	NULL
형식	0은 숫자이며 컴퓨터에서 8비트 이진 표기법으로 표시된다.	NULL은 아무것도 없거나 빈 필드를 의미한다.
포인터	첫 번째 초기 값을 나타낸다.	값이 없음을 나타낸다.
DBMS	0을 추가해도 합계값에 영향을 주지 않는다. (a+0=a)	NULL을 추가하면 합계 값에 영향을 미친다. (a+NULL=NULL)
문자열 형식 표현	길이가 0인 문자열에는 특별히 공백 값이 지정되어 있다.	NULL은 문자열 형식의 값이 없다.
조건문	조건 지향적이며 조건이 충족되면 다음 명령문을 실행한다.	어떠한 조건도 지향하지 않고 NULL 값으로만 리턴한다.

• count(컬럼명) : NULL 값을 제외하고 count
• count(*) : NULL 값을 포함하여 count

NULL propagation?

null propagation은 null 값을 확인하지 않고도 변수를 안전하게 탐색하도록 도와준다. 코드를 쉽게 읽을 수 있고, 오류가 덜 발생한다.

NULL 처리 함수

• oracle : NVL(expr1, expr2), sql : ISNULL(expr1, expr2), mysql : IFNULL(expr1, expr2)

  • NULL 값인 경우 지정한 값으로 대체해주는 함수이다.
  • expr1값과 expr2값이 달라도 자동으로 변환해서 적용해준다.
  • 사용할 수 있는 데이터 타입 : 날짜, 문자, 숫자

    # 예시
    NVL(column, 0)  # column 값이 NULL일 경우 0으로 치환
    NVL(column, '')  # column 값이 NULL일 경우 ''으로 치환
    NVL(column, SYSDATE)  # column 값이 NULL일 경우 현재 날짜로 치환

• NVL2(expr1, expr2, expr3)

  • expr1 값이 NULL이 아닐 경우, expr2 값을 반환한다.
  • expr1 값이 NULL일 경우, expr3 값을 반환한다.

    SELECT MGR,
           COMM,
         NVL2(MGR, 'Y', 'N')  # 매니저가 있으면 'Y', 없으면 'N'
         NVL2(COMM, 'Y', 'N') # 
    FROM EMP

• NULLIF(expr1, expr2)

  • expr1 값과 expr2 값이 동일하면 NULL, 그렇지 않으면 expr1 값을 반환한다.

    # 19살에 해당하면 NULL을 반환
    SELECT NULLIF(AAGE,19) FROM MEMBERS;

• IFNULL(expr1, expr2)

  • expr1이 NULL이면 expr2를 리턴하고, NULL이 아니면 expr1을 리턴한다.

• COALESCE(expr1, expr2, ..., exprn)

  • 인자가 정해져 있지 않고 원하는대로 넣을 수 있다.
  • 입력받은 값 중에서 NULL 값이 아닌 첫 번째 표현식을 출력한다.

NULL의 비교연산자

잘못된 사용

# NULL은 비교 연산자를 사용할 수 없다.
column = NULL
column != NULL
# 빈 문자열은 비교 연산자를 사용할 수 없다.
column = ''
# NULL을 사칙연산 한 결과는 NULL이다.
column + NULL 

올바른 사용

• IS NULL 또는 LENGTH() = 0
  • 특정 컬럼이 NULL인 모든 데이터 조회
• IS NOT NULL 또는 LENGTH() >= 1
  • 특정 컬럼이 NULL이 아닌 모든 데이터 조회

CASE문

SELECT empno
    , ename
    , sal
    , comm
    , CASE WHEN comm IS NULL THEN 'N'
            ELSE 'Y'
      END comm_yn
  FROM emp

DECODE 함수

SELECT empno
    , ename
    ,sal
    , comm
    , DECODE(comm, NULL, 'N', 'Y') comm_yn
  FROM emp

SET 조건 이용하기 (👎🏻)

SET 조건을 변경하는 것은 관리 리소스가 필요하므로 웬만하면 default 값 사용을 권장한다.
(변경 사례: readonly, timezone, 대소문자 구분 등)

• ANSI_NULLS
  • SQL Server의 NULL 값과 함께 사용될 경우 Equals(=)와 Not Equal To(<, >) 비교 연산자의 ISO 호환 동작을 지정한다.
    
• ANSI_WARNINGS
  • : 여러 오류에 대한 SQL-92 표준 동작을 지정한다.
  • ON으로 설정한 경우,
    • SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, COUNT 등의 집계 함수에 NULL 값이 있을 때 경고 메세지를 호출한다.
    • 0으로 나누기 및 산술 오버플로우 오류가 발생하면 롤백되고 오류메세지가 호출 되는 것이다.
  • OFF로 설정한 경우,
    • 경고가 발생하지 않는다.
    • 0으로 나누기 및 산술 오버플로우 오류가 발생하면 NULL 값이 반환된다.
• ARITHIGNORE
  • : 쿼리 실행 중 오버플로우 또는 0으로 나누기 오류에서 오류 메세지를 반환할지 여부를 제어한다.
  • SQL Server는 이 설정에 관계없이 오버플로우 또는 0으로 나누기 오류와 연관된 계산에서 NULL을 반환한다.
• ARITHABORT
  • : 쿼리 실행 중 오버플로우 또는 0으로 나누기 오류가 발생하면 쿼리를 종료한다.
  • 계산 열이나 인덱싱된 뷰에서 인덱스를 만들거나 변경할 때는 SET ARITHABORT 옵션을 ON으로 설정해야한다.
  • SET ARITHABORT 옵션이 OFF면 계산 열의 인덱스가 있는 테이블이나 인덱싱된 뷰에서 CREATE, UPDATE, INSERT, DELETE 문이 실패한다.

SET ANSI_WARNINGS OFF
SET ARITHIGNORE ON
SET ARITHABORT OFF

SELECT 5/5 UNION all
SELECT 5/0 UNION all
SELECT ifnull(5/0, 0);

# 1) 1
# 2) NULL
# 3) 0

BigQuery에서 inf, NaN, 0

• 수학함수 > IEEE_DIVIDE(X, Y)
  • X를 Y로 나눈다.
  • 이 함수는 float64를 반환하며, 나누기 연산자와 달리 0으로 나눌 때 오버플로우 오류를 발생하지 않는다.

X	Y	IEEE_DIVIDE(X, Y)
20.0	4.0	5.0
0.0	25.0	0.0
25.0	0.0	+inf
-25.0	0.0	-inf
0.0	0.0	NaN
0.0	NaN	NaN
NaN	0.0	NaN
+inf	+inf	NaN
-inf	-inf	NaN

---

Reference

• https://anjoliena.tistory.com/59
• https://spidyweb.tistory.com/203
• https://gent.tistory.com/281