はじめに
以下公式リファレンスには、NULL値について大きく分けてポイントが４点記載されておりますが、公式リファレンスを見ただけではNULL値の扱い方を十分に理解したとはなかなか言えないと思います。
NULL値の取り扱いを誤ると、SQLが期待通りに動作しないことがありますので、
今回はNULL値の扱い方について、いくつか検証してみましたので紹介したいと思います。
https://dev.mysql.com/doc/refman/8.0/ja/null-values.html
NULL値は「データなし」を意味します。 NULL は大文字と小文字のどちらでも記述できます。
NULL値は、数値型での 0 や文字列型での空文字列などの値とは異なります。
LOAD DATA または SELECT … INTO OUTFILE で実行されるテキストファイルのインポートまたはエクスポート操作の場合、NULL は\N 順序で表されます。
ORDER BY でソートする場合、NULL値は、昇順ソートの場合は他の値の前にソートされ、降順ソートの場合は他の値の後にソートされます。
データ準備
本検証は、MySQL 8.0.26およびMySQL8.4.4で検証して問題ありませんでしたので、MySQL8.0および8.4の仕様に差異はないと思います。
今回は検証用に１つのテーブルを作成し、テストデータを投入します。
テーブル構成としては、動物（ペット）の医療記録データとして、
「測定日」、「種類」、「漢字」、「かな」、「体重」を保持しています。
なお動物（ペット）の名前に思い入れのある飼い主は80%と言われていますが、
テストデータでは珍しい名前を設定しています。
実際に試す際は、動物（ペット）の名前に興味のある漫画や映画の登場キャラクターなど、
愛着が持てるデータに書き換えていただくと楽しさが倍増するかもしれませんね！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
mysql> #Create Table mysql> CREATE TABLE medical_record -> ( -> measurement_day DATE, -> type VARCHAR(20), -> kanji_name VARCHAR(20), -> kana_name VARCHAR(20), -> weight INT -> ); Query OK, 0 rows affected (0.08 sec) mysql> #Data Insert mysql> INSERT INTO medical_record VALUES -> ( NULL, 'DOG', '奇跡', 'だいや', 3120 ), -> ( NULL, 'DOG', '苺苺苺', 'まりなる', 2300), -> ( NULL, 'CAT', '波波波', 'さんば', 1560), -> ( NULL, 'CAT', '桜桜桜', 'あらし', 2010), -> ( '2024-11-1', 'DOG', '奇跡', 'だいや', NULL ), -> ( '2024-11-1', 'DOG', '苺苺苺', 'まりなる', NULL), -> ( '2024-11-1', 'CAT', '波波波', 'さんば', 1700), -> ( '2024-11-1', 'CAT', '桜桜桜', 'あらし', 2400), -> ( '2024-12-1', 'DOG', '奇跡', 'だいや', 3500 ), -> ( '2024-12-1', 'DOG', '苺苺苺', 'まりなる', NULL), -> ( '2024-12-1', 'CAT', '波波波', 'さんば', 1850), -> ( '2024-12-1', 'CAT', '桜桜桜', 'あらし', 2500), -> ( '2025-1-1', 'DOG', '奇跡', 'だいや', 3600 ), -> ( '2025-1-1', 'DOG', '苺苺苺', 'まりなる', 2970), -> ( '2025-1-1', 'CAT', '波波波', 'さんば', 1960), -> ( '2025-1-1', 'CAT', '桜桜桜', 'あらし', 2600) -> ; Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> #confirmation mysql> SELECT * FROM medical_record; +-----------------+------+------------+--------------+--------+ | measurement_day | type | kanji_name | kana_name | weight | +-----------------+------+------------+--------------+--------+ | NULL | DOG | 奇跡 | だいや | 3120 | | NULL | DOG | 苺苺苺 | まりなる | 2300 | | NULL | CAT | 波波波 | さんば | 1560 | | NULL | CAT | 桜桜桜 | あらし | 2010 | | 2024-11-01 | DOG | 奇跡 | だいや | NULL | | 2024-11-01 | DOG | 苺苺苺 | まりなる | NULL | | 2024-11-01 | CAT | 波波波 | さんば | 1700 | | 2024-11-01 | CAT | 桜桜桜 | あらし | 2400 | | 2024-12-01 | DOG | 奇跡 | だいや | 3500 | | 2024-12-01 | DOG | 苺苺苺 | まりなる | NULL | | 2024-12-01 | CAT | 波波波 | さんば | 1850 | | 2024-12-01 | CAT | 桜桜桜 | あらし | 2500 | | 2025-01-01 | DOG | 奇跡 | だいや | 3600 | | 2025-01-01 | DOG | 苺苺苺 | まりなる | 2970 | | 2025-01-01 | CAT | 波波波 | さんば | 1960 | | 2025-01-01 | CAT | 桜桜桜 | あらし | 2600 | +-----------------+------+------------+--------------+--------+ 16 rows in set (0.00 sec) |
NULL値を抽出する方法は？
データベースではnullとそれ以外の値で区別されていると同時に、検索対象としては特殊な扱いを受けるように設計されていますので、ケースごとに検証を行いたいと思います。
今回は、weightカラムの値がNULL値になっている行が選択されるかを見たいと思いますので、上手く抽出できると以下の３行が含まれた形で表示されます。
|
1 2 3 4 5 6 7 |
+-----------------+------+------------+--------------+--------+ | measurement_day | type | kanji_name | kana_name | weight | +-----------------+------+------------+--------------+--------+ | 2024-11-01 | DOG | 奇跡 | だいや | NULL | | 2024-11-01 | DOG | 苺苺苺 | まりなる | NULL | | 2024-12-01 | DOG | 苺苺苺 | まりなる | NULL | +-----------------+------+------------+--------------+--------+ |
ケース1：等号、等号否定、不等号で抽出できるのか？
まずはよく使う関係比較演算子の等号、等号否定、不等号を用いて、NULL値が抽出されるかを検証してみます。
なお関係比較演算子にも種類が色々ありますので、1SQL内で複数の関係比較演算子を用いて検証しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
mysql> #等号(=) と 不等号(≦,≧)の一部を用いた検証 mysql> select * from medical_record where weight = NULL -> UNION -> select * from medical_record where weight <= NULL -> UNION -> select * from medical_record where weight >= NULL -> ; Empty set (0.00 sec) mysql> #不等号(<,>)の一部を用いた検証 mysql> select * from medical_record where weight < 0 or weight > 0 -> ; +-----------------+------+------------+--------------+--------+ | measurement_day | type | kanji_name | kana_name | weight | +-----------------+------+------------+--------------+--------+ | NULL | DOG | 奇跡 | だいや | 3120 | | NULL | DOG | 苺苺苺 | まりなる | 2300 | | NULL | CAT | 波波波 | さんば | 1560 | | NULL | CAT | 桜桜桜 | あらし | 2010 | | 2024-11-01 | CAT | 波波波 | さんば | 1700 | | 2024-11-01 | CAT | 桜桜桜 | あらし | 2400 | | 2024-12-01 | DOG | 奇跡 | だいや | 3500 | | 2024-12-01 | CAT | 波波波 | さんば | 1850 | | 2024-12-01 | CAT | 桜桜桜 | あらし | 2500 | | 2025-01-01 | DOG | 奇跡 | だいや | 3600 | | 2025-01-01 | DOG | 苺苺苺 | まりなる | 2970 | | 2025-01-01 | CAT | 波波波 | さんば | 1960 | | 2025-01-01 | CAT | 桜桜桜 | あらし | 2600 | +-----------------+------+------------+--------------+--------+ 13 rows in set (0.00 sec) mysql> #不等号(<,>)の一部を用いた検証 その２ mysql> select * from medical_record where weight < NULL or weight > NULL; Empty set (0.00 sec) mysql> #等号否定(!=,<>)を用いた検証 mysql> select * from medical_record where weight != 0 -> UNION -> select * from medical_record where weight <> 0 -> ; +-----------------+------+------------+--------------+--------+ | measurement_day | type | kanji_name | kana_name | weight | +-----------------+------+------------+--------------+--------+ | NULL | DOG | 奇跡 | だいや | 3120 | | NULL | DOG | 苺苺苺 | まりなる | 2300 | | NULL | CAT | 波波波 | さんば | 1560 | | NULL | CAT | 桜桜桜 | あらし | 2010 | | 2024-11-01 | CAT | 波波波 | さんば | 1700 | | 2024-11-01 | CAT | 桜桜桜 | あらし | 2400 | | 2024-12-01 | DOG | 奇跡 | だいや | 3500 | | 2024-12-01 | CAT | 波波波 | さんば | 1850 | | 2024-12-01 | CAT | 桜桜桜 | あらし | 2500 | | 2025-01-01 | DOG | 奇跡 | だいや | 3600 | | 2025-01-01 | DOG | 苺苺苺 | まりなる | 2970 | | 2025-01-01 | CAT | 波波波 | さんば | 1960 | | 2025-01-01 | CAT | 桜桜桜 | あらし | 2600 | +-----------------+------+------------+--------------+--------+ 13 rows in set (0.01 sec) |
結果は上記の通り、等号、等号否定、不等号ではNULL値を抽出できませんでした・・・。
等号 と 不等号の検証では、NULL値を比較してもエラーにはなりませんでしたので、
SQLの文法としては問題ないが、NULL値と比較しても値は返ってこないよ・・・ということなのでしょう。
なお等号否定(!=,<>)を利用した際に、NULL値が除外されてしまうという点も注意が必要ですね！
ケース２： 論理演算子(NOT演算子)ではどうなのか？
論理演算子の中には、NOT演算子（NOT,!）が存在します。
果たしてNULL値は出力されるのか実験してみましたが、以下のようにNULL値には確固たる信念があるようで、抽出することができません・・・。
NOT演算子（NOT,!）を利用する場合でも、NULL値が除外されてしまうという点は、注意が必要ですね！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
mysql> select * from medical_record where not weight = 0 -> UNION -> select * from medical_record where ! weight = 0 -> UNION -> select * from medical_record where not weight = NULL -> UNION -> select * from medical_record where not weight != NULL -> ; +-----------------+------+------------+--------------+--------+ | measurement_day | type | kanji_name | kana_name | weight | +-----------------+------+------------+--------------+--------+ | NULL | DOG | 奇跡 | だいや | 3120 | | NULL | DOG | 苺苺苺 | まりなる | 2300 | | NULL | CAT | 波波波 | さんば | 1560 | | NULL | CAT | 桜桜桜 | あらし | 2010 | | 2024-11-01 | CAT | 波波波 | さんば | 1700 | | 2024-11-01 | CAT | 桜桜桜 | あらし | 2400 | | 2024-12-01 | DOG | 奇跡 | だいや | 3500 | | 2024-12-01 | CAT | 波波波 | さんば | 1850 | | 2024-12-01 | CAT | 桜桜桜 | あらし | 2500 | | 2025-01-01 | DOG | 奇跡 | だいや | 3600 | | 2025-01-01 | DOG | 苺苺苺 | まりなる | 2970 | | 2025-01-01 | CAT | 波波波 | さんば | 1960 | | 2025-01-01 | CAT | 桜桜桜 | あらし | 2600 | +-----------------+------+------------+--------------+--------+ 13 rows in set, 1 warning (0.01 sec) |
ケース３： NULL値を扱うには、やはりあの演算子か・・・？
NULL値を扱うにはやはり「IS NULL」演算子を利用するというのが一般的だと思います。
以下のように実行することで、今度こそNULL値が取得できていることがわかります。
|
1 2 3 4 5 6 7 8 9 |
mysql> select * from medical_record where weight IS NULL; +-----------------+------+------------+--------------+--------+ | measurement_day | type | kanji_name | kana_name | weight | +-----------------+------+------------+--------------+--------+ | 2024-11-01 | DOG | 奇跡 | だいや | NULL | | 2024-11-01 | DOG | 苺苺苺 | まりなる | NULL | | 2024-12-01 | DOG | 苺苺苺 | まりなる | NULL | +-----------------+------+------------+--------------+--------+ 3 rows in set (0.00 sec) |
ちなみに公式リファレンスには以下のように記載されています。
・NULL を調べるために、次に示すように IS NULL および IS NOT NULL 演算子を使用します。
・=、<、または <> などの算術比較演算子を使用して NULL をテストすることはできません。
・・・では、
もし「NULL値を扱うには「IS NULL」演算子のみである？」というクイズを提示された場合、「YES/NO」のどちらで答えますか？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> select * from medical_record where weight <=> NULL; +-----------------+------+------------+--------------+--------+ | measurement_day | type | kanji_name | kana_name | weight | +-----------------+------+------------+--------------+--------+ | 2024-11-01 | DOG | 奇跡 | だいや | NULL | | 2024-11-01 | DOG | 苺苺苺 | まりなる | NULL | | 2024-12-01 | DOG | 苺苺苺 | まりなる | NULL | +-----------------+------+------------+--------------+--------+ 3 rows in set (0.01 sec) mysql> select * from medical_record where weight <=> 2300; +-----------------+------+------------+--------------+--------+ | measurement_day | type | kanji_name | kana_name | weight | +-----------------+------+------------+--------------+--------+ | NULL | DOG | 苺苺苺 | まりなる | 2300 | +-----------------+------+------------+--------------+--------+ 1 row in set (0.00 sec) |
実は「<=>」演算子を利用すると、NULL値の比較と等号(=)を用いた比較ができます！
「<=>」演算子は見た目が宇宙船に似ている事から、
「Spaceship Operator」や、「UFO演算子」と呼ばれることもありますが・・・
MySQLの公式のドキュメントでは、「NULL-safe equal」、「NULL-安全等価」演算子と呼ばれています。
さらに細かく記載すると・・・
以下のようにIS UNKNOWNでもNULL値を取得することはできます。
|
1 2 3 4 5 6 7 8 9 |
mysql> select * from medical_record where weight IS UNKNOWN; +-----------------+------+------------+--------------+--------+ | measurement_day | type | kanji_name | kana_name | weight | +-----------------+------+------------+--------------+--------+ | 2024-11-01 | DOG | 奇跡 | だいや | NULL | | 2024-11-01 | DOG | 苺苺苺 | まりなる | NULL | | 2024-12-01 | DOG | 苺苺苺 | まりなる | NULL | +-----------------+------+------------+--------------+--------+ 3 rows in set (0.00 sec) |
それならば「<=>」演算子と「UNKNOWN」の組み合わせでも比較できるのか？
細かい部分ですが気になったので、実験してみたところ以下のようにエラーになりました。。。
|
1 2 |
mysql> select * from medical_record where weight <=> UNKNOWN; ERROR 1054 (42S22): Unknown column 'UNKNOWN' in 'where clause' |
ケース４：NULL値を含めて値を取り出したい場合のテンプレ
以下はweightが「2300以下」もしくはNULL値を抽出するSQLです。
注意点としては、やはりNULL値を考慮することが必要になりますので、「<=>」もしくは「IS NULL」演算子を用いてNULL値を取得する必要があるということです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
mysql> select * from medical_record where weight <=> NULL or weight <= 2300; +-----------------+------+------------+--------------+--------+ | measurement_day | type | kanji_name | kana_name | weight | +-----------------+------+------------+--------------+--------+ | NULL | DOG | 苺苺苺 | まりなる | 2300 | | NULL | CAT | 波波波 | さんば | 1560 | | NULL | CAT | 桜桜桜 | あらし | 2010 | | 2024-11-01 | DOG | 奇跡 | だいや | NULL | | 2024-11-01 | DOG | 苺苺苺 | まりなる | NULL | | 2024-11-01 | CAT | 波波波 | さんば | 1700 | | 2024-12-01 | DOG | 苺苺苺 | まりなる | NULL | | 2024-12-01 | CAT | 波波波 | さんば | 1850 | | 2025-01-01 | CAT | 波波波 | さんば | 1960 | +-----------------+------+------------+--------------+--------+ 9 rows in set (0.00 sec) mysql> select * from medical_record where weight is NULL or weight <= 2300; +-----------------+------+------------+--------------+--------+ | measurement_day | type | kanji_name | kana_name | weight | +-----------------+------+------------+--------------+--------+ | NULL | DOG | 苺苺苺 | まりなる | 2300 | | NULL | CAT | 波波波 | さんば | 1560 | | NULL | CAT | 桜桜桜 | あらし | 2010 | | 2024-11-01 | DOG | 奇跡 | だいや | NULL | | 2024-11-01 | DOG | 苺苺苺 | まりなる | NULL | | 2024-11-01 | CAT | 波波波 | さんば | 1700 | | 2024-12-01 | DOG | 苺苺苺 | まりなる | NULL | | 2024-12-01 | CAT | 波波波 | さんば | 1850 | | 2025-01-01 | CAT | 波波波 | さんば | 1960 | +-----------------+------+------------+--------------+--------+ 9 rows in set (0.00 sec) |
このようにNULL値の取り扱い方について知っておく必要はありますが・・・
一方で、設計時にNULLを使用するシチュエーションはなるべく避け、
その上でテーブル定義作成時に値が必須のカラムにはNOT NULL制約を付けたり、
（NULL値以外の）適切なデフォルト値を設定することも対策として有効です。
集合関数とNULL値の関係はどうなのか？
次に集合関数とNULL値の関係性についてみていきます。
具体的には集計対象にNULL値が含まれる場合、どのような結果になるのかを検証していきたいと思います。
ケース1：COUNT関数編
まずは、medical_recordテーブルのレコード数を数えるために、
SELECT句でcount(*)を指定します。以下の通り全てのレコードがカウントできています。
|
1 2 3 4 5 6 7 |
mysql> select count(*) from medical_record; +----------+ | count(*) | +----------+ | 16 | +----------+ 1 row in set (0.01 sec) |
次にNULL値が含まれるweightカラムをcount関数の引数に指定して検証します。
結果はweightがNULL値となっている3レコードが除外され、カウントしているように見えます。
|
1 2 3 4 5 6 7 |
mysql> select count(weight) from medical_record; +---------------+ | count(weight) | +---------------+ | 13 | +---------------+ 1 row in set (0.00 sec) |
本当にNULL値をカウントしていないのか確認するために、
GROUP BY句でweightを指定して結果を見てみます。
・・・すると以下の通り、NULL値のレコードは0件（=NULL値はカウントされていない）という事実がわかります。
もし全レコード数を見たい場合は、SELECT句でcount(*)を指定するか・・・
count関数の引数にはPKカラムを指定する方が無難です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> mysql> select weight,count(weight) from medical_record group by weight order by weight; +--------+---------------+ | weight | count(weight) | +--------+---------------+ | NULL | 0 | | 1560 | 1 | | 1700 | 1 | | 1850 | 1 | | 1960 | 1 | | 2010 | 1 | | 2300 | 1 | | 2400 | 1 | | 2500 | 1 | | 2600 | 1 | | 2970 | 1 | | 3120 | 1 | | 3500 | 1 | | 3600 | 1 | +--------+---------------+ 14 rows in set (0.00 sec) |
そして抽出対象となるデータが0件の場合や該当データがNULL値であった場合ですが、
COUNT関数の場合はどちらも0が返ってきます！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select count(weight) from medical_record where weight = 9999 ; +---------------+ | count(weight) | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec) mysql> select count(weight) from medical_record where weight is null ; +---------------+ | count(weight) | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec) |
ケース２：MAX関数編
次にMAX関数の例です。
以下の結果だけ見るとMAX関数の場合は最大値が取得できるので、特にNULLの考慮は不要でしょ！
・・・と思うかもしれません。
|
1 2 3 4 5 6 7 |
mysql> select max(weight) from medical_record; +-------------+ | max(weight) | +-------------+ | 3600 | +-------------+ 1 row in set (0.00 sec) |
例えば抽出対象となるデータが0件の場合や該当データがNULL値であった場合、
結果はどちらもNULL値が返ってきます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select max(weight) from medical_record where weight = 9999 ; +-------------+ | max(weight) | +-------------+ | NULL | +-------------+ 1 row in set (0.00 sec) mysql> select max(weight) from medical_record where weight is null ; +-------------+ | max(weight) | +-------------+ | NULL | +-------------+ 1 row in set (0.00 sec) |
アプリケーションとDBを組み合わせてシステムを構築する都合上、
「DBからの問い合わせ結果がNULL値では、アプリケーション側に不都合が・・・」
という場合には、NULL値を0に変換するような処理が必要となります。
そういった場合は以下のように、
IFNULL関数やCOALESCE関数を利用してNULL値を0に変換することをお勧めいたします。
この時、例えばweightのデータが10件あった場合、
10件の中からNULL値を全て0に置き換える必要はないと思いますので・・・
「max(ifnull(weight,0))」と書くよりも、
以下のように「ifnull(max(weight),0) 」という形で書いた方が良いと考えます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select ifnull(max(weight),0) from medical_record where weight is null ; +-----------------------+ | ifnull(max(weight),0) | +-----------------------+ | 0 | +-----------------------+ 1 row in set (0.00 sec) mysql> select COALESCE(max(weight),0) from medical_record where weight is null ; +-------------------------+ | COALESCE(max(weight),0) | +-------------------------+ | 0 | +-------------------------+ 1 row in set (0.00 sec) |
ケース３：MIN関数編
MIN関数の場合はどうかというと・・・
NULL値を除外した最小値が出力されます！
|
1 2 3 4 5 6 7 |
mysql> select min(weight) from medical_record; +-------------+ | min(weight) | +-------------+ | 1560 | +-------------+ 1 row in set (0.00 sec) |
・・・ですので、
NULL値を除外した最小値を求めたい場合は、
以下のようにwhere weight is not nullと記述する必要はありません。
|
1 2 3 4 5 6 7 |
mysql> select min(weight) from medical_record where weight is not null; +-------------+ | min(weight) | +-------------+ | 1560 | +-------------+ 1 row in set (0.01 sec) |
なおNULL値があった場合は0として扱いたいという場合は、
MIN関数 + IFNULL関数やCOALESCE関数を利用して以下の順番で記載する必要があります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
ysql> select min(ifnull(weight,0)) from medical_record ; +-----------------------+ | min(ifnull(weight,0)) | +-----------------------+ | 0 | +-----------------------+ 1 row in set (0.00 sec) mysql> select min(COALESCE(weight,0)) from medical_record ; +-------------------------+ | min(COALESCE(weight,0)) | +-------------------------+ | 0 | +-------------------------+ 1 row in set (0.00 sec) |
MIN関数 + IFNULL関数やCOALESCE関数の順序を以下のようにしてしまうと、
NULL値を除いた最小値が出力されます・・・。
これはIFNULL関数やCOALESCE関数を使っている意味がないので注意が必要ですね！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select ifnull(min(weight),0) from medical_record ; +-----------------------+ | ifnull(min(weight),0) | +-----------------------+ | 1560 | +-----------------------+ 1 row in set (0.00 sec) mysql> select COALESCE(min(weight),0) from medical_record ; +-------------------------+ | COALESCE(min(weight),0) | +-------------------------+ | 1560 | +-------------------------+ 1 row in set (0.00 sec) |
ケース４：SUM関数編
まずSUM関数を利用する前の基礎知識として、
四則演算を行う際にNULL値を含めて計算すると以下のようにNULL値になります。
ちなみに文字列結合においてもNULL値を結合するとNULL値になりますのでこちらも要注意です！
|
1 2 3 4 5 6 7 |
mysql> select 1 + NULL; +----------+ | 1 + NULL | +----------+ | NULL | +----------+ 1 row in set (0.00 sec) |
実際にSUM関数を利用するとweightの合計値が表示されているように見えますので、
SUM関数ではNULL値を除外して計算しているということが分かります！
|
1 2 3 4 5 6 7 |
mysql> select sum(weight) from medical_record; +-------------+ | sum(weight) | +-------------+ | 32070 | +-------------+ 1 row in set (0.00 sec) |
しかし、MAX関数と同様に抽出対象となるデータが0件の場合や
該当データがNULL値であった場合、結果はどちらもNULL値が返ってきます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select sum(weight) from medical_record where weight = 9999 ; +-------------+ | sum(weight) | +-------------+ | NULL | +-------------+ 1 row in set (0.00 sec) mysql> select sum(weight) from medical_record where weight is null ; +-------------+ | sum(weight) | +-------------+ | NULL | +-------------+ 1 row in set (0.00 sec) |
NULL値を0に変換したい場合は
IFNULL関数やCOALESCE関数を利用して変換することをお勧めいたします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> select ifnull(sum(weight),0) from medical_record where weight is null ; +-----------------------+ | ifnull(sum(weight),0) | +-----------------------+ | 0 | +-----------------------+ 1 row in set (0.00 sec) mysql> select COALESCE(sum(weight),0) from medical_record where weight is null ; +-------------------------+ | COALESCE(sum(weight),0) | +-------------------------+ | 0 | +-------------------------+ |
ケース５：AVG関数編
最後にAVG関数です。
こちらもSUM関数と同様にNULL値を除外して計算しています！
|
1 2 3 4 5 6 7 |
mysql> select avg(weight) from medical_record; +-------------+ | avg(weight) | +-------------+ | 2466.9231 | +-------------+ 1 row in set (0.00 sec) |
AVG関数でも抽出対象となるデータが0件の場合や
該当データがNULL値であった場合、結果はどちらもNULL値が返ってきます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select avg(weight) from medical_record where weight = 9999 ; +-------------+ | avg(weight) | +-------------+ | NULL | +-------------+ 1 row in set (0.00 sec) mysql> select avg(weight) from medical_record where weight is null ; +-------------+ | avg(weight) | +-------------+ | NULL | +-------------+ 1 row in set (0.00 sec) |
もはやテンプレート化していますが、
他の関数と同様にNULL値を0に変換したい場合はIFNULL関数やCOALESCE関数を利用しましょう！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> select ifnull(avg(weight),0) from medical_record where weight is null ; +-----------------------+ | ifnull(avg(weight),0) | +-----------------------+ | 0.0000 | +-----------------------+ 1 row in set (0.00 sec) mysql> select COALESCE(avg(weight),0) from medical_record where weight is null ; +-------------------------+ | COALESCE(avg(weight),0) | +-------------------------+ | 0.0000 | +-------------------------+ 1 row in set (0.00 sec) |
集合関数を検証した結果のまとめ
今回５つの集合関数を検証した結果をまとめると以下のようになります。
・集合関数はNULL値を除外して処理を行っている模様。
・抽出対象となるデータが0件の場合や該当データがNULL値であった場合・・・
→ COUNT関数は 0 を返す。
→ COUNT関数以外は NULL値 を返す。
ちなみに公式リファレンスには、以下のような記載があります。
今回５つの集合関数の説明欄にはNULL値を含んで処理を行うような記載はなかったので、
NULL値は除外して処理を行っているものとみて良いと思います。
特に明記されていないかぎり、集計関数は NULL値を無視します。
こういう内容は、DBの試験問題に出そうですね！
是非覚えておきましょう！！
まとめ
さて皆さんは「水を入れずにご飯を炊いたこと」はありますでしょうか？
炊きあがった時に目にするものは「ご飯」ではなく、「アツアツの米」ができています！
勿論そのままでは美味しくありませんので、お水とお酒を足して再度炊飯器で炊きなおしたり、
ピラフやパエリア、リゾットなど別の料理に作り直す必要があります。
NULL値も取り扱いを間違え、DB操作のトラブルを起こしてリカバリーが発生しないよう、
本ブログの内容が参考になれば幸いです！
