3値論理とは？
3値論理とは、真（True）、偽（False）、および未知（Unknown）の3つの値を持つ論理体系です。
従来の2値論理（真と偽）に対して、データベースでNULL値を扱う際に用いられます。
データベースでNULLを使用することで、SQLクエリを記載したエンジニアにとって想定外の挙動を引き起こすことがあります。
本記事では、3値論理による想定外の挙動がどういった場合に起きうるのかを説明します。
3値論理の動作を見るための準備
以降のSQLクエリは PostgreSQL 14.19 で動作確認しています。
以下の定義で users テーブルを作成し、4人のユーザーを登録します。
users テーブルの organization には NOT NULL 制約を付けていません。
未配属のユーザーの部署は NULL とするテーブル構成になっております。
'青空 三郎'さんは入社して間もないユーザーで未配属の為、organization が NULL に設定されています。
CREATE TABLE users (
user_id INTEGER NOT NULL,
name CHARACTER VARYING(20) NOT NULL,
organization CHARACTER VARYING(20),
CONSTRAINT users_pkey PRIMARY KEY (user_id)
);
INSERT INTO users (user_id, name, organization) VALUES
(1, '青空 太郎', '営業部'),
(2, '青空 二郎', '開発部'),
(3, '秋空 花子', '開発部'),
(4, '青空 三郎', NULL);
こちらのテーブルを用いて、3値論理の動作を確認していきます。
3値論理で気を付けるべきパターン
users テーブルを用いて、開発部以外の'青空'から始まる名前のユーザーを検索するクエリを作成しました。
SELECT * FROM users u1
WHERE u1.name LIKE '青空%'
*AND* u1.organization <> '開発部'
こちらのクエリでは、'青空 太郎' さんと '青空 三郎' さんがヒットしそうですが、検索結果として記載されるのは '青空 太郎' さんだけになります。
'青空 三郎' さんの organization カラムは NULL であり、'開発部以外'という条件にヒットしない為です。
この事から、NULL許容列に対する比較処理に関しては注意が必要である事が分かります。
NULL になっているカラムに関しては、IS NULL など NULL カラムに対しての比較をすることや NULL カラムを別の値に置き換える関数を適用する事で正しくデータの取得などが出来ます。
先ほどのクエリを直すのであれば以下のような書き方が出来ます。
SELECT * FROM users u1
WHERE u1.name LIKE '青空%'
AND ( u1.organization <> '開発部' *OR u1.organization IS NULL* )
もしくは、
SELECT * FROM users u1
WHERE u1.name LIKE '青空%'
AND *COALESCE(u1.organization, '未所属')* <> '開発部'
これらのクエリであれば、'青空 太郎' さんと '青空 三郎' さんがヒットするようになります。
まとめ
上記、PostgreSQLを例にして記載していますが他のDBMSでも同様に3値論理による考慮は必要になります。
SQL にてNULL を扱う際は3値論理で考える必要があるため、想定外の挙動を生んでしまわないように気を付けましょう。
データベース設計やクエリの作成時には、NULL値の扱いに特に注意を払い予期しない結果を防ぐための対策を講じることが重要です。
NULL許容カラムのあるテーブルの試験時には、該当カラムがNULLの場合をテストケースに含めることなどが対策になるかと思います。
また、NULL を許容しないようにリレーション設計を見直すことも非常に効果的です。
本記事の内容がデータベースのリレーション設計、開発の一助となれば幸いです。
