Snowflake NULLの扱いについて全部まとめてみた！

こんにちは！oneTossiyです👓

今回の記事ではSnowflakeにおける『NULL』の扱い方についてまとめました。特に勘違いしがちなSQL NULL・JSON NULLの違いも細かくまとめています。

SQLの基本的な知識もまとめておりますので、ぜひ参考にしてみてください！

想定読者

• NULLは知っている
• Snowflake固有の挙動を体系的に知りたい人
• 「SQL NULL」「 JSON NULL」の違いがわかっていない

1. 比較演算とNULL

NULLは値が存在しない状態を表すため、通常の比較演算子では扱えません。

-- NULLと比較は常にNULLを返す
SELECT NULL = NULL; -- NULL
SELECT NULL != NULL; -- NULL
SELECT NULL > 1; -- NULL

--- True, Falseで出力させたい場合はIS NULL/IS NOT NULLを扱う
SELECT NULL IS NULL; -- True
SELECT NULL IS NOT NULL; -- False

EQUAL_NULL(Snowflake 固有)

SnowflakeにはNULLを含めた等価比較ができるEQUAL_NULL関数があります。

SELECT EQUAL_NULL(NULL, NULL);   -- → True
SELECT EQUAL_NULL(NULL, 1);      -- → False
SELECT EQUAL_NULL(1, 1);         -- → True

docs.snowflake.com

比較演算子ポイント

• 通常の比較演算子では、NULL同士を比較してもTRUEではなくNULLになる
• SnowflakeでNULLを考慮して等価比較したい場合はEQUAL_NULL が使える

2. 集計関数とNULL

集計関数によってNULLの扱いが異なります。

-- テーブルの例(sample_data)
-- col: [1, 2, NULL, 4, NULL]

WITH sample_data AS (
  SELECT 1 AS col
  UNION ALL
  SELECT 2
  UNION ALL
  SELECT NULL
  UNION ALL
  SELECT 4
  UNION ALL
  SELECT NULL
)

SELECT
  COUNT(*),     -- → 5  (NULL を含めてカウント)
  COUNT(col),   -- → 3  (NULL を除いてカウント)
  SUM(col),     -- → 7  (NULL を無視して合計)
  AVG(col),     -- → 2.333  (NULL を除いて平均 = 7/3)
  MAX(col),     -- → 4  (NULL を無視)
  MIN(col)      -- → 1  (NULL を無視)
FROM sample_data;

注意点

AVGやCOUNT関数を利用する際は、NULLが除外されることに注意しましょう。

SELECT AVG(score) FROM exams;
-- scoreにNULLが含まれると全レコードではなくNULL以外で平均が計算される

qiita.com

集計関数ポイント

• COUNT(*)はNULLを含む全行をカウント
• COUNT(col)はNULLを除いた行をカウント

3. NULL 変換関数

NULLを別の値に変換する関数が複数あります。

このNULLを引数とする関数は資格試験でも問われるので押さえておきましょう。

特に「デフォルト値を返すのか」や、「IFNULL,NULLIFの挙動」あたりはよく問われる気がします。

関数	挙動	ドキュメント
NVL(a, b)	aがNULLならbを返す	NVL | Snowflake Documentation
NVL2(a, b, c)	aがNULLでなければb、NULLならcを返す	NVL2 | Snowflake Documentation
IFNULL(a, b)	NVLと同じ	IFNULL | Snowflake Documentation
COALESCE(a, b, c…)	最初のNULLでない値を返す	COALESCE | Snowflake Documentation
NULLIF(a,b)	aとbが等しければNULLを返し、等しくなければaを返す	NULLIF | Snowflake Documentation
ZEROIFNULL(a)	引数がNULLならば0を返す	ZEROIFNULL | Snowflake Documentation

