クエリはエラーなしで実行され、妥当な数値が返されますが、行が欠落しており、平均が間​​違っています。 SQL NULL 値 が原因であることがよくあります。 NULL は通常の値のように動作しないため、初心者も経験豊富な開発者も同様に引っかかります。

このガイドでは、NULL がどのように動作するかを正確に説明します。 SQLite 3.50 ですべてのクエリを実行し、表示された結果はその実行から得られたものです。この動作のほとんどは標準 SQL であり、NULL ソート順序や Oracle の空文字列の処理など、データベースが異なる点に注目します。

NULLとは何ですか?

NULL は、値が欠落しているか不明であることを意味するマーカーです。これは通常の意味での値ではないため、これらはすべて異なるものです。

この違いは特に金額に関して重要です。ボーナス 0 は、その人が何も受け取らなかったことを意味します。 NULL ボーナスはまだ誰も知らないことを意味します。

サンプルテーブル

CREATE TABLE employees (
  id INT, name TEXT, phone TEXT, bonus INT, manager_id INT
);

INSERT INTO employees VALUES
 (1,'Meera','555-0101',500,NULL),
 (2,'Arjun',NULL,300,1),
 (3,'Sara','',NULL,1),
 (4,'Ravi',NULL,NULL,2);

Sara の電話文字列は空ですが、Arjun と Ravi の文字列は NULL であることに注意してください。この 2 つはまったく異なる扱いを受けます。

NULL のテスト

ほとんどの人が最初に犯す間違いは次のとおりです。

SELECT name FROM employees WHERE phone = NULL;
-- (no rows)

SELECT name FROM employees WHERE phone IS NULL;
-- Arjun
-- Ravi

SELECT name FROM employees WHERE phone IS NOT NULL;
-- Meera
-- Sara

最初のクエリは有効な SQL であり、エラーは発生しません。何かを NULL と比較することは決して真ではないため、単純に何も返しません。空の文字列は実数値であるため、Sara は IS NOT NULL の下に表示されます。ただし、Oracle では、空の文字列は NULL として扱われます。これは注目すべき例外です。

3値ロジック

ほとんどのプログラミング言語では、条件は true または false です。 SQL は 3 番目の結果 unknown を追加します。値がわからない場合、それが 5 に等しいかどうかを判断することはできません。また、2 つの未知の値が等しいかどうかは不明であるため、NULL = NULL は真ではありません。私たちのテストでは、これらはすべて NULL を返しました。

SELECT NULL = NULL, NULL <> 1, 1 + NULL, 'a' || NULL;

-- NULL | NULL | NULL | NULL

条件の結合は慎重なルールに従って行われます。実行結果は次のとおりです。1 が true、0 が false です。

WHERE から消える行

WHERE は、その条件が true の場合にのみ行を保持します。結果が不明な行は、false の行とともに削除されます。これにより、驚くべき結果が得られます。

SELECT name FROM employees WHERE phone <> '555-0101';

-- Sara

アルジュンとラヴィの電話番号は確かにその番号ではないので、アルジュンとラヴィが登場することを期待するかもしれません。しかし、彼らの携帯電話は不明であるため、結果は不明で、彼らは失踪します。それらを含めるには、明示的にそう言います: WHERE phone <> '555-0101' OR phone IS NULL。否定も役に立ちません。

SELECT name FROM employees WHERE NOT (bonus > 400);

-- Arjun

サラとラヴィにはボーナスがないため、bonus > 400 にもその逆にも登場しません。これは覚えておくべき重要な点です。条件とその否定は必ずしもすべての行をカバーするとは限りません。

COALESCE: フォールバックを提供します

COALESCE はリスト内の最初の非 NULL 値を返し、すべての主要なデータベースで機能します。

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- third

SELECT name, COALESCE(phone, 'no phone') FROM employees;
-- Meera | 555-0101
-- Arjun | no phone
-- Sara  |
-- Ravi  | no phone

空の文字列は NULL ではないため、Sara は空白のまま表示されます。 MySQL と SQLite には IFNULL、SQL Server には ISNULL、Oracle には NVL など、同様の関数が存在します。 COALESCE はポータブルな選択肢です。計算に関する前述の点はここにも当てはまります。 bonus + 100 はボーナスのない人には NULL を与えるため、不足しているボーナスをゼロとして扱うことがビジネスにとって正しい場合は COALESCE(bonus, 0) + 100 と書きます。

NULLIF: 値を NULL に変換します

NULLIF(a, b) は 2 つの値が等しい場合は NULL を返し、そうでない場合は a を返します。特に役立つのは 2 つの用途です。プレースホルダー値をクリーンアップできます。

SELECT name, NULLIF(phone, '') FROM employees WHERE id = 3;
-- Sara | NULL

