Q&A
タグは実際に使うデータベースだけにしましょう。何でも数撃ちゃ当たると思っても同じSQLでもデータベースやそのバージョンによって方言が大きいですからエラーが出まくります。
###質問内容
2つのテーブルのレコードの総当たり表を出力するためには、下記の3つの方法があると認識しております。
この3つの方法の違いについて教えてください。
パフォーマンスや、クエリ処理される手順、注意すべき点、DBによる違い等々。
方法①：INNER JOIN - ON 1
方法②: CROSS JOIN
方法③: SELECT -, (SELECT -)
また、他に方法があれば、その方法と上記3つの方法との違いについても教えてください。
###2つのテーブル
(TABLE1):
| ID | DATA |
|---|---|
| A | 100 |
| B | 101 |
| C | 102 |
(TABLE2):
| ID | NAME |
|---|---|
| A | あ |
| B | い |
| C | う |
###出力結果
| DATA | NAME |
|---|---|
| 100 | あ |
| 101 | あ |
| 102 | あ |
| 100 | い |
| 101 | い |
| 102 | い |
| 100 | う |
| 101 | う |
| 102 | う |
| ###方法① INNER JOIN - ON 1 | |
| ```SQL | |
| SELECT T1.DATA, T2.NAME FROM TABLE1 T1 | |
| INNER JOIN TABLE2 T2 on 1 | |
| ``` | |
| ###方法② CROSS JOIN | |
| ```SQL | |
| SELECT T1.DATA, T2.NAME from TABLE1 T1 | |
| CROSS JOIN TABLE2 T2 | |
| ``` | |
| ###方法③ SELECT -, (SELECT -) | |
| ```SQL | |
| SELECT T1.DATA, T2.NAME FROM TABLE1 T1, | |
| (SELECT NAME FROM TABLE2) T2 | |
| ``` | |
| ###前提条件 | |
| 現環境はMySQLを用いています。が、他のDBも扱うため他のDBでのことについても知りたいです。 | |
| TABLE1, TABLE2 は下記でCREATE, INSERTしています。 |
SQL
1CREATE TABLE `TABLE1` ( 2 `ID` char(2) NOT NULL, 3 `DATA` int(4) DEFAULT NULL, 4 PRIMARY KEY (`ID`) 5) 6INSERT INTO `TABLE1` VALUES 7('A',100), 8('B',101), 9('C',102);
SQL
1CREATE TABLE `TABLE2` ( 2 `ID` char(2) NOT NULL, 3 `NAME` char(4) DEFAULT NULL, 4 PRIMARY KEY (`ID`) 5) 6INSERT INTO `TABLE2` VALUES 7('A','あ'), 8('B','い'), 9('C','う');
回答2件
0
ベストアンサー
mysql上でinner joinとcross join(およびタダのjoin)は全て同等です
標準SQLと異なるという認識だけ必要です
投稿2020/02/28 01:39
総合スコア114779
結局、以下は内部的に全て同等（オプティマイズされるので）
select name,data from table1,table2
select name,data from table1 join table2
select name,data from table1 inner join table2
select name,data from table1 cross join table2
outer join すると処理が異なります（推奨はできませんが）
select name,data from table1 left join table2 on 1
select name,data from table1 right join table2 on 1
※right joinはleftを裏返しての処理しているだけです
回答ありがとうございました。
他の方法についても列挙していただき理解することができました。
@yambejpさん
> mysql上でinner joinとcross join(およびタダのjoin)は全て同等です
inner join とcross joinがMySQLでは同じとは一体どこにそんな文献が？
※on 指定しなければって事ですか？
TO:saziさん
https://dev.mysql.com/doc/refman/5.6/ja/join.html
私も疑念があったのですが公式にそうかいてあるのでそうなんでしょう
「MySQL では、JOIN、CROSS JOIN、および INNER JOIN は構文上同等です (互いに置き換えることができます)。標準 SQL では、それらは同等ではありません。」
テーブルの列記についてはexplainの結果を参考としています
@yambejpさん
引用の続きに
> INNER JOIN は ON 句とともに使用され、CROSS JOIN はそれ以外のときに使用されます。
ってありますね。
ところがmysqlのinner joinはonなしで動作するんですよ・・・
そしてcross joinにonが効きます
mysql自体の仕様なのでそういうものだと理解するしかありません
（おそらく他のRDBへの互換用のごまかしなのでしょう）
0
やられたい事は直積(直積集合)言います。
cross join で表現するか単にテーブルやサブクエリーを,で区切ってfrom句に並べます。
記述の方式によってパフォーマンスに差が出る事は無いと思いますし、聞いたこともありません。
大量件数になる組み合わせの直積はあまりお勧めできるものではありません。
その直積を元に別な表と結合するような場合に性能が悪い事が多いからです。
そういった場合、一旦テーブルに落としてからインデックスなどを利用するようにします。
投稿2020/02/27 08:31
編集2020/02/27 08:36総合スコア25173
あなたの回答
tips
プレビュー