SELECT NVL(NULL, 'default');           -- → 'default'
SELECT IFNULL(NULL, 'default');       -- → 'default' (NVLと同じ)
SELECT NVL2(NULL, 'not null', 'null'); -- → 'null'
SELECT NVL2(1, 'not null', 'null');    -- → 'not null'
SELECT COALESCE(NULL, NULL, 'third');  -- → 'third'
SELECT NULLIF(1, 1);                   -- → NULL (1=1なため成立）
SELECT NULLIF(1, 2);                   -- → 1
SELECT ZEROIFNULL(NULL);               -- → 0

NULL関数ポイント

• NVLとIFNULLは同じ、NVL2もあることに注意
• COALESCEは複数の引数の中から最初のNULLでない値を返す。NVL2は１つ目の引数がNULLかによって２つ目または３つ目の値を返す。

4. ソートとNULL

Snowflakeでは、デフォルトのソート順ではNULLの順番が決まっています。

-- ASC のデフォルト：NULL は最後
SELECT * FROM t ORDER BY col ASC;

-- DESC のデフォルト：NULL は最初
SELECT * FROM t ORDER BY col DESC;

-- 明示的に指定する
SELECT * FROM t ORDER BY col ASC NULLS FIRST;   -- NULL を最初に
SELECT * FROM t ORDER BY col ASC NULLS LAST;    -- NULL を最後に
SELECT * FROM t ORDER BY col DESC NULLS FIRST;  -- NULL を最初に
SELECT * FROM t ORDER BY col DESC NULLS LAST;   -- NULL を最後に

ソートとNULLポイント

• Snowflakeの標準設定では、ASCはNULLが最後、DESCはNULLが最初
• NULLの並びは変更が可能

5. GROUP BYとNULL

GROUP BYではNULLは一つのグループとして扱います。

-- テーブル例
-- category: ['A', 'A', NULL, NULL, 'B']

SELECT category, COUNT(*)
FROM t
GROUP BY category;

-- 結果
-- A     → 2
-- NULL  → 2
-- B     → 1

GROUP BYとNULLポイント

• NULLもグループとして処理される

6. JOINとNULL

JOINキーにNULLが含まれる場合、NULL同士ではマッチしません。

しかし、先ほど説明した『EQUAL_NULL』を利用することでNULLと一致して結合することも可能です。

-- テーブル a: id [1, 2, NULL]
-- テーブル b: id [1, NULL, 3]

SELECT *
FROM a
JOIN b ON a.id = b.id;
-- NULL = NULL はマッチしないため
-- id = 1 の行のみ結合される

NULLも含めて結合したい場合

-- テーブル a: id [1, 2, NULL]
-- テーブル b: id [1, NULL, 3]

SELECT *
FROM a
JOIN b ON EQUAL_NULL(a.id, b.id);
-- NULL 同士もマッチする

JOINとNULLポイント

• デフォルトでは、NULL同士は結合されない
• EQUAL_NULLを使うことで、NULL同士の結合も可能に

7. 文字列結合とNULL

『||』演算子と『CONCAT』関数ではNULLと文字列結合してもNULLになります。

SELECT 'hello' || NULL || 'world';   -- → NULL
SELECT CONCAT('hello', NULL, 'world');  -- → NULL

もしNULLと文字列を結合する場合は、IFNULLやCOALESCEなどのNULL専用の関数を利用して文字列として置き換える処理を加えましょう。

SELECT 'hello' || COALESCE(NULL, '') || 'world';
-- → 'helloworld'

SELECT CONCAT('hello', COALESCE(NULL, ''), 'world');
-- → 'helloworld'

文字列結合とNULLポイント

• ||やCONCATなどでNULLを結合すると結果はNULLとなる
• NULLを結合したい場合はNULL系関数を利用する

8. 「SQL NULL」と「JSON NULL」

Snowflakeでは「SQL NULL」と「JSON NULL」という2種類のNULLが存在し、それぞれ別枠で考える必要があります。

この違いはSnowflake特有の型、『VARIANT』とも関わってくるのでおさえておきましょう。

SQL NULLとは

従来通りのNULLの意味で、そもそも値が存在しないことを表します。

一般的にNULLという場合はSQL NULLを指す場合が多いです。

JSON NULLとは

一方、JSON NULLとはJSONデータの中に含まれるNULLのことを指します。

SnowflakeではJSONなどの半構造データは『VARIANT』型として扱います。「SQL NULL」と「JSON NULL」は別物なため『IS NULL』による比較でも「JSON NULL」はFALSEを返します。

厳密にはJSONのリテラルは小文字の null です。 この記事では分かりやすさのために「JSON NULL」と表記しています。

SELECT PARSE_JSON('null'); -- PARSE_JSONはJSON形式の文字列をVARIANT型に変換する関数
SELECT
  PARSE_JSON('null') IS NULL;
-- 結果はFALSE、なぜならSQL NULLとは異なるから！

docs.snowflake.com

ここからさらに話が細かくなります。

PARSE_JSONの引数には上記の例だと’null’というJSONのnull値を渡していますが、NULLをそのまま引数として与えた場合どうなるでしょうか？

SELECT PARSE_JSON(NULL) IS NULL; -- PARSE_JSONはJSON形式の文字列をVARIANT型に変換する関数

この場合、PARSE_JSON() に渡しているのはJSON文字列ではなくSQL NULLです。 そのため、JSON nullではなくSQL NULLが返され、IS NULL の結果は True になります。

いったんこれまでの違いを比較すると、以下になります。

式	意味	SQL NULL or JSON NULL
NULL	SQLのNULL	SQL NULL
PARSE_JSON(’null’)	JSON文字列’null’をVARIANT化したもの	JSON NULL
PARSE_JSON(NULL)	そもそもJSONデータではなく、SQL NULLを渡している	SQL NULL
PARSE_JSON('{"a": null}')	JSONオブジェクトのaの中身がnull	aはJSON NULL

「SQL NULL」と「JSON NULL」の判定方法

SQL NULLの判定はIS NULLを利用します。

SELECT NULL IS NULL; -- True (左の式がNULLかどうか確認)
SELECT PARSE_JSON('null') IS NULL; -- JSON NULLだからFalse

JSON NULLの判定には、IS_NULL_VALUE()を使用します。

SELECT IS_NULL_VALUE(PARSE_JSON('null')); -- True
SELECT IS_NULL_VALUE(PARSE_JSON('"hello"')); -- False
-- SQL NULLに対してはNULLが返されることに注意すること
SELECT IS_NULL_VALUE(PARSE_JSON(NULL)); -- NULL

docs.snowflake.com

SQL NULLとJSON NULLまとめ

比較項目	SQL NULL	JSON null
意味	値が存在しない	JSON の null という値
例	NULL	PARSE_JSON('null')
型の扱い	SQL の NULL	VARIANT 内の JSON null
IS NULL	TRUE	FALSE
IS_NULL_VALUE()	NULL	TRUE

まとめ

今回はSnowflake NULLの扱いについてまとめました。 データベースを扱う上で、NULLの理解は避けられない道となります。 使う基盤によってはNULLの扱いも異なるので、これを機会に整理してみてはいかがでしょうか？

ここまでみていただき、ありがとうございました。

この記事を書いた人

oneTossiy (id:id:oneTossiy)

CDP構築を主に担当しています。CDP以外にも気になることがあれば投稿していきます！