SELECT name FROM employees
WHERE COALESCE(NULLIF(phone, ''), '') = '';
-- Arjun
-- Sara
-- Ravi

2 番目のクエリは、「欠落している値または空白の」値を見つけるための便利な方法です。また、多くのデータベースでエラーが発生するゼロ除算を防ぐこともできます。

SELECT total / NULLIF(quantity, 0) FROM sales;

quantity が 0 の場合、失敗ではなく NULL が返されます。私たちのテストでは、SELECT 100 / NULLIF(0, 0) は NULL を返しました。

集計では NULL をスキップします

ここで、NULL がレポートに最も大きなダメージを与えます。

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus),
       AVG(COALESCE(bonus, 0))
FROM employees;

-- 4 | 2 | 800 | 400.0 | 200.0

では、ボーナスの平均は 400 または 200 でしょうか?それは、データ内で NULL が何を意味するかによって異なります。 NULL が「ボーナスが支払われなかった」ことを意味する場合、200 が適切です。 「まだ決まっていない」という意味であれば、400が正しいかもしれません。データベースがこの決定を行うことはできないため、ビジネスに詳しい人に質問する必要があります。

関連するトラップ: 値のない SUM は 0 ではなく NULL です。ボーナスのない行のみを合計すると NULL が返されましたが、COUNT は 0 を返しました。数値を期待しているアプリケーション コードはこれでクラッシュする可能性があるため、COALESCE(SUM(bonus), 0) でラップします。

GROUP BY、ソート、DISTINCT

NULL は比較では「等しくない」ですが、次の操作ではそれらを 1 つのグループとして扱います。

SELECT bonus, COUNT(*) FROM employees GROUP BY bonus ORDER BY bonus;
-- NULL | 2
--  300 | 1
--  500 | 1

SELECT DISTINCT bonus FROM employees;
-- 500, 300, NULL

グループ化の詳細については、SQL GROUP BY および HAVING を参照してください。データベースが大きく異なるのはソート順序です。 SQLite と MySQL は NULL を最小値として扱いますが、PostgreSQL と Oracle はそれを最大値として扱います。したがって、同じ ORDER BY bonus DESC では、ある場合は最後に NULL を置き、別の場合は最初に NULL を入れることができます。 PostgreSQL、Oracle、最新の SQLite では、NULLS FIRST または NULLS LAST を使用してこれを直接記述することができます。

結合します

= を使用した結合条件は NULL と一致しないため、NULL キーを持つ行は内部結合から除外されます。

SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id;
-- Arjun, Sara, Ravi    (Meera is gone)

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- Meera | NULL
-- Arjun | Meera
-- Sara  | Meera
-- Ravi  | Arjun

Meera にはマネージャーがないため、内部結合によってその行が静かに削除されます。 LEFT JOIN はそれを保持し、欠落している側に対して独自の NULL も作成します。詳細については、SQL 結合の説明 を参照してください。

NOT IN トラップ

NOT IN リスト内の単一の NULL はすべての結果を消去します。

SELECT name FROM employees WHERE manager_id NOT IN (1);
-- Ravi

SELECT name FROM employees WHERE manager_id NOT IN (1, NULL);
-- (no rows)

2 番目の形式は manager_id <> 1 AND manager_id <> NULL を意味し、最後の部分は決して true にはなりません。これは、NULL が気づかれずに到着する可能性があるサブクエリの場合に特に危険です。 SQL サブクエリのチュートリアル で説明されているように、代わりに NOT EXISTS を使用してください。最初の結果には、その行の manager_id が NULL であるため、Meera も欠落しています。

NULLを使用した設計

• デフォルトでは NOT NULL を使用します。 値が実際に欠落している可能性がある場合にのみ NULL を許可します。
• 賢明なデフォルトを追加します。 たとえば、quantity INT NOT NULL DEFAULT 0。
• マジック値を使用しないでください。 1900-01-01 の日付または「不明」の代わりに -1 の年齢を使用すると、計算が破損します。
• UNIQUE の動作を理解してください。 ほとんどのデータベースでは UNIQUE 列に複数の NULL を許可しており、テストではエラーなしで 2 つが挿入されました。 SQL Server は例外であり、通常は許可されています。
• 主キーを NULL にすることはできないことに注意してください。主キーと外部キーを参照してください。
• NULL を使用したテスト。 NULL を含まないテスト データは、これらのバグを隠します。他のエッジケースと同様に、これらを意図的に含めます。

まとめ

NULL を「不明」として扱うと、その動作のほとんどが意味をなすようになります。つまり、不明プラス 1 は不明であり、不明に等しい不明は真であるかどうかは不明です。今週は自分のレポートを 1 つ監査してください。 COUNT(*) と COUNT(column) を使用して NULL 許容列をチェックし、NOT IN を探し、各平均で NULL が何を意味するかを決定します。

よくある質問

学び続ける