MySQL【外部結合について】

Question

食品名を検索して、データを取ってくるということをやろうとしています。
それぞれのテーブルには、
"materials"テーブルには食品名一覧が入っていて、
"materials_search"テーブル"materials_search_02"テーブルには食品名の読み仮名が入っています。(データが1000件以上になったので2テーブルに分けました)
ex)鮭ならシャケ、しゃけ、さけ、サケ、サーモンという具合。

「やりたいこと」
"materials_search"テーブルと"materials_search_02"テーブルから「さけ」とつく名前のものを見つけ出し、"materials"テーブルの中にある「鮭」と「酒」を取得したい。

「補足」
"materials"テーブルの食品名と
"materials_search"テーブル"materials_search_02"テーブルの食品名読み仮名はIDをすべてつけています。
ex)
"materials"テーブル
materials_id　｜　materials
100000 | 酒
100001 | 鮭

"materials_search"テーブル
materials_id　｜　materials_no　|materials_yomi
100000 | 1 |酒
100000 | 2 |さけ
100000 | 2 |サケ

"materials_search"テーブル
materials_id　｜　materials_no　|materials_yomi
100001 | 1 |鮭
100001 | 2 |さけ
100001 | 2 |サケ
100001 | 2 |しゃけ
100001 | 2 |シャケ
100001 | 2 |サーモン

SELECT m.materials
FROM 
materials m RIGHT OUTER JOIN materials_search m1
ON m1.materials_search_name ="さけ"
AND
materials m RIGHT OUTER JOIN materials_search_02 m2
ON m2.materials_search_name = "さけ"

---

ちなみに、これならできました。

SELECT materials
FROM materials NATURAL JOIN materials_search_02
WHERE materials_search_name = "さけ"

【出力結果】
materials
鮭

---

しかし、私が今やりたいのは複数の表("materials_search"テーブル"materials_search_02"テーブル)を検索をして、"materials"テーブルに登録してある名前で出力することです。

情報が足りなければ補足いたします。
お願いいたします。

Comments

[退会済みユーザー]

「質問タグ」に、RDBMS名も指定してください。SQL言語は方言がきついため処理系がわからないと適切な回答をしにくいです。質問文中はもちろんですが、質問のあるなしをMyタグにあるもので絞り込んで表示する方が多いので、タグの指定がかなり重要です。

[jin007]

修正いたしました、ご指摘ありがとうございます！

Answer 1

※過去質問からMysqlと想定（方言は使用してないのでMysqlでなくても大丈夫だと思いますが）

select * from materials
where materials_id in (
        select materials_id from materials_search where materials_yomi='さけ'
        union all
        select materials_id from materials_search02 where materials_yomi='さけ'
      )

Comments

[jin007]

ありがとうございます。
他の検索もする際副問合せや集合演算子を参考にします

Answer 2

質問とは離れますが、

"materials_search"テーブルと"materials_search_02"テーブル

一連の情報はひとつのまとめるのが、テーブル設計の基本なので、ひとつのテーブルにまとめておいた方が良さそうに思えます。

Comments

[jin007]

1つのテーブルに1000件しかデータを格納できない為2つに分けました。
現在、2000近くのデータがあります。
1000件以上格納できるテーブルの作り方が分からずこうしております。

[Orlofsky]

1つのテーブルに1000件しかデータを格納できないのはなぜですか？

D.10.3 テーブルサイズの制限
https://dev.mysql.com/doc/refman/5.6/ja/table-size-limit.html

MySQLでビッグデータ（１億レコード以上のデータ）を作って遊ぼう
https://qiita.com/saitamasaitama/items/3ca05f8bd22044bfa26a

[jin007]

こちらの記事に1テーブルに格納できる最大カラム数は1017個ときさいされているため、上限の増やし方が分かりません。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-restrictions.html

[Orlofsky]

カラム数とはmaterials_id, materials_no, materials_yomiなどの列の数です。レコード件数は上記URLやディスクの容量の上限になりますから、テーブルを materials_search と materials_search_02 に分ける必要はありません。
１億レコード以上のデータを1,000件毎に分けたらSQLはUNIONの嵐になりパフォーマンスがとんでもなく悪くなります。