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句に入れておいた喜びを最も感じるケース。

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