サブクエリと外部（内部）結合の違い

Question

今、サブクエリを勉強しているのですが
サブクエリの話を聞くにつれて、「これ外部または内部結合で代用できるしそっちの方が見通しいいような。。。」
と思って、、、

ここで聞きたいのが、サブクエリを用いた時のうまみ、そしてサブクエリにしかできないこととはなんなのでしょうか。

Comments

[m.ts10806]

なにかご自身で検証してみたことはありますか？

Answer 1

副問合せ（サブクエリー）には相関副問合せも含まれます。

相関でない副問合せを結合(join)に置き換え、同じ結果になるのは、直積(cross join)のみです。
相関副問合せを結合に置き換え、同じ結果とするには必然的に外部結合(outer join)になります。

基本的には結合で考えて、状況次第でサブクエリーとする方が良いと思いますよ。

結合にするか相関問い合わせにするかの選択は、コストとの見合いです。
DBMSとデータの状況によるものですから、必要に応じて検討すべきものだと思います。

Answer 2

私も勉強(1年ちょっとですが)したての頃はサブクエリを使う利点は何だろうと思ってました…。

1. JOIN テーブルA ON 結合条件 = 結合条件

2. JOIN (SELECT id,name FROM テーブルA) as テーブルA ON 結合条件 = 結合条件

上記2種類の結合方法がありますが、サブクエリを用いた場合だと参照してくるテーブルのカラム名を絞れます。
1.の場合だとテーブル内の全カラムを参照してきます。
カラムが数個ならそこまで問題ありませんが、業務ではカラムが数百個あるテーブルもあるためパフォーマンスに影響したりします。

個人的にサブクエリを用いるならWITH句がオススメです。
WITH句はSQL内で仮テーブルを作れるイメージです。
調べると色々と出てくるので参考にしてみてください。

Answer 3

SQL入門でサブクエリってけっこうつまずく人が多いです。きちんと勉強してください。参考

通常のJOINを使った時と比べて、サブクエリはパフォーマンスが悪いことが多いです。サブクエリを多用しなければいけないシステムはテーブル設計に問題がある場合が多いです。

Answer 4

あるテーブルに集計関数をかけた結果を使って、別のテーブルに対してWHEREを行いたい場合、「WHEREの条件内でサブクエリを書く」あるいは「集計関数をかけた結果をサブクエリとしてJOINを行う」の2択となります。

結合では実行できない一般的な形式のサブクエリー比較の例を次に示します。これは、column1 値がテーブル t2 内の最大値に等しいテーブル t1 内のすべての行を検索します。（MySQL 5.6リファレンスより）

SELECT * FROM t1
  WHERE column1 = (SELECT MAX(column2) FROM t2);

Comments

[maisumakun]

MAXであれば、いちおう「CROSS JOINして必要なものだけ取り出す」方法でも（非効率という問題を無視すれば）実行はできるかもしれませんが、AVERAGEとなればもっと困難です。

Answer 5

勉強中とのことなのでMySQLマニュアルを参考に
サブクエリの種類と置き換えが可能かどうかをよく勉強してみてください