新規に質問を投稿する
「INNER JOIN」と「LEFT OUTER JOIN」
2019-12-24 13:01
お世話になります。
SQLについて質問させていただきます。
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
select
C.部門名称
from
re117k_契約基本情報 A
INNER JOIN
re000_共通ユーザーマスタ B
On
A.担当者ID =B.ユーザーID
INNER JOIN
re000_共通部門情報 C
On
B.所属部門コード = C.部門コード
WHERE
A.退会日_年 IS NULL
GROUP BY C.部門名称
ORDER BY C.部門コード DESC
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
上記のSQLをCELFで実行すると、いつまでも終わらないので、仕方ないから強制終了しています。
ですが、このSQLの２ヶ所の「INNER JOIN」を「LEFT OUTER JOIN」に変えると、
普通に動くようになります。
なぜ「LEFT OUTER JOIN」なら動くのに、「INNER JOIN」だとダメなのか…。
もちろん、テーブルの中身は「INNER JOIN」でも問題ない内容になっていますので、テーブル設計上の問題ではないはずだと思います。
こういった現象について、情報をお持ちの方がいらしたら、お教え願えればと思います。
どうぞよろしくお願いいたします。
回答を投稿するにはログインが必要です。
こんにちは。
大変勉強になりました。とても助かります。
思えばその昔デスクトップDBなどではそもそもテーブル結合にはインデックスが必須でした。
CELFの場合アクションの「テーブルから複数件取得する」の機能が弱い（結合の種類だけでなく複雑な検索条件が設定できない、SELECTにカラム以外を指定できないなど）のでSQLを書かざるを得ません。
HELPにSQLの編集画面のスクリーンショットがあるので、問い合わせたところ
「現在のところ、SQLアクションの編集ダイアログを表示する機能の提供はございません。
当機能につきましては、近々リリースされる予定です。今暫くお待ちいただけますでしょうか。」との回答を頂いております。
データ取得アクションの機能強化と、SQLアクション関係の強化の両面を期待しているところです。
大変勉強になりました。とても助かります。
思えばその昔デスクトップDBなどではそもそもテーブル結合にはインデックスが必須でした。
CELFの場合アクションの「テーブルから複数件取得する」の機能が弱い（結合の種類だけでなく複雑な検索条件が設定できない、SELECTにカラム以外を指定できないなど）のでSQLを書かざるを得ません。
HELPにSQLの編集画面のスクリーンショットがあるので、問い合わせたところ
「現在のところ、SQLアクションの編集ダイアログを表示する機能の提供はございません。
当機能につきましては、近々リリースされる予定です。今暫くお待ちいただけますでしょうか。」との回答を頂いております。
データ取得アクションの機能強化と、SQLアクション関係の強化の両面を期待しているところです。
CELFサポートセンターに、こちらで実行していたA・B・Cの３テーブルを
添付して問い合わせをしたところ、以下の回答をいただきました。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【回答】
添付のデータをもとに検証しましたところ、
当方でも現象を確認いたしました。
しかしながら、CELF以外の別ツールからSQLを実行した場合も
同様の現象が発生することから、 この現象は、CELFに起因した現象
ではなく、MySQLに起因した現象であり、根本的な原因については
CELFサポートセンターからは回答いたしかねます。
大変恐れ入りますが、何卒ご容赦ください。
以下は動作検証した結果となります。
ご提示のSQL文の実行計画をそれぞれ確認したところ、
テーブルA⇔Cの結合が先に処理されており、この結合が原因で処理に
時間を要しておりました。
■2789行、もしくは、LEFT OUTER JOIN の場合
AテーブルとBテーブルを JOIN → 結果をCテーブルと JOIN
■2790行、かつ、INNER JOIN
AテーブルとCテーブルを JOIN → 結果をBテーブルと JOIN
AテーブルとCテーブルの間には結合条件が無いためクロス結合となり、
この結合が処理時間増加の原因だと考えられます。
この現象を回避する方法として、INDEX を設定していただき、
AテーブルとBテーブルの結合を優先させることが有効かと存じます。
■テーブル検索を高速化する（インデックス設定）
https://cloud.celf.jp/celf-help/ja/texts/table/set_index/set_index.html
AテーブルとBテーブルの ON 句で使用している以下の項目を、
INDEX に設定することで、処理時間が改善することを確認いたしました。
※
z000_契約基本情報.担当者ID
z000_共通ユーザーマスタ.ユーザーID
回答は以上となります。
ご参考いただけますと幸いです。
宜しくお願い申し上げます。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
以上がCELFサポートセンターからいただいた回答です。
Yexiongmao様
ご協力いただき、ありがとうございました。
添付して問い合わせをしたところ、以下の回答をいただきました。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【回答】
添付のデータをもとに検証しましたところ、
当方でも現象を確認いたしました。
しかしながら、CELF以外の別ツールからSQLを実行した場合も
同様の現象が発生することから、 この現象は、CELFに起因した現象
ではなく、MySQLに起因した現象であり、根本的な原因については
CELFサポートセンターからは回答いたしかねます。
大変恐れ入りますが、何卒ご容赦ください。
以下は動作検証した結果となります。
ご提示のSQL文の実行計画をそれぞれ確認したところ、
テーブルA⇔Cの結合が先に処理されており、この結合が原因で処理に
時間を要しておりました。
■2789行、もしくは、LEFT OUTER JOIN の場合
AテーブルとBテーブルを JOIN → 結果をCテーブルと JOIN
■2790行、かつ、INNER JOIN
AテーブルとCテーブルを JOIN → 結果をBテーブルと JOIN
AテーブルとCテーブルの間には結合条件が無いためクロス結合となり、
この結合が処理時間増加の原因だと考えられます。
この現象を回避する方法として、INDEX を設定していただき、
AテーブルとBテーブルの結合を優先させることが有効かと存じます。
■テーブル検索を高速化する（インデックス設定）
https://cloud.celf.jp/celf-help/ja/texts/table/set_index/set_index.html
AテーブルとBテーブルの ON 句で使用している以下の項目を、
INDEX に設定することで、処理時間が改善することを確認いたしました。
※
z000_契約基本情報.担当者ID
z000_共通ユーザーマスタ.ユーザーID
回答は以上となります。
ご参考いただけますと幸いです。
宜しくお願い申し上げます。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
以上がCELFサポートセンターからいただいた回答です。
Yexiongmao様
ご協力いただき、ありがとうございました。
Yexiongmao様
度々ありがとうございます。
またお手すきの際にご協力いただけると嬉しいです。
どうぞよろしくお願いいたします。
度々ありがとうございます。
またお手すきの際にご協力いただけると嬉しいです。
どうぞよろしくお願いいたします。
ベストアンサー
こんにちは。
テストなのでデータ件数の少ないものを試してみました。
Bの件数は数百件程度、Aのデータも絞ってあるので、恐らく最初のJOIN ONの結果のサブテーブル件数は十数件程です。
数千件程度で体感できるほどスピードが変わるのはおかしいですね。
Bに数万件程度のデータを使って試してみます。
テストなのでデータ件数の少ないものを試してみました。
Bの件数は数百件程度、Aのデータも絞ってあるので、恐らく最初のJOIN ONの結果のサブテーブル件数は十数件程です。
数千件程度で体感できるほどスピードが変わるのはおかしいですね。
Bに数万件程度のデータを使って試してみます。
Yexiongmao様
いろいろご協力いただき、ありがとうございます。
可能でしたら、Bのテーブルの件数を教えていただけないでしょうか？
あの後いろいろ検証しましたところ、
Bテーブルのレコード数が2789行ならすんなりとSQLが通るが、2790行なら激遅。
↑こういう事象が確かめられました。
中途半端な件数なので、単にレコード数だけで切り分けされてる
というわけではないのかもしれませんが。。
恐れ入りますが、またどうぞよろしくお願いいたします。
いろいろご協力いただき、ありがとうございます。
可能でしたら、Bのテーブルの件数を教えていただけないでしょうか？
あの後いろいろ検証しましたところ、
Bテーブルのレコード数が2789行ならすんなりとSQLが通るが、2790行なら激遅。
↑こういう事象が確かめられました。
中途半端な件数なので、単にレコード数だけで切り分けされてる
というわけではないのかもしれませんが。。
恐れ入りますが、またどうぞよろしくお願いいたします。
おはようございます。
CELFでテストのため、現役のDBを使用して、以下のようなSQLを試してみましたが、正常にデータ取得できました。
また、INNER JOINをLEFT OUTER JOINに変えて見たところ、全く同じデータを取得できました。
ご質問のSQLには問題がないように思えます。
INNNER JOINでエラーにもならず固まってしまうのはDBの定義かデータの何かにCELFのバグが絡んでいる可能性が高いような気がします。
****************************************************************************************
SELECT C.部門名 FROM 売上入力 A
INNER JOIN 社員マスタ B ON A.社員CD = B.社員CD
INNER JOIN 組織マスタ C ON B.部門CD = C.部門CD
WHERE A.伝票STS = 40
GROUP BY C.部門名
ORDER BY C.部門CD
CELFでテストのため、現役のDBを使用して、以下のようなSQLを試してみましたが、正常にデータ取得できました。
また、INNER JOINをLEFT OUTER JOINに変えて見たところ、全く同じデータを取得できました。
ご質問のSQLには問題がないように思えます。
INNNER JOINでエラーにもならず固まってしまうのはDBの定義かデータの何かにCELFのバグが絡んでいる可能性が高いような気がします。
****************************************************************************************
SELECT C.部門名 FROM 売上入力 A
INNER JOIN 社員マスタ B ON A.社員CD = B.社員CD
INNER JOIN 組織マスタ C ON B.部門CD = C.部門CD
WHERE A.伝票STS = 40
GROUP BY C.部門名
ORDER BY C.部門CD
Yexiongmao様
わざわざありがとうございます。
引き続き情報をお待ちしてます。
わざわざありがとうございます。
引き続き情報をお待ちしてます。
申し訳ございません。
頭からできないと思っていたので、必要な場合はLEFT OUTER JOINを使用していましたが、調べたところMySQLでも、INNER JOINでA→B→Cの結合は全く可能なようです。
となると、CELF固有の現象なのか調べてみようと思っている次第です。
頭からできないと思っていたので、必要な場合はLEFT OUTER JOINを使用していましたが、調べたところMySQLでも、INNER JOINでA→B→Cの結合は全く可能なようです。
となると、CELF固有の現象なのか調べてみようと思っている次第です。
Yexiongmao様
早速のご回答ありがとうございます。
ーーーーーーーーーーーーーーーーーーー
SQL Serverの場合は、
AとBをINNER JOIN、BとCをINNER JOIN、
A←→B←→C
ということが可能という認識なのですが、
MySQLでは
AとBをINNER JOIN、AとCをINNER JOIN、
A←→B
A←→C
このような書き方しか受け付けないのでしょうか？
恐れ入りますが、またご指導のほど、よろしくお願いいたします。
早速のご回答ありがとうございます。
ーーーーーーーーーーーーーーーーーーー
SQL Serverの場合は、
AとBをINNER JOIN、BとCをINNER JOIN、
A←→B←→C
ということが可能という認識なのですが、
MySQLでは
AとBをINNER JOIN、AとCをINNER JOIN、
A←→B
A←→C
このような書き方しか受け付けないのでしょうか？
恐れ入りますが、またご指導のほど、よろしくお願いいたします。
こんにちは。
（INNER) JOIN はメインテーブル（FROM XXXX）から結合するので、B→C の結合はできないと思います。
LEFT OUTER JOINは、本来A→Bの結合からネストしなければならないのですが、A→B→Cの場合は、テーブルの出現順になるのでエラーにならないようです。
（INNER) JOIN はメインテーブル（FROM XXXX）から結合するので、B→C の結合はできないと思います。
LEFT OUTER JOINは、本来A→Bの結合からネストしなければならないのですが、A→B→Cの場合は、テーブルの出現順になるのでエラーにならないようです。
Q&A一覧へ