Edited at

WHERE句のサブクエリは大抵の場合テーブルJOINで代替できる

WHERE句にあるサブクエリ時々見かけるんですが、対象のデータ件数が増えるにつれ速度低下のボトルネックになったり、後から追加された仕様に対応するため複雑な相関サブクエリが使われていて一見してなにをしているSQLなのかわからなくなったりしていることが経験上多い気がしています。

できる限りサブクエリをJOIN句に入れておくことで世の中が少し平和になったりしないかなと思いました。


対象条件の抽出

SELECT hoge 

FROM tblA
WHERE id IN (SELECT id FROM tblB WHERE piyo=1)

SELECT tblA.hoge

FROM tblA
INNER JOIN tblB USING( id )
WHERE tblB.piyo=1


重複した行をとりたくない場合

SELECT tblA.hoge

FROM tblA
WHERE id IN (SELECT DISTINCT a_id FROM tblB WHERE piyo=1)

SELECT (DISTINCT tblA.a_id), tblA.hoge 

FROM table
INNER JOIN tblB ON ( tblA.a_id = tblB.a_id)
WHERE tblB.piyo=1

※tblAのidに対するDISTINCTでSELECTの結果からは重複は除外されます。

※DISTINCTを後まわしにすることで性能面で問題が出るのではという心配もありますが、tblAとtblBのデータ件数や取得するフィールド数にもよりますが意外と重くはなりません。idに対してだけDISTINCTをかけるのがポイントだと思われます。


INNER JOINではフォローできないケースはOUTER JOINでなんとかなる

SELECT tblA.hoge

FROM tblA
WHERE id IN (SELECT DISTINCT id from tblB where status=1)
OR status=2

SELECT DISTINCT tblA.*

FROM tblA
LEFT OUTER JOIN tblB ON (tblA.x_id=tblB.x_id)
WHERE tblB.status=1
OR tblA.status=2

もしくはこれでも

SELECT DISTINCT tblA.*

FROM tblA
LEFT OUTER JOIN tblB ON (tblA.x_id=tblB.x_id AND status=1)
WHERE tblB.id IS NOT NULL
OR tblA.status=2


サブクエリで集計した結果からの条件指定

SELECT hoge, 

piyo,
fuga,
(SELECT AVG(piyo) as avg_piyo
FROM tbl as tbl2
WHERE tbl1.fuga = tbl2.fuga)
FROM tbl as tbl1
WHERE tbl.piyo < (SELECT AVG(piyo) as avg_piyo
FROM tbl as tbl2
WHERE tbl1.fuga = tbl2.fuga);

SELECT tbl1.hoge, 

tbl1.piyo,
tbl1.fuga,
tbl2.avgPiyo
FROM tbl
INNER JOIN (
SELECT fuga, AVG(piyo) avgPiyo FROM tbl GROUP BY fuga
) avg USING ( fuga )
WHERE piyo >= avg.avgPiyo

JOIN句に入れておいた喜びを最も感じるケース。

以上、ご参考になりましたら嬉しいです。