集計、JOIN、DISTINCTにおけるNULL
グループ化、JOIN、一意性のそれぞれでNULLの挙動が異なる仕組みを学びます。
「集計、JOIN、DISTINCTにおけるNULL」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。
NULLが意外な挙動をする3つの場面
NULLはどこでも同じように動作するわけではありません。最後のレッスンでは、候補者が最も意外に感じる3つの文脈、つまり集約関数、JOIN、DISTINCT / GROUP BYを扱います。
繰り返し登場するポイントは、集約関数とフィルタリングではNULLを「無視する対象」として扱う一方、グループ化とDISTINCTではNULLを「他のNULLと等しい値」として扱うことです。この一貫しない挙動こそ、面接官が確認する点です。
これらを習得すれば、SQLの選考で最もよく出るNULLに関する質問を一通り押さえられます。
集約関数はNULLを無視する
基本ルールは、集約関数はNULLをスキップすることです。SUM、AVG、MIN、MAX、COUNT(column)はすべて、NULLの入力を0として扱うのではなく完全に無視します。
そのため、AVGが期待とは異なる値を返すことがあります。AVGは行の総数ではなく、非NULL値の合計を非NULL値の個数で割ります。
-- bonus values: 100, 200, NULL
SELECT
SUM(bonus) AS total, -- 300 (NULL ignored)
AVG(bonus) AS average, -- 150 = 300 / 2, not / 3
COUNT(bonus) AS cnt -- 2 (NULL not counted)
FROM employees;COUNT(*)とCOUNT(column)の比較
集約関数とNULLに関して最もよく聞かれる質問です。COUNT(*)はNULLを含む行を数えます。COUNT(column)は、その列が非NULLである行だけを数えます。
したがって、両者の差は、その列に含まれるNULLの個数そのものです。COUNT(DISTINCT column)はさらに重複を除去しながら、NULLも無視します。
SELECT
COUNT(*) AS rows_total, -- all rows
COUNT(bonus) AS non_null_bonus, -- excludes NULLs
COUNT(DISTINCT bonus) AS distinct_bonus, -- excludes NULLs + dups
COUNT(*) - COUNT(bonus) AS null_bonus
FROM employees;AVGとSUM/COUNT(*)：よくある落とし穴
面接官は「AVG(x)はSUM(x) / COUNT(*)と同じですか」と質問します。NULLが含まれる場合、答えはいいえです。
AVG(x)はSUM(x) / COUNT(x)と等しく、非NULL値の個数で割ります。一方、COUNT(*)で割ると、NULLを0として扱うことになり、平均値が押し下げられます。
NULLを実際に0として数えたい場合は、COALESCEを使って明示的に指定する必要があります。
-- These differ when bonus has NULLs:
SELECT
AVG(bonus) AS avg_ignoring_nulls,
SUM(bonus) * 1.0 / COUNT(*) AS avg_nulls_as_zero,
AVG(COALESCE(bonus, 0)) AS explicit_nulls_as_zero
FROM employees;すべてNULLの場合の集約処理
すべての入力がNULLの場合、または行が存在しない場合、集約関数は何を返すのでしょうか。面接官が好む、正確な区別は次のとおりです：
- すべての入力がNULLの場合（または0行の場合）の
SUM、AVG、MIN、MAXはNULLを返します。 COUNTは常に0を返し、NULLにはなりません。
そのため、レポートの合計が空欄になる場合は、SUMの対象がすべてNULLになっている可能性があります。COALESCEで囲んで0を表示してください。
-- No matching rows or all bonuses NULL:
SELECT SUM(bonus) FROM employees WHERE 1 = 0; -- NULL
SELECT COUNT(bonus) FROM employees WHERE 1 = 0; -- 0
-- Present a clean zero:
SELECT COALESCE(SUM(bonus), 0) FROM employees;JOIN条件におけるNULL
JOINのON句でも、NULL = NULLはUNKNOWNのままなので、NULLキーは決して一致しません。JOINキーが両方ともNULLの2行は、組み合わせられません。
これは、任意指定の外部キーを使ってJOINする場合に見落としやすい点です。NULL同士を一致させることが意図した動作であれば、前のレッスンで説明したNULL安全演算子（IS NOT DISTINCT FROMまたは<=>）が必要です。
-- Rows with region IS NULL on both sides do NOT match
SELECT *
FROM a JOIN b ON a.region = b.region;
-- To match NULL-to-NULL (ANSI):
SELECT *
FROM a JOIN b ON a.region IS NOT DISTINCT FROM b.region;外部結合で生成されるNULL
外部結合は、一致しない行に対してNULLを生成します。LEFT JOINの後では、対応する行が見つからなかった左側の行について、右側のすべての列がNULLになります。
これはアンチJOINパターンの基礎です。WHERE right_table.key IS NULLでフィルタリングすると、注文のない顧客など、一致する行がない行を見つけられます。
ただし注意が必要です。外部結合した列をWHEREでフィルタリングすると、意図せず内部結合に戻ってしまうことがあります。次のシーンでこの問題を扱います。
-- Find customers who have never ordered (anti-join)
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;外部結合でWHEREに指定するNULLの落とし穴
よくある落とし穴です。ordersをLEFT JOINした後、WHERE o.status = 'shipped'を追加すると、注文のない顧客が突然消え、外部結合が実質的な内部結合になってしまいます。
なぜでしょうか。一致しない行ではo.statusがNULLであり、NULL = 'shipped'はUNKNOWNになるため、WHEREによって除外されるからです。一致しない行を残すには、条件をON句に移してください。
-- Accidental inner join: drops customers with no orders
SELECT c.name, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';
-- Correct: keep unmatched customers
SELECT c.name, o.status
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id AND o.status = 'shipped';DISTINCT はすべての NULL を等しいものとして扱います
ここで、誰もが驚く一貫性のなさが出てきます。集約関数は NULL をスキップしますが、DISTINCT は NULL をちょうど 1 つ残し、すべての NULL を互いに重複しているものとして扱います。
したがって、値 100、100、NULL、NULL に対して SELECT DISTINCT bonus を実行すると、3 行、つまり 100、NULL、そしてそれだけが返ります。ほかの場所では NULL = NULL が UNKNOWN であるにもかかわらず、2 つの NULL は 1 つにまとめられます。
-- bonus: 100, 100, NULL, NULL, 200
SELECT DISTINCT bonus FROM employees;
-- Returns: 100, 200, NULL (the two NULLs become one row)GROUP BY は NULL を 1 つのグループにまとめます
GROUP BY は DISTINCT と同じルールに従い、NULL のキーをすべて単一のグループにまとめます。これは、NULL 同士が決して等しくならない比較ロジックとは正反対です。
したがって、NULL を許容する列でグループ化すると、NULL をキーとするすべてのレコードを表す 1 行が得られます。レポートでは通常、この動作が求められます。この「グループ化と比較の違い」に触れると、理解の深さを示せます。
-- All employees with NULL department form ONE group
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;
-- A single row where department is NULL totals all of them面接で押さえるポイント
面接官に好印象を与える、全体をまとめた説明は次のとおりです。
- 集約関数は NULL を無視します。AVG は COUNT(*) ではなく COUNT(column) で割ります。
- COUNT(*) は行数を数えますが、COUNT(col) と COUNT(DISTINCT col) は NULL をスキップします。
- 行がない場合、SUM/AVG/MIN/MAX は NULL を返し、COUNT は 0 を返します。
- 結合では、NULL のキーは決して一致しません。外部結合した列を WHERE でフィルタリングすると、気付かないうちに内部結合になります。
- DISTINCT と GROUP BY はすべての NULL を等しいものとして扱います。これは比較ロジックとは正反対です。
一言で言えば、「NULL は集約と比較では無視されますが、重複排除では 1 つにまとめられます」です。
確認問題
グループ化と集約の違いを確認しましょう。
まとめ
面接で問われる NULL の扱いを学習しました。
- 集約関数は NULL をスキップします。AVG は NULL でない値の個数で割り、すべて NULL の SUM は NULL、COUNT は 0 になります。
COUNT(*)は NULL の行も含めますが、COUNT(col)は含めません。その差は NULL の個数と一致します。- NULL の結合キーは決して一致しません。外部結合した列を WHERE でフィルタリングすると、内部結合に変わることがあります。
- DISTINCT と GROUP BY はすべての NULL を 1 つにまとめます。これは比較ロジックとは逆の動作です。
次の定型句を覚えておきましょう。NULL は集約と比較では無視されますが、重複排除では 1 つにまとめられます。この 1 つの理解があれば、NULL に関する面接の質問の大半に答えられます。
よくある質問
「集計、JOIN、DISTINCTにおけるNULL」レッスンは無料ですか？
はい。「集計、JOIN、DISTINCTにおけるNULL」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。
「集計、JOIN、DISTINCTにおけるNULL」で何を学びますか？
グループ化、JOIN、一意性のそれぞれでNULLの挙動が異なる仕組みを学びます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。
「集計、JOIN、DISTINCTにおけるNULL」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- 三値論理とUNKNOWN
- IS NULL、IS NOT NULL、NULLセーフな等価比較
- COALESCE、NULLIF、ISNULL
- 集計、JOIN、DISTINCTにおけるNULL