MariaDB講座 / IS NULL・COALESCEでNULLを正しく扱う
NULLの検査にはIS NULLやIS NOT NULLを使います。= NULLや通常の否定比較では未知の結果になるため、欠損行を含めるかを明示する必要があります。COALESCEは表示用の代替値を選び、保存内容は変えません。
欠損を見分けるデータを作る
SQLファイルの実行方法でcourse_userとして学習サーバーへ接続し、SELECT VERSION(), DATABASE();とSHOW TABLES;で対象を確認します。通常例はUTF-8の.sqlファイルで実行できます。同名の実習表がある場合は定義と行を調べ、必要なデータを保存してから判断します。既存のアプリケーション表を置き換えません。
USE shopcourse; CREATE TABLE products_16_lab ( product_id INT NOT NULL AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL, category VARCHAR(30) NOT NULL, price DECIMAL(10,2) NOT NULL, stock INT NOT NULL DEFAULT 0, note VARCHAR(100) NULL DEFAULT NULL, PRIMARY KEY (product_id), CONSTRAINT ck_p16_price CHECK (price >= 0), CONSTRAINT ck_p16_stock CHECK (stock >= 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO products_16_lab (product_id, product_name, category, price, stock, note) VALUES (1, '노트', '문구', 3500.00, 20, NULL), (2, '볼펜', '문구', 1500.00, 0, ''), (3, '머그컵', '생활', 8000.00, 12, '선물 포장'), (4, '스탠드', '생활', 25000.00, 5, NULL), (5, '파일', '문구', 1500.00, 50, ''), (6, '케이블', '전자', 8000.00, 0, '호환 확인');
SQLの文字列はUTF-8実習の条件をそろえるため、原文の韓国語サンプルを維持しています。ID1～6はノート、ペン、マグカップ、スタンドライト、ファイル、ケーブルです。分類は文具・生活用品・電子用品で、結果はIDと数値を基準に照合できます。ファイルはUTF-8、接続もutf8mb4を使ってください。
SELECT product_id, note, note IS NULL AS is_missing,
note = '' AS is_empty
FROM products_16_lab ORDER BY product_id;
ID1・4はis_missing=1、is_empty=NULL、ID2・5は0と1、ID3・6は0と0です。NULLと空文字の比較は偽0ではなく未知NULLです。真・偽・未知の三つを区別すると、否定やWHEREの結果を説明できます。
欠損と非欠損を明示して選ぶ
三つの条件を比較します。
SELECT product_id FROM products_16_lab WHERE note = NULL ORDER BY product_id; SELECT product_id FROM products_16_lab WHERE note IS NULL ORDER BY product_id; SELECT product_id FROM products_16_lab WHERE note IS NOT NULL ORDER BY product_id;
= NULLは一行も返さず、IS NULLは1・4、IS NOT NULLは2・3・5・6です。非NULLには空文字も入るため、目に見える説明が書かれているとは限りません。
SELECT product_id FROM products_16_lab WHERE note <> '선물 포장' ORDER BY product_id; SELECT product_id FROM products_16_lab WHERE note IS NULL OR note <> '선물 포장' ORDER BY product_id;
贈答包装ではないという通常の<>条件は2・5・6だけです。NULLも含めると明示したOR条件は1・2・4・5・6になります。欠損が「一致しない」の反対側に入ると決めつけず、業務上どちらに含めるかを選びます。
代替表示とNULL安全な比較
次の表示ラベルも原文のサンプルです。未入力や既定案内という意味で、列の保存値を変更しません。
SELECT product_id, COALESCE(note, '미입력') AS display_note
FROM products_16_lab ORDER BY product_id;
SELECT COALESCE(NULL, NULL, '기본 안내') AS fallback_text;
SELECT NULL = NULL AS ordinary_equal,
NULL <=> NULL AS null_safe_equal;
COALESCEは左から最初の非NULLを返し、すべてNULLならNULLです。空文字は非NULLなのでID2・5の表示は空のままです。最後の比較はordinary_equal=NULL、null_safe_equal=1になります。<=>は両方NULLでも等価性を比較できますが、単純な欠損検査ならIS NULLが明確です。
数値計算に使う在庫と説明ラベルを不用意に混ぜません。表示を整えても情報が補完されたわけではなく、未知の在庫を0とすると売り切れと誤解される場合があります。
NOT INの一覧にNULLを入れない
除外一覧を確認します。
SELECT product_id FROM products_16_lab
WHERE category NOT IN ('문구', NULL) ORDER BY product_id;
この結果は0行です。文具は除外対象に一致し、他の分類もNULLとの比較が未知になるためです。一覧からNULLを除くと3・4・6が返ります。アプリケーションや副問い合わせから作った一覧も検証します。
NOT EXISTSは表をまたぐ不在検査を表せますが、文脈なしにNOT INを文字置換する方法ではありません。比較対象列がNULLの場合の意図も別に決めます。
入力方針と再作成の確認
クライアントがNULLを空白表示するならIS NULLの結果列を追加します。DEFAULTやNOT NULLは入力時の規則なので、表示の代替とは制約の役割を分けます。
やり直すときは、選択中のDB・実際の表定義・保存行を確認し、必要なものをバックアップします。削除してよいこの記事の実習表だけに次を実行します。DROP TABLEは定義と行を削除し、通常のROLLBACKでは戻せないため、トランザクションの実験とは分けます。
DROP TABLE shopcourse.products_16_lab;
