SQLのNULLは奇妙

 (jirevwe.github.io)
4 ポイント 投稿者 GN⁺ 2025-01-11 | 2件のコメント | WhatsAppで共有

  • SQLではNULL値が独特に扱われる。UNIQUE制約のある列には複数のNULL値を入れられる。

    • これは、各NULL値が他のNULLとは異なる独立した値として見なされるため。
    • SQLite、Postgres、MySQLはすべて同じように動作する。

  • 基準の確認

    select '' = '';    -- Returns 1 (true) 빈 문자열은 같음   
select 1 = 1;      -- Returns 1 (true) 숫자는 같음   
select 1 = 0;      -- Returns 0 (false) 숫자가 다름   
select null = null; -- Returns NULL (null) 응?
    • NULLは「未知の値」を表すプレースホルダーなので、2つの未知の値が同一だとは見なされないため。
    • IS演算子を使うとNULLの同一性を確認できる。たとえば、null is nullはTRUEを返す。

  • 一意性について

    • UNIQUE制約のある列にNULL値が含まれている場合、NULL値は互いに異なるものとして扱われるため、一意性制約に違反しない。
    • たとえば、('ray@mail.com', NULL)と('ray@mail.com', NULL)は別々の行として見なされる。

  • NULLがこのように扱われる理由

    • SQLiteや他のSQL互換データベースは、NULLを他のデータベースと一貫した形で扱うためにこのように実装されている。SQL標準文書ではNULLはあらゆる場所で一意であるべきだと示唆されているが、実際にはほとんどのSQLエンジンがSELECT DISTINCTやUNIONでNULLを一意なものとして扱わない。

  • 一意性を保証する方法

    • 生成列を使う

      • 常にNULLではない決定論的な値を持つ列を生成することで、この問題を緩和できる。たとえば、COALESCE(deleted_at, '1970-01-01')を使ってNULL値を置き換えられる。
      • この方法ではテーブルに追加フィールドが増えるため、領域を消費する可能性がある。

    • 部分インデックスを使う

      • deleted_atがNULLの場合にだけemailに対する部分インデックスを作成することで、一意性を保証できる。
      • 部分インデックスはテーブルを広げず、使用領域も少なく、同じレコードの組を繰り返し削除してもエラーが発生しない。

  • 更新

    • Oracleは空文字列をNULLとして扱う。

  • 結論

    • ORMを使っていると見えにくいが、SQLのNULLの独特な扱い方は混乱を招くことがある。SQL標準文書は公開されておらず、入手には費用がかかる。

関連記事

2件のコメント

 
iolothebard 2025-01-14

すべての null は奇妙ですよね。
だから、まともな SQL の null のほうが、かえって奇妙に見えるという…。
片目の国では両目の人が異常……
 
GN⁺ 2025-01-11
Hacker Newsのコメント

  • SQL の NULL は、クリーンの TRUE-FALSE-UNKNOWN 論理に基づいている。NULL を UNKNOWN と読めば、多くの演算がより直感的に理解できる

    • TRUE OR UNKNOWN = TRUE、TRUE AND UNKNOWN = UNKNOWN、UNKNOWN XOR UNKNOWN = UNKNOWN など
    • NULL は UNKNOWN を表すプレースホルダーであり、2つの NULL が等しいとは言えない
    • PostgreSQL 15 以降では、NULLS NOT DISTINCT を使って一意インデックスを作成できる

  • 1970年代に NULL の概念が導入されたとき、将来多くの混乱を招くと思っていた。45年経った今でも、なお議論されている

  • NULL の直感的な理解: 特定のテーブルセルの NULL 値は「値がない」ことを表す方法である。一意な値を求めるとき、値がない場合は考慮すべきではない

  • ORM 利用への懐疑論: ORM は便利だが、リレーショナルデータベースが実際にどう動くかを学ばない世代を生み出した。SQL NULL の挙動は基本的な関係代数と一致しており、問題なのは C スタイルの NULL である

  • Blackadder のエピソード中の会話から、NULL 比較のユーモアを思い出す

  • Oracle では NULL が空文字列と同じである点が奇妙だと思う

  • オブジェクト指向の文脈では、"null" は特定の属性に値がないことを示すのに有用である。JavaScript には null と undefined があり、undefined は値が不明で、null は値がないことを意味すると考えられる

  • NULL は重複がないという意味で奇妙ではない。NULL 同士は等しくないため、重複にはなりえない。NULL のセマンティクスが気に入らないなら、センチネル値を使うこともできる

  • SQL NULL は、存在しない値を持つレコードに対して関係論理がどう機能してほしいかを考えれば、奇妙ではない