はじめに
以下公式リファレンスには、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の仕様に差異はないと思います。
今回は検証用に１つのテーブルを作成し、テストデータを投入します。
テーブル構成としては、星座占いのデータとして、
「番号」、「星座（英語）」、「星座（日本語）」、「占い」を保持しています。
占いの結果を信じる/信じない、良い部分だけ信じる・・・
人それぞれ様々な意見があると思いますが、本ブログの内容は信じて頂けると幸いです！
|
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 |
mysql> #Create Table mysql> CREATE TABLE horoscopes -> ( -> auto_number INT, -> star_sign_en VARCHAR(100), -> star_sign_jp VARCHAR(100), -> fortune_telling VARCHAR(100) -> ); Query OK, 0 rows affected (0.09 sec) mysql> #Data Insert mysql> INSERT INTO horoscopes VALUES -> ( 1, 'Aries', '牡羊座', '裏表がなくチャレンジャー' ), -> ( 2, 'Taurus', 'おうし座', 'とっても慎重で努力家'), -> ( 3, 'Gemini', '双子座', '多才、ユーモアのある人気者'), -> ( 4, 'Cancer', '蟹座', '人当たりが良く仲間思い'), -> ( 5, 'Leo', '獅子座', 'みんなを笑顔にするのが大好き'), -> ( 6, 'Virgo', '乙女座', '知的探究心や鋭い観察眼の持ち主'), -> ( 7, 'Libra', '天秤座', '社交的で人当たりが良い'), -> ( 8, 'Scorpio', 'さそり座', '集中力と忍耐力に長ける優等生'), -> ( null, 'Sagittarius', '射手座', '最も賢く正義感にあふれている'), -> ( null, 'Capricorn', 'やぎ座', '一言で表すなら「努力の人」'), -> ( 11, 'Aquarius', '水瓶座', '固定概念がなく視野が広い'), -> ( 12, 'Pisces', '魚座', '優しくて博愛主義') -> ; Query OK, 12 rows affected (0.04 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql> #confirmation mysql> SELECT * FROM horoscopes; +-------------+--------------+--------------+-----------------------------------------------+ | auto_number | star_sign_en | star_sign_jp | fortune_telling | +-------------+--------------+--------------+-----------------------------------------------+ | 1 | Aries | 牡羊座 | 裏表がなくチャレンジャー | | 2 | Taurus | おうし座 | とっても慎重で努力家 | | 3 | Gemini | 双子座 | 多才、ユーモアのある人気者 | | 4 | Cancer | 蟹座 | 人当たりが良く仲間思い | | 5 | Leo | 獅子座 | みんなを笑顔にするのが大好き | | 6 | Virgo | 乙女座 | 知的探究心や鋭い観察眼の持ち主 | | 7 | Libra | 天秤座 | 社交的で人当たりが良い | | 8 | Scorpio | さそり座 | 集中力と忍耐力に長ける優等生 | | NULL | Sagittarius | 射手座 | 最も賢く正義感にあふれている | | NULL | Capricorn | やぎ座 | 一言で表すなら「努力の人」 | | 11 | Aquarius | 水瓶座 | 固定概念がなく視野が広い | | 12 | Pisces | 魚座 | 優しくて博愛主義 | +-------------+--------------+--------------+-----------------------------------------------+ 12 rows in set (0.01 sec) |
ORDER BY によるNULL値を含めた値のソート方法
まずは、データをauto_numberで昇順/降順ソートしたときの挙動を見てみましょう。
公式リファレンスに記載されている通り、昇順ソートの場合は先頭に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 32 33 34 35 36 37 |
mysql> SELECT * FROM horoscopes ORDER BY auto_number; +-------------+--------------+--------------+-----------------------------------------------+ | auto_number | star_sign_en | star_sign_jp | fortune_telling | +-------------+--------------+--------------+-----------------------------------------------+ | NULL | Sagittarius | 射手座 | 最も賢く正義感にあふれている | | NULL | Capricorn | やぎ座 | 一言で表すなら「努力の人」 | | 1 | Aries | 牡羊座 | 裏表がなくチャレンジャー | | 2 | Taurus | おうし座 | とっても慎重で努力家 | | 3 | Gemini | 双子座 | 多才、ユーモアのある人気者 | | 4 | Cancer | 蟹座 | 人当たりが良く仲間思い | | 5 | Leo | 獅子座 | みんなを笑顔にするのが大好き | | 6 | Virgo | 乙女座 | 知的探究心や鋭い観察眼の持ち主 | | 7 | Libra | 天秤座 | 社交的で人当たりが良い | | 8 | Scorpio | さそり座 | 集中力と忍耐力に長ける優等生 | | 11 | Aquarius | 水瓶座 | 固定概念がなく視野が広い | | 12 | Pisces | 魚座 | 優しくて博愛主義 | +-------------+--------------+--------------+-----------------------------------------------+ 12 rows in set (0.00 sec) mysql> SELECT * FROM horoscopes ORDER BY auto_number desc; +-------------+--------------+--------------+-----------------------------------------------+ | auto_number | star_sign_en | star_sign_jp | fortune_telling | +-------------+--------------+--------------+-----------------------------------------------+ | 12 | Pisces | 魚座 | 優しくて博愛主義 | | 11 | Aquarius | 水瓶座 | 固定概念がなく視野が広い | | 8 | Scorpio | さそり座 | 集中力と忍耐力に長ける優等生 | | 7 | Libra | 天秤座 | 社交的で人当たりが良い | | 6 | Virgo | 乙女座 | 知的探究心や鋭い観察眼の持ち主 | | 5 | Leo | 獅子座 | みんなを笑顔にするのが大好き | | 4 | Cancer | 蟹座 | 人当たりが良く仲間思い | | 3 | Gemini | 双子座 | 多才、ユーモアのある人気者 | | 2 | Taurus | おうし座 | とっても慎重で努力家 | | 1 | Aries | 牡羊座 | 裏表がなくチャレンジャー | | NULL | Sagittarius | 射手座 | 最も賢く正義感にあふれている | | NULL | Capricorn | やぎ座 | 一言で表すなら「努力の人」 | +-------------+--------------+--------------+-----------------------------------------------+ |
では、昇順ソートの場合はNULL値を末尾に表示したいと思います。
まずソートする際に必要なことは、ORDER BY句の記載を以下のようにすることです。
第１ソート：「ソートしたいカラム IS NULL」
第２ソート：「ソートしたいカラム」
具体的なSELECT文と結果は以下のようになりますが、
SELECT句の先頭２列にソートキーの値を表示することで、
なぜNULL値が末尾に表示されるか、一目でわかると思います！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> SELECT auto_number IS NULL,horoscopes.* FROM horoscopes ORDER BY auto_number IS NULL,auto_number; +---------------------+-------------+--------------+--------------+-----------------------------------------------+ | auto_number IS NULL | auto_number | star_sign_en | star_sign_jp | fortune_telling | +---------------------+-------------+--------------+--------------+-----------------------------------------------+ | 0 | 1 | Aries | 牡羊座 | 裏表がなくチャレンジャー | | 0 | 2 | Taurus | おうし座 | とっても慎重で努力家 | | 0 | 3 | Gemini | 双子座 | 多才、ユーモアのある人気者 | | 0 | 4 | Cancer | 蟹座 | 人当たりが良く仲間思い | | 0 | 5 | Leo | 獅子座 | みんなを笑顔にするのが大好き | | 0 | 6 | Virgo | 乙女座 | 知的探究心や鋭い観察眼の持ち主 | | 0 | 7 | Libra | 天秤座 | 社交的で人当たりが良い | | 0 | 8 | Scorpio | さそり座 | 集中力と忍耐力に長ける優等生 | | 0 | 11 | Aquarius | 水瓶座 | 固定概念がなく視野が広い | | 0 | 12 | Pisces | 魚座 | 優しくて博愛主義 | | 1 | NULL | Sagittarius | 射手座 | 最も賢く正義感にあふれている | | 1 | NULL | Capricorn | やぎ座 | 一言で表すなら「努力の人」 | +---------------------+-------------+--------------+--------------+-----------------------------------------------+ 12 rows in set (0.00 sec) |
次に降順ソートの場合はNULL値を先頭に表示したいと思います。
こちらのORDER BY句の記載は以下のようになります。
第１ソート：「ソートしたいカラム IS NULL desc」
第２ソート：「ソートしたいカラム desc」
以下の結果を確認するとNULL値を先頭に表示できます！
結論としてNULL値を意識してソートする場合は、２つ指定する必要があります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> SELECT auto_number IS NULL,horoscopes.* FROM horoscopes ORDER BY auto_number IS NULL desc ,auto_number desc; +---------------------+-------------+--------------+--------------+-----------------------------------------------+ | auto_number IS NULL | auto_number | star_sign_en | star_sign_jp | fortune_telling | +---------------------+-------------+--------------+--------------+-----------------------------------------------+ | 1 | NULL | Sagittarius | 射手座 | 最も賢く正義感にあふれている | | 1 | NULL | Capricorn | やぎ座 | 一言で表すなら「努力の人」 | | 0 | 12 | Pisces | 魚座 | 優しくて博愛主義 | | 0 | 11 | Aquarius | 水瓶座 | 固定概念がなく視野が広い | | 0 | 8 | Scorpio | さそり座 | 集中力と忍耐力に長ける優等生 | | 0 | 7 | Libra | 天秤座 | 社交的で人当たりが良い | | 0 | 6 | Virgo | 乙女座 | 知的探究心や鋭い観察眼の持ち主 | | 0 | 5 | Leo | 獅子座 | みんなを笑顔にするのが大好き | | 0 | 4 | Cancer | 蟹座 | 人当たりが良く仲間思い | | 0 | 3 | Gemini | 双子座 | 多才、ユーモアのある人気者 | | 0 | 2 | Taurus | おうし座 | とっても慎重で努力家 | | 0 | 1 | Aries | 牡羊座 | 裏表がなくチャレンジャー | +---------------------+-------------+--------------+--------------+-----------------------------------------------+ 12 rows in set (0.00 sec) |
NULL値のレコードを正しくカウントする方法は？
ケース1:COUNT関数
まずは失敗例を見ていきましょう。
パッとSQLを見る限りはNULL値をカウントできそうな気配はありますが、
結果は0件です・・・。
前回の記事でも少し触れていますが、
COUNT関数とNULL値の関係性として、COUNT関数の引数にauto_numberを指定した場合は、
NULL値はカウントされませんので、0件になります。
|
1 2 3 4 5 6 7 |
mysql> SELECT auto_number,count(auto_number) FROM horoscopes WHERE auto_number IS NULL group by auto_number; +-------------+--------------------+ | auto_number | count(auto_number) | +-------------+--------------------+ | NULL | 0 | +-------------+--------------------+ 1 row in set (0.00 sec) |
NULL値のレコードを正しくカウントしたい場合は、
以下のように、count(*)を使うと表示できます！！
|
1 2 3 4 5 6 7 |
mysql> SELECT auto_number,count(*) FROM horoscopes WHERE auto_number IS NULL group by auto_number; +-------------+----------+ | auto_number | count(*) | +-------------+----------+ | NULL | 2 | +-------------+----------+ 1 row in set (0.00 sec) |
補足として、NULL値を含めて全レコードをカウントしたい場合も、count(*)を指定します。
|
1 2 3 4 5 6 7 |
mysql> SELECT count(*) FROM horoscopes; +----------+ | count(*) | +----------+ | 12 | +----------+ 1 row in set (0.00 sec) |
どうしてもカラムを指定して全レコードをカウントしたい場合は、
IFNULL関数やCOALESCE関数、CASEを利用することで実現できますが・・・
以下のように「IS NULL」や「<=>」演算子を利用しても表示できます！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> SELECT count(auto_number IS NULL) FROM horoscopes; +----------------------------+ | count(auto_number IS NULL) | +----------------------------+ | 12 | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT count(auto_number <=> NULL) FROM horoscopes; +-----------------------------+ | count(auto_number <=> NULL) | +-----------------------------+ | 12 | +-----------------------------+ 1 row in set (0.01 sec) |
ケース2:SUM関数
ここまでの記事を読んで勘の良い方は、
どうすればNULL値のレコードを正しくカウントできるか見当がつくと思います。
ヒントは以下のSELECT文の結果を利用します・・・
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> SELECT auto_number IS NULL,auto_number FROM horoscopes; +---------------------+-------------+ | auto_number IS NULL | auto_number | +---------------------+-------------+ | 0 | 1 | | 0 | 2 | | 0 | 3 | | 0 | 4 | | 0 | 5 | | 0 | 6 | | 0 | 7 | | 0 | 8 | | 1 | NULL | | 1 | NULL | | 0 | 11 | | 0 | 12 | +---------------------+-------------+ 12 rows in set (0.00 sec) |
以下のように「IS NULL」や「<=>」演算子 ＋ 「SUM関数」を利用しても表示できます！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> SELECT sum(auto_number IS NULL) FROM horoscopes; +--------------------------+ | sum(auto_number IS NULL) | +--------------------------+ | 2 | +--------------------------+ 1 row in set (0.00 sec) mysql> SELECT sum(auto_number <=> NULL) FROM horoscopes; +---------------------------+ | sum(auto_number <=> NULL) | +---------------------------+ | 2 | +---------------------------+ 1 row in set (0.00 sec) |
こちらもIFNULL関数やCOALESCE関数、CASEを利用することで実現できます。
例えばCASEを利用すると様々な条件式を記載でき、自由度も高いですが、
記述も長くなってしまいますので、ケースバイケースで使い分けする形も良いと思います。
|
1 2 3 4 5 6 7 8 |
mysql> SELECT sum(CASE WHEN auto_number is NULL THEN 1 END ) FROM horoscopes; +------------------------------------------------+ | sum(CASE WHEN auto_number is NULL THEN 1 END ) | +------------------------------------------------+ | 2 | +------------------------------------------------+ 1 row in set (0.00 sec) |
IN句でNULL値を含めた検索はできる/できない
前回の記事で関係比較演算子や論理演算子を利用して、
NULL値が表示できるか検証しましたが、どんなに頑張っても表示できませんでした・・・
まずは、深く考えずに文法通りにSELECT文を記載してみましたが、
以下の結果を見ると再び「不可能」、「絶望」という文字が頭に浮かびます・・・。
|
1 2 |
mysql> SELECT * FROM horoscopes WHERE auto_number IN (NULL); Empty set (0.00 sec) |
IN句を利用してNULL値だけを抽出することができない状況で、
NULL値とそれ以外の値を表示することは可能なのか？？？
そして考えること10分～15分・・・
完成したSELECT文と結果は以下の通りです！！！
まずはNULL値のみを抽出する例です。
「auto_number IS NULL」と「1」の組み合わせを使うことで抽出できます！
|
1 2 3 4 5 6 7 |
mysql> SELECT * FROM horoscopes WHERE 1 IN (auto_number IS NULL); +-------------+--------------+--------------+--------------------------------------------+ | auto_number | star_sign_en | star_sign_jp | fortune_telling | +-------------+--------------+--------------+--------------------------------------------+ | NULL | Sagittarius | 射手座 | 最も賢く正義感にあふれている | | NULL | Capricorn | やぎ座 | 一言で表すなら「努力の人」 | +-------------+--------------+--------------+--------------------------------------------+ |
さらに、IN句内でカンマ区切りを利用して、
NULL値とNULL値以外の値を抽出することも可能です。
|
1 2 3 4 5 6 7 8 9 |
mysql> SELECT * FROM horoscopes WHERE 1 IN (auto_number IS NULL,auto_number = 3); +-------------+--------------+--------------+--------------------------------------------+ | auto_number | star_sign_en | star_sign_jp | fortune_telling | +-------------+--------------+--------------+--------------------------------------------+ | 3 | Gemini | 双子座 | 多才、ユーモアのある人気者 | | NULL | Sagittarius | 射手座 | 最も賢く正義感にあふれている | | NULL | Capricorn | やぎ座 | 一言で表すなら「努力の人」 | +-------------+--------------+--------------+--------------------------------------------+ 3 rows in set (0.00 sec) |
そしてIN句をネストすることで、
NULL値＋複数の値を表示することができます！！
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT * FROM horoscopes WHERE 1 IN (auto_number IS NULL,auto_number IN (1,2,3)); +-------------+--------------+--------------+--------------------------------------------+ | auto_number | star_sign_en | star_sign_jp | fortune_telling | +-------------+--------------+--------------+--------------------------------------------+ | 1 | Aries | 牡羊座 | 裏表がなくチャレンジャー | | 2 | Taurus | おうし座 | とっても慎重で努力家 | | 3 | Gemini | 双子座 | 多才、ユーモアのある人気者 | | NULL | Sagittarius | 射手座 | 最も賢く正義感にあふれている | | NULL | Capricorn | やぎ座 | 一言で表すなら「努力の人」 | +-------------+--------------+--------------+--------------------------------------------+ 5 rows in set (0.00 sec) |
まさかのLIKEとNULL値のコラボは実現するのか・・・
IN句でNULL値を含めた検索ができるのであれば、
もしかしたらLIKEとNULL値のコラボも実現する方法があるのでは？
・・・そんな些細な疑問から
無謀とも思えるこのお題を検証してみました！
まずは一般的な文法に沿って、LIKEとNULL値を組み合わせてみると
以下のように表示される件数は０件です・・・。
|
1 2 |
mysql> SELECT * FROM horoscopes WHERE auto_number like NULL; Empty set (0.00 sec) |
結論としては、
LIKEとNULL値だけ組み合わせても、なかなか実現は難しいです・・・
しかし、LIKEとNULL値とIN句を組み合わせると、
もしかしたら出来るのではないかなと考え、検証をしてみました。
「いやいや、そもそもLIKEとIN句を同時に利用することは出来ないでしょう」
・・・そう思う方もいると思いますが、なんと同時に利用可能です！！
|
1 2 3 4 5 6 7 8 9 10 |
mysql> SELECT * FROM horoscopes WHERE 1 IN (auto_number like '1%',auto_number like '3%'); +-------------+--------------+--------------+-----------------------------------------+ | auto_number | star_sign_en | star_sign_jp | fortune_telling | +-------------+--------------+--------------+-----------------------------------------+ | 1 | Aries | 牡羊座 | 裏表がなくチャレンジャー | | 3 | Gemini | 双子座 | 多才、ユーモアのある人気者 | | 11 | Aquarius | 水瓶座 | 固定概念がなく視野が広い | | 12 | Pisces | 魚座 | 優しくて博愛主義 | +-------------+--------------+--------------+-----------------------------------------+ 4 rows in set (0.00 sec) |
そして、以下のように記載することで、
少し無理やりですが、NULL値を含めたあいまい検索の完成です！！！
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT * FROM horoscopes WHERE 1 IN ((auto_number IS NULL) like '1',auto_number like '%2'); +-------------+--------------+--------------+--------------------------------------------+ | auto_number | star_sign_en | star_sign_jp | fortune_telling | +-------------+--------------+--------------+--------------------------------------------+ | 2 | Taurus | おうし座 | とっても慎重で努力家 | | NULL | Sagittarius | 射手座 | 最も賢く正義感にあふれている | | NULL | Capricorn | やぎ座 | 一言で表すなら「努力の人」 | | 12 | Pisces | 魚座 | 優しくて博愛主義 | +-------------+--------------+--------------+--------------------------------------------+ 4 rows in set (0.00 sec) |
もう少しスッキリ記載すると以下の２パターンになります。
SELECT文だけに注目すると、2パターン目に記載しているIN句の左辺は「TRUE」と記載した方が表示できるイメージを持ちやすいかもしれません！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> SELECT * FROM horoscopes WHERE 1 IN (auto_number IS NULL , auto_number like '%2'); +-------------+--------------+--------------+--------------------------------------------+ | auto_number | star_sign_en | star_sign_jp | fortune_telling | +-------------+--------------+--------------+--------------------------------------------+ | 2 | Taurus | おうし座 | とっても慎重で努力家 | | NULL | Sagittarius | 射手座 | 最も賢く正義感にあふれている | | NULL | Capricorn | やぎ座 | 一言で表すなら「努力の人」 | | 12 | Pisces | 魚座 | 優しくて博愛主義 | +-------------+--------------+--------------+--------------------------------------------+ mysql> SELECT * FROM horoscopes WHERE TRUE IN (auto_number IS NULL , auto_number like '%2'); +-------------+--------------+--------------+--------------------------------------------+ | auto_number | star_sign_en | star_sign_jp | fortune_telling | +-------------+--------------+--------------+--------------------------------------------+ | 2 | Taurus | おうし座 | とっても慎重で努力家 | | NULL | Sagittarius | 射手座 | 最も賢く正義感にあふれている | | NULL | Capricorn | やぎ座 | 一言で表すなら「努力の人」 | | 12 | Pisces | 魚座 | 優しくて博愛主義 | +-------------+--------------+--------------+--------------------------------------------+ 4 rows in set (0.00 sec) |
IN句やLIKEを利用してSELECT文は記載できたけど、インデックスは使えないのでは？
・・・そんな疑問が出てくると思いますが、それは次回以降記事にしたいと思います！！
まとめ
本ブログを記述するにあたり、
IN句やLIKEを利用してNULL値を含めたデータを抽出するというのは、
さっとWEBで調べたところ、ストライクな情報が記載されているページは見当たりませんでした。。。
本ブログ執筆前は、それほどNULL値の検証に苦戦するとは考えていなかった一方、
ある程度求めていた結果が得られたときは「やればできる」という言葉が浮かびました。
「やればベストを尽くすことができる。挑戦こそ楽しいことである。」
・・・という意味が込められた
この言葉の通り、挑戦してベストを尽くすことができたと思いますので、
今回、紹介した内容が皆様のご参考にもなればより幸いです！！
次回は、「Left JoinとNULL値の関連性」や「NULL値とインデックスの関連性」などについて紹介したいと考えています！
