MYSQL １対複数のLEFT JOINを1行で表示できるようにする。

2020年1月14日2021年6月2日

　webプログラムを組む際にはデータベースとの連結は必須です。
かのワードプレスやEC-CUBEといったメジャーなプログラムはすべてデータベースと連結して豊富な機能を実現しています。

私も請謁ながらwebプログラム開発をさせていただくことがしばしばございます。その際にMYSQLと連結させて開発しています。

今回はちょっとしたことですが、役立ったテクニックがありましたので、忘備録として記録いたします。

今回やりたかったこと

データベースには異なったテーブル同士を「結合」して参照する方法があります。マイクロソフトアクセスでいうところの「クエリ」です。

結合方法にはいろいろあるのですが、今回は「LEFT　JOIN」で1対複数のデータを結合します。

例えば1対10で結合したとすると、SELECTで最低10行のデータが読み込みされます。

今回これを1行で読み込みしたいと思いました。

早速ググって調べました。

たいていのことはググって調べると出てきます。
今回もちゃんと出てきました。

それによると「 GROUP_CONCAT」関数を使用するとできるとありました。
CONCATってエクセルにもある関数なのでイメージが付きます。
文字結合です。

ということで、MYSQLで実験してみました。

テストテーブルとデータは以下の通りです。

上位のAテーブルと、下位のBテーブルを準備ました。
それぞれ中身が入っています。

Bテーブルの中身はAテーブルのbase01と結合する形です。

通常のLEFT　JOINで読み込みするとこうなります。

LEFT　JOINでセレクトすると、以下のように複数行にわたって読み込みされます。

■SQL文

---

Bテーブルが3件あるので、結合して読み込むと全部で4行になります。
これはこれでその通りです。

今回base01 に結合しているsub01～sub03の3件を1件にまとめます。

GROUP_CONCAT を使って、テーブルBを一つにまとめます。

ではSQL文に GROUP_CONCAT 関数を追加してまとめて読み込みを実行してみます。

■SQL文

FROMの前にGROUP_CONCATを追加します。

---

予定通り3行になっていたデータが1行にまとまりました。
ってあれ、base02データが読み込みされていません。

LEFT JOINにしているのは、結合データがなくても全てのデータを読み込みしたいのにこれでは具合が悪いです。

何がいけないのでしょうか?

原因は文法不足

GROUP_CONCATを使う場合は、GROUP BY 文が必要です。
それが抜けていたため、base02が呼び出しされなかったのです。
そこで、GROUP BY文をつけて呼び出しします。

■SQL文

---

GROUP BY 文を付け足すことでちゃんと読み込みされるようになりました。

ソートして読み込みしたい場合はORDER BY文を付け足します。

実用的なことを言えば、データのソートが必要になります。
そこで、ORDER BY文を付け足します。

■SQL文

ソートを付け足せばOKです。

---

あれ、文法エラーになりました。
なぜでしょうか?

原因はORDER BYとGROUP BYの表記順番

文法エラーになるのは、ORDER BY文とGROPU BY文の表記順番が間違っているためです。

この2つの文には意外にも表記順番があります。
それがこちらです。

■SQL文

なんだかんだと言いつつ、GROUP BYを先にして、ORDER BYを後にします。

---

うまく呼び出しができました。

SQL文は組み合わせを考えて効率よく

今回は1対複数結合を1行にまとめるという目的でGROUP_CONCATを使用しました。そもそも結合データをキーワード検索をする際に、テーブルAだけの情報を出力したいと考えたのですが、通常LEFT JOINだと同じデータが複数表示されるため、試行錯誤しました。

あとから、サブクエリで検索したほうが早くないか？と気づきサブクエリに変えました。

ただ、サブクエリはケースによっては重くなることもあるため、必ずしもサブクエリが良いとも言えません。

そういった意味では、GROUP_CONCATは利便性の高い関数であることを体験できました。