MYSQLでテーブルを使用する際に、「結合」して使うということがしばしばあります。
例えばパターンが会員データと、会員のサービス情報の結合です。
会員番号と、会員の使用する情報を結合して情報を引き出します。
この場合、「JOIN」句を使用して結合します。
ところが今回、JOINでの結合が不可能なテーブルの結合という無理難題が起こりました。
何とかならないものかとググってググって調べたところ、JOINを使わない結合方法で対処することができました。
忘備録として記録いたします。
今回やりたいこと
今回フォーマットの異なる３つの食材テーブルを使用する必要がありました。
共通するのは食材名ぐらいです。
この3つのテーブルからすべての食材名を引き出すことを行う必要がありました。
以下のようなテーブルです
それぞれフォーマットが異なり、食材もバラバラです。
重複しているものもあります。
最初はJOINで何とかしようとしましたが失敗しました
どのみち3つのテーブルを結合しないと実現できないので、LEFT JOIN、RIGHT JOIN等を駆使して実現しようと思いました。
しかし以下の要因により実現できませんでした。
- 3つのテーブルにはそれぞれそのテーブルにしかない食材データがある
- JOINするための共通キーがない
１の要因はJOINでは解決できません。
なぜならば、JOINはベースとなるテーブルに別のテーブルを結合するためです。
例えば、上記テーブル例では「野菜」「果物」「混合」に分類されています。
野菜テーブルをベースにした場合、いくらセレクトしても果物やMIXテーブルにしかないキウイを取り出すことができません。
ならばRIGHT JOINすればいいのではと思いますが、そうすると今度は野菜が取り出せません。
２の要因はそもそもJOINの前提を崩しています
JOIN句を使用するためには、結合キーが必須です。
今回共通しているキーは「食材名」だけでそれ以外に共通項目はありません。
食材を取り出したいのに、食材名で結合すると、取りこぼしが発生するのです。
いったいどうすればすべての食材名を取り出すことができるのでしょうか？
UNION句を使用すると共通項がなくても結合できます
いままでテーブル結合はJOIN句だけだと思っていましたが、浅はかでした。
ググってみると、UNION句でも結合できることがわかりました。
しかもJOINとは違い結合キーがいりません。
早速やってみました。
UNION句での結合SQL文
以下のSQL文で処理を実行しました。
SELECT name FROM yasai UNION ALL SELECT name FROM kudamono UNION ALL SELECT name FROM mix ORDER BY name ASC;通常のSELECT文の後にUNION句をつけ、あらたなSELECT文をつないでいます。
実行結果
３つのテーブルの全ての食材が取り出せました。
ただ、同じ食材が複数表示されました。
これは何とかできないのでしょうか?
UNION句で同じものを除外する方法
MYSQLは便利にできていて、UNION句でも「全取得」と「重複除外取得」の2種類指定できるようになっています。
最初のSQL文は「全取得」を指定していたため、重複した食材名が表示されていました。
具体的な記述方法
最初の記述は「UNION ALL」と記述していました。
これを「UNION DISTINCT」と記述すれば重複除外になります。
SQL文は以下の通りです。
SELECT name FROM yasai UNION DISTINCT SELECT name FROM kudamono UNION DISTINCT SELECT name FROM mix ORDER BY name ASC;UNION ALL と記述していた部分を UNION DISTINCTと記述しました。
実行結果
重複することなく、全ての食材名を取り出すことができました。
こんな方法があったのかと驚きです。
UNION句を使用する際に気の付いたこと
UNION句には結合キーが必要ありません。
そのため、全く共通性がなくても結合できます。
今回テストをして気の付いた点を以下に記述します。
- 結合するメンバ名は同じ名称でなくてもOK
- メンバ数（列数）が異なるとエラーになる
今回のサンプルの食材名は「name」で統一されていましたが、異なる名称でも結合できました。
ただし、統一されていないとORDER BY句でエラーになるため、ASを使って名称を統一する必要がありました。
UNION句の後のSELECT文で、カラム数（指定メンバ数）が異なっていてもSQLエラーになりました。
例えば10項目表示にした場合は、結合するすべてのSELECTを10項目にしなければいけませんでした。
JOINと違い、なんでも結合OKなのがUNIONでした。
無論JOINのほうが優れている点もあるので、使い分けが必要でした。
MYSQLは知っているようで知らないことがまだまだたくさんあります。
本記事がお役に立てば幸いです。