学ぶ高度なNULL処理

メニューを表示するにはスワイプしてください

SQLを使用する際、特に複雑なクエリではNULL値の正しい扱いが重要。NULLは欠損または不明なデータを表し、集計や比較の結果に大きな影響を与える可能性がある。たとえば、COUNT、SUM、AVGなどの操作を行う場合、これらの関数がNULL値をどのように扱うかによって、考慮しないと予想外の結果になることがある。


              1234567
            
-- Demonstrate how COUNT, SUM, and AVG handle NULL values in the 'bonus' column
SELECT
    COUNT(bonus) AS count_bonus,    -- Counts only non-NULL values
    COUNT(*) AS count_all,          -- Counts all rows, including those with NULL
    SUM(bonus) AS sum_bonus,        -- Ignores NULLs when summing
    AVG(bonus) AS avg_bonus         -- Ignores NULLs when averaging
FROM employees;

上記の例では、COUNT(bonus)はbonusがNULLでない行のみをカウントし、COUNT(*)はNULLの有無に関係なくすべての行をカウントする。SUMとAVGもNULL値を無視し、NULLを無視し、NULLを無視し、存在するデータのみを対象とする。この動作により、欠損データが合計や平均値を歪めることはないが、実際に存在する値の数を把握しておく必要がある。

NULLを含む比較も独特であり、=、<>、<、>などの比較はすべて不明（unknown）を返し、真や偽にはならない。

ゼロやNULLとなる可能性のある値で除算する際によく問題が発生する。エラーや望ましくない結果を避けるために、NULLIF関数を利用できる。NULLIFは2つの式を比較し、等しい場合はNULLを返し、そうでなければ最初の式を返す。これはゼロによる除算を防ぐのに特に有用であり、NULLで除算すると実行時エラーの代わりにNULLが返される。


              12345678910111213
            
-- Safely calculate the average bonus per department, avoiding division by zero
SELECT
    department,
    SUM(bonus) / NULLIF(COUNT(bonus), 0) AS avg_bonus
FROM employees
GROUP BY department;

-- Combine COALESCE and NULLIF to provide a default value when division yields NULL
SELECT
    department,
    COALESCE(SUM(bonus) / NULLIF(COUNT(bonus), 0), 0) AS avg_bonus
FROM employees
GROUP BY department;

COALESCE と NULLIF を組み合わせることで、部門にボーナスが存在しない場合など、除算結果が NULL になる際に、代わりに 0 などのデフォルト値を返すことができます。この方法により、クエリの堅牢性が向上し、レポートや計算で予期しない NULL 結果を防ぐことができます。