NULL(널) 붙잡을 SQL

들어가며

이번 글의 목적은 업무 중에 깨달은, 그리고 추후에 큰 피해를 막기 위해 null에 대해 삽질을 하며 정리해 보는 글이다.

그리고 SQL에서 null에 대한 고찰(?)에 대해 써보려 한다.

 

우선 DB에서 null은 무엇인가?

DB NULL은 데이터베이스에서 '값이 없음' 또는 '알 수 없음'을 의미하는 특별한 표시입니다.
숫자 0이나 빈 문자열과는 다르게, "값이 존재하지 않음"을 의미합니다.
출처 : Google AI Overview

 

그렇다. 일종의 빈 공간을 표현하기 위한 컴퓨팅 언어라고 볼 수 있는데, Null은 수학적인 의미의 0과는 비슷할 수 있다. 하지만 DB에서 Null의 의미는 0과는 전혀 다르다.

 

 DB에서는 특정되지 않는 값이라는 모호한 '인간적(?)'인 표현으로 빈칸들(일명 Whitespace, 띄어쓰기-엔터-탭)과 0 그리고 null을 묶어서 표현하지만, 이는 모두 다른 값이다.

 

 특히 검증되지 않은 데이터를 사용할 때는 이러한 가능성을 모두 배제하지 않고, 방어로직을 충분히 구성하는 것이 중요하다.

 

 다시 null으로 돌아가자면, null은 '인간적(?)'인 표현 중에서 더 자세히 설명하면 '모른다'와 비슷하겠다. 즉, DB에서 아직 정의되지 않은 값을 null이라고 하는 것이다.

 

---

 DB 내 null의 논리 계산

select null * 0 = null

특정 숫자와 null과의  곱연산을 하면, 그 결과는 null이다.

select null || s = null

특정 문자와 null을 concat 하면, 그 결과는 null이다.

select null or true = true

 

boolean 타입 중 True or null 연산하면, 그 결과는 true이다. '알 수 없(null)'거나 '참(true)'이면, 무조건 참이다.

 

출처 : Null (SQL) - 위키백과, 우리 모두의 백과사전

 

---

나의 시행착오, null 붙잡은 쿼리

유저, 결제 ERD

tbl_user에서 userid(유저ID) 별로 특정 가게(prod_id)에 결제한 날짜(reg_date)를 적재한다.
tbl_meta은 id 별로 prod_id에 대응하는 prod_name(한글명)을 적재한다.
tbl_cashusage는 유저가 prod_id에서 결제한 날짜(reg_date)와 prod_id, amount(결제금액)을 적재한다.

 

 

테이블 구성 (Bigquery)

tbl_user, tbl_meta, tbl_cashusage 세 가지 테이블을 이용해서 몇 가지 SQL 조회를 해보려고 한다.

 

CREATE TEMP TABLE tbl_user AS (
    SELECT * FROM UNNEST([
        STRUCT('abc123' AS userid, '2024-01-02' AS reg_date, 5 AS prod_id),
        STRUCT('ac123' AS userid, '2023-01-02' AS reg_date, 3 AS prod_id),
        STRUCT('ab123' AS userid, '2023-03-02' AS reg_date, 2 AS prod_id),
        STRUCT('abc13' AS userid, '2023-07-02' AS reg_date, 4 AS prod_id),
        STRUCT('ab123' AS userid, '2023-11-02' AS reg_date, 2 AS prod_id),
        STRUCT('abc13' AS userid, '2023-05-02' AS reg_date, 1 AS prod_id),
        STRUCT('ab123' AS userid, '2023-04-02' AS reg_date, 2 AS prod_id),
        STRUCT('abc13' AS userid, '2023-04-02' AS reg_date, 2 AS prod_id),
        STRUCT('ab123' AS userid, '2023-03-02' AS reg_date, 3 AS prod_id),
        STRUCT('ab123' AS userid, '2023-03-02' AS reg_date, 3 AS prod_id),
        STRUCT('abc23' AS userid, '2022-01-02' AS reg_date, 4 AS prod_id),
        STRUCT('abc13' AS userid, '2023-01-02' AS reg_date, 5 AS prod_id),
        STRUCT('abc13' AS userid, '2023-05-02' AS reg_date, 5 AS prod_id),
        STRUCT('ac123' AS userid, '2023-08-02' AS reg_date, 3 AS prod_id)
    ])
);

