0Pricing
SQL Academy · レッスン

集約とJOINにおけるNULL

COUNT、SUM、JOINでNULLがどう扱われるかを学びます。

「集約とJOINにおけるNULL」はCoddyKit上の無料SQL Academyレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

NULLによる計算結果の変化

集約関数と結合は、どちらもNULLを特別に扱います。ルールを知らないと、合計や件数が気づかないうちに誤ってしまうことがあります。

このレッスンでは、COUNT、SUM、AVG、GROUP BY、外部結合が欠損値とどのように関わるかを説明します。

SELECT amount FROM payments;
-- amount
-- -------
--    100
--   NULL   <- missing
--    200

集約関数はNULLを無視する

ほとんどの集約関数 — SUM、AVG、MIN、MAX — はNULL値を単にスキップします。データがある行だけを集約するということです。

そのため、amountがNULLでもSUMが壊れることはありません。合計から除外されるだけです。

-- Using amounts 100, NULL, 200
SELECT
  SUM(amount) AS total,  -- 300 (NULL skipped)
  MIN(amount) AS lo,     -- 100
  MAX(amount) AS hi      -- 200
FROM payments;

AVGもNULLを無視する

AVGは、NULLでない値の合計をNULLでない値の件数で割ります。NULLはどちらにも含まれません。

これは重要です。{100, NULL, 200}の平均は100ではなく150です — NULLはゼロとして数えられません。

-- (100 + 200) / 2 = 150, the NULL row is ignored
SELECT AVG(amount) AS avg_amount FROM payments;

-- If you WANT NULLs counted as 0, COALESCE first:
SELECT AVG(COALESCE(amount, 0)) AS avg_with_zeros FROM payments; -- 100

COUNT(*)とCOUNT(column)の違い

この違いでつまずく人は少なくありません。

  • COUNT(*)は、NULLを含む行を数えます。
  • COUNT(column)は、その列がNULLでない行だけを数えます。
-- 3 rows total, but only 2 have a non-NULL amount
SELECT
  COUNT(*)       AS row_count,    -- 3
  COUNT(amount)  AS has_amount    -- 2
FROM payments;

COUNT(DISTINCT)とNULL

COUNT(DISTINCT col)は、NULLでない値の種類数を数えます。NULLは完全に除外されるため、種類数に加わることはありません。

「一意なXはいくつあるか」を測定するときは、この点に注意してください。

-- statuses: 'paid', NULL, 'paid', 'void'
SELECT COUNT(DISTINCT status) AS distinct_statuses
FROM payments;
-- 2  (paid, void) -- NULL not counted

空集合に対する集約

集約関数を0行に対して実行した場合、結果は関数によって異なります。

  • COUNT(...)は0を返します。
  • SUM、AVG、MIN、MAXはNULLを返します。

必要に応じて、COALESCEを使ってNULLの合計を0に変換してください。

-- No rows match -> SUM is NULL, not 0
SELECT COALESCE(SUM(amount), 0) AS total
FROM payments
WHERE status = 'refunded';  -- no such rows

GROUP BYはNULLをまとめてグループ化する

他の場所ではNULL = NULLの結果は不明になりますが、GROUP BYはすべてのNULLを1つのグループにまとめます。

そのため、NULLのカテゴリは結果内で独自のグループになり、欠損データの行をまとめて集計できます。

SELECT category, COUNT(*) AS n
FROM products
GROUP BY category;

-- category | n
-- ---------+---
-- books    | 5
-- toys     | 3
-- NULL     | 2   <- all NULL categories in one group

外部結合によるNULL

外部結合はNULLが発生する主な原因です。LEFT JOINは左側のすべての行を保持し、右側に一致する行がない場合は右側の列がNULLになります。

このNULLは「一致する行がない」ことを意味し、「NULL値が保存されている」ことを意味するわけではありません。

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- name  | order_id
-- ------+---------
-- Alice | 10
-- Bob   | NULL     <- Bob has no orders

LEFT JOIN後に一致件数を数える

LEFT JOIN後に実際の一致だけを数えるには、COUNT(*)ではなく、右側のテーブルにあるNULLにならない列を数えてください。

COUNT(o.id)は、一致しない左側の行によって生成されたNULLの行を無視するため、実際の注文数を得られます。

SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

-- Bob shows 0, not 1×NULL

一致しない行を除外する

注意が必要な落とし穴があります。LEFT JOINの後、右側のテーブルに対する条件をWHEREに置くと、NULL = valueの結果が不明になって除外されるため、LEFT JOINが内部結合に変わります。

一致しない行を保持したい場合は、条件をON句に置くか、NULLを明示的に確認してください。

-- Accidentally drops Bob (his o.status is NULL)
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'open';

-- Keep unmatched rows: move the test into ON
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'open';

覚えておきたいルール

集約と結合を使うすべてのクエリで、次のルールを覚えておいてください。

  • 集約関数はNULLを無視します（COUNT(*)を除く）。
  • colにNULLがある場合、COUNT(col) < COUNT(*)となります。
  • 空集合に対するSUM/AVGはNULLになるため、COALESCEで囲みます。
  • LEFT JOINでは一致しない行にNULLが生成されるため、右側のキーを数えます。
  • 右側テーブルのフィルターはWHEREではなくONに置きます。
SELECT c.name,
  COALESCE(SUM(o.amount), 0) AS spent,
  COUNT(o.id)                AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

クイックチェック

列amountに、3行にわたって100、NULL、200が入っています。COUNT(*)とCOUNT(amount)はそれぞれ何を返しますか。

まとめ

集約や結合でNULLがどのように扱われるかを学びました。集約関数はNULLをスキップし、COUNT(*)は行を数える一方でCOUNT(col)はNULLでない値を数え、空集合に対する合計はNULLになり、GROUP BYはNULLを1つのグループにまとめます。

また、外部結合では一致しない行にNULLが生成されることと、右側テーブルのフィルターをONに置く理由も確認しました。これでNULLの扱いコースは完了です。これで欠損データを自信を持って扱えるようになりました。

-- A NULL-safe summary query
SELECT c.name,
  COUNT(o.id)                AS orders,
  COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY total_spent DESC;

よくある質問

「集約とJOINにおけるNULL」レッスンは無料ですか？

はい。「集約とJOINにおけるNULL」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「集約とJOINにおけるNULL」で何を学びますか？

COUNT、SUM、JOINでNULLがどう扱われるかを学びます。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。

「集約とJOINにおけるNULL」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. NULLの本当の意味
  2. IS NULLとIS NOT NULL
  3. COALESCEとNULLIF
  4. 集約とJOINにおけるNULL
← SQL Academyに戻る