あまり無いと思いますが、テーブルをJOINで結合する条件にNULLが含まれる場合にどう対応するかということ考えていこうと思います。
ただ、今回紹介する方法はアンチパターンな気がします。あくまで、こういった方法で抽出できますよ、という例として紹介します。
例えば以下のような2つのテーブルがあったとします。"今飼っているペットの数を管理しているデータベース"とでもしましょう。
animalテーブルでは、ペットの種類と、種別、原産国が格納されています。種別が分からないものはtypeにNULLが入ります。
animal_cntテーブルでは、ペットの種類と、種別、飼っている数が格納されています。ワンコとニャンコがわんさか居て楽しそうですね。こちらも種別が分からないものはtypeにNULLが入ります。
animal
+--------+---------+--------+
| animal | type | origin |
+--------+---------+--------+
| inu | NULL | NULL |
| inu | shiba | japan |
| inu | pug | china |
| inu | samoyed | russia |
| neko | NULL | NULL |
| neko | mike | japan |
| neko | exotic | us |
| neko | ragdoll | us |
+--------+---------+--------+animal_cnt
+--------+---------+-----+
| animal | type | cnt |
+--------+---------+-----+
| inu | NULL | 2 |
| inu | NULL | 2 |
| inu | NULL | 2 |
| inu | shiba | 2 |
| inu | shiba | 3 |
| inu | pug | 2 |
| inu | pug | 2 |
| inu | pug | 2 |
| inu | samoyed | 1 |
| inu | samoyed | 1 |
| inu | samoyed | 1 |
| neko | NULL | 1 |
| neko | mike | 1 |
| neko | mike | 1 |
| neko | exotic | 1 |
| neko | exotic | 3 |
| neko | ragdoll | 1 |
| neko | ragdoll | 2 |
| neko | ragdoll | 3 |
+--------+---------+-----+この2つのテーブルを使って、「種類、種別ごとの合計数と原産国を一覧にする」という要望があったとします。どういったSQLで出せるでしょうか。
真っ先に思いつくのは、以下のパターンでしょうか。ただ、実際に実行してみると思った結果がでません。
SELECT
animal.*,
SUM(animal_cnt.cnt) AS sum
FROM animal
LEFT JOIN animal_cnt
ON animal.animal = animal_cnt.animal
AND animal.type = animal_cnt.type
GROUP BY animal_cnt.animal, animal_cnt.type;+--------+---------+--------+------+
| animal | type | origin | sum |
+--------+---------+--------+------+
| inu | NULL | NULL | NULL |
| inu | pug | china | 6 |
| inu | samoyed | russia | 3 |
| inu | shiba | japan | 5 |
| neko | exotic | us | 4 |
| neko | mike | japan | 2 |
| neko | ragdoll | us | 6 |
+--------+---------+--------+------+LEFT JOINが駄目ならばRIGHT JOINだ、とやってみても結果はでません。animalがNULLになってしまいます。結果としては惜しいなどと考えてしまうと、泥沼にハマってしまいますのでご注意を。
SELECT
animal.*,
SUM(animal_cnt.cnt) AS sum
FROM animal
RIGHT JOIN animal_cnt
ON animal.animal = animal_cnt.animal
AND animal.type = animal_cnt.type
GROUP BY animal_cnt.animal, animal_cnt.type;+--------+---------+--------+------+
| animal | type | origin | sum |
+--------+---------+--------+------+
| NULL | NULL | NULL | 6 |
| inu | pug | china | 6 |
| inu | samoyed | russia | 3 |
| inu | shiba | japan | 5 |
| NULL | NULL | NULL | 1 |
| neko | exotic | us | 4 |
| neko | mike | japan | 2 |
| neko | ragdoll | us | 6 |
+--------+---------+--------+------+じゃあ、あらかじめサブクエリでGROUP BYしてしまって、その結果をJOINしてみましょう。これでもsumがNULLになってしまいます。
SELECT
animal.*,
animal_sum.cnt AS sum
FROM animal
LEFT JOIN (
SELECT
animal,
type,
SUM(cnt) AS cnt
FROM animal_cnt
GROUP BY animal, type
) AS animal_sum
ON animal.animal = animal_sum.animal
AND animal.type = animal_sum.type;+--------+---------+--------+------+
| animal | type | origin | sum |
+--------+---------+--------+------+
| inu | NULL | NULL | NULL |
| inu | shiba | japan | 5 |
| inu | pug | china | 6 |
| inu | samoyed | russia | 3 |
| neko | NULL | NULL | NULL |
| neko | mike | japan | 2 |
| neko | exotic | us | 4 |
| neko | ragdoll | us | 6 |
+--------+---------+--------+------+どれも思った通りの結果を取得できませんでした。これは、JOIN句で指定されている条件に要因があります。
今回JOIN句の条件としてtypeが指定していますが、typeのデータにはNULLが含まれています。例えばNULL同士を結合条件として指定すると、NULL = NULLとなりますが、これは果たしてTRUE(1)でしょうか、FALSE(0)でしょうか。
実は、NULL = NULLはTRUE(1)でも、FALSE(0)でもなく、NULLになります。
試しに以下のSQLを実行してみますと、上から3つのクエリはすべて1ですが、NULL = NULLだけはNULLが返ってきます。
SELECT TRUE = TRUE;
SELECT FALSE = FALSE;
SELECT "" = "";
SELECT NULL = NULL;つまり、NULL同士を結合条件に指定しても、NULLが返ってきてしまい、うまく結合できないのです。
そこで1つの解決方法を提示します。IFNULLという関数を使います。
IFNULLは2つの引数を渡しますが、1つ目の引数がNULLでない場合はその引数を返し、1つ目の引数がNULLであれば2つ目の引数を返します。
ということは、IFNULLの1つ目の引数に、対象のカラムを指定して、2つ目の引数には両者に固定値で同じ値を指定すれば良さそうです。今回は''(空)を指定してみました。
SELECT
animal.*,
SUM(animal_cnt.cnt) AS cnt
FROM animal
LEFT JOIN animal_cnt
ON animal.animal = animal_cnt.animal
AND IFNULL(animal.type, '') = IFNULL(animal_cnt.type, '')
GROUP BY animal_cnt.animal, animal_cnt.type;+--------+---------+--------+------+
| animal | type | origin | cnt |
+--------+---------+--------+------+
| inu | NULL | NULL | 6 |
| inu | pug | china | 6 |
| inu | samoyed | russia | 3 |
| inu | shiba | japan | 5 |
| neko | NULL | NULL | 1 |
| neko | exotic | us | 4 |
| neko | mike | japan | 2 |
| neko | ragdoll | us | 6 |
+--------+---------+--------+------+これで思ったとおりの結果がでました。こういったパターンは結構限られた状態でしか発生しないかもしれませんが、正規化されていないDBでは出てくるかもしれません。
また、複雑な情報を1回のクエリで取得するというような場合でも発生しやすいと思います。私もそういう環境で実際にこの対応をとりました。
工夫すれば何とかできますね、という紹介でした。おつかれさまでした。
今回の説明で利用したファイルはこちらにあります。実験用データも含めています。