CREATE TEMP TABLE tbl_meta AS (
    SELECT * FROM UNNEST([
        STRUCT(5 AS ID, '든든국밥집' AS prod_name),
        STRUCT(3 AS ID, 'GU편의점' AS prod_name),
        STRUCT(1 AS ID, '팝팝PC방' AS prod_name),
        STRUCT(4 AS ID, '버블세차장' AS prod_name),
        STRUCT(2 AS ID, '팝콘각영화관' AS prod_name)
    ])
);

CREATE TEMP TABLE tbl_cashusage AS (
    SELECT * FROM UNNEST([
        STRUCT('abc123' AS userid, '2024-01-02' AS reg_date, 5 AS prod_id, 12000 AS amount),
        STRUCT('ac123' AS userid, '2023-01-02' AS reg_date, 3 AS prod_id, 9900 AS amount),
        STRUCT('ab123' AS userid, '2023-03-02' AS reg_date, 2 AS prod_id, 7400 AS amount),
        STRUCT('abc13' AS userid, '2023-07-02' AS reg_date, 4 AS prod_id, 12500 AS amount),
        STRUCT('ab123' AS userid, '2023-11-02' AS reg_date, 2 AS prod_id, 21300 AS amount),
        STRUCT('abc13' AS userid, '2023-05-02' AS reg_date, 1 AS prod_id, 11100 AS amount),
        STRUCT('abc13' AS userid, '2023-11-02' AS reg_date, 2 AS prod_id, 6500 AS amount),
        STRUCT('ab123' AS userid, '2023-03-02' AS reg_date, 3 AS prod_id, 9700 AS amount),
        STRUCT('ab123' AS userid, '2023-11-02' AS reg_date, 2 AS prod_id, 9700 AS amount),
        STRUCT('abc23' AS userid, '2022-01-02' AS reg_date, 4 AS prod_id, 8000 AS amount),
        STRUCT('abc13' AS userid, '2023-01-02' AS reg_date, 5 AS prod_id, 9900 AS amount),
        STRUCT('ac123' AS userid, '2023-08-02' AS reg_date, 3 AS prod_id, 7200 AS amount)
    ])
);

 

 

Left Join 쿼리

위 테이블을 이용해서 아래처럼 left join을 포함한 간단한 쿼리를 날렸다.

select tbl_user.userid, tbl_user.reg_date, tbl_user.prod_id, tbl_cashusage.amount
from tbl_user
 left join tbl_cashusage
 on tbl_user.userid = tbl_cashusage.userid
 and tbl_user.prod_id = tbl_cashusage.prod_id
 and tbl_user.reg_date = tbl_cashusage.reg_date
 ;

 

쿼리 결과

 

 tbl_cashusage이 tbl_user과 매핑되지 않은 경우 amount가 null로 표기되고 있다. 위 쿼리에서 on 절에 아래 조건을 하나 더 추가해 보자. 

 

and tbl_cashusage.amount <= 99999999

 

---

Null 이용할 때, Left Join 절과 Where 절의 함정

 위 조건을 추가한다고 해서 결과가 달라지지 않을 것이라는 것을 예상할 수 있고, 역시 결과는 같았다. 하지만 문제는 다음부터 발생했다. 마지막에 추가한 조건을 where 절로 옮겨보자.

select tbl_user.userid, tbl_user.reg_date, tbl_user.prod_id, tbl_cashusage.amount
from tbl_user
 left join tbl_cashusage
 on tbl_user.userid = tbl_cashusage.userid
 and tbl_user.prod_id = tbl_cashusage.prod_id
 and tbl_user.reg_date = tbl_cashusage.reg_date
where tbl_cashusage.amount <= 99999999
 ;

null 어디 갔니?

 붙잡아야 할 null이 where 조건으로 인해 모두 사라졌다. 이를 응용한 상황 언제든지 null이라는 존재가 조건을 거는 컬럼에 존재할 경우 모두 제거될 수 있다.

 

 즉, 흔히 사용하는 '컬럼A is not null'이라는 조건은 거의 모든 조건을 사용할 때 항상 베이스로 깔려있기 때문에, 우리는 null이 필요한 상황에서 null을 붙잡을 수 없게 된다.

 

 

---

글을 마치며

 Null에 대한 이야기가 어쩌면 기본이라고 생각됐지만, 우리가 업무를 진행하는 쿼리는 생각보다 복잡하고, 길고,,, 생각할 것이 많다. 그래서 기본을 한 번 더 짚고 넘어가자는 의미도 있다.

 

 이번에 다시 DB에 대한 null 개념을 정리하면서, 앞으로 업무에 대해 꼼꼼함과 이전 업무에 대해 되돌아보는 시간을 가진 소중한 글이라고 생각한다!!