「JOIN」と「SELECT列のサブクエリー」は、他のテーブル情報をキーを使って取得するという点においての効用としては似ています。
私の場合は、JOINで取れるものは、サブクエリーは基本使いませんが、その理由は「可読性」もありますが「性能的」にもサブクエリーは劣っているという印象もあります。
しかし、昨今のオプティマイザや処理の最適化が行われている、RDBではどのような結果になるのか気になったので、単純なサンプルを使って代表的なデータベースそれぞれの性能的な違いを検証してみました。
（PC環境やデータベースのバージョンやドライバやデータ件数、使用ツールの違いで結果が異なる場合があるので、あくまで参考として頂ければと思います。）
検証用テーブル
※リレーションは、[M_PRODUCT.PRODUCT_ID] 1 – 0..N [T_ORDER.PRODUCT_ID]
| 製品マスタ（M_PRODUCT） | 5,000件 |
| 受注テーブル（T_ORDER） | 100,000件 |
出力内容
製品マスタと受注テーブルの製品IDで一致するものを抽出し、受注情報に製品名を付加した一覧を取得する。
求めたい表イメージ（選択件数：100,000件）
①LEFT JOINで求めるてみる
| select o.PRODUCT_ID, p.PRODUCT_NAME, o.QUANTITY, o.ORDER_DATE from T_ORDER o left join M_PRODUCT p on o.PRODUCT_ID = p.PRODUCT_ID order by o.PRODUCT_ID, o.ORDER_DATE |
②サブクエリーで求めるてみる
| select o.PRODUCT_ID , (select p.PRODUCT_NAME from M_PRODUCT p where p.PRODUCT_ID = o.PRODUCT_ID) as PRODUCT_NAME , o.QUANTITY , o.ORDER_DATE from T_ORDER o order by o.PRODUCT_ID, o.ORDER_DATE |
③INNER JOINで求めるてみる
内部結合もRDB毎に違いがあるのか試してみました。
| select o.PRODUCT_ID, p.PRODUCT_NAME, o.QUANTITY, o.ORDER_DATE from T_ORDER o inner join M_PRODUCT p on o.PRODUCT_ID = p.PRODUCT_ID order by o.PRODUCT_ID, o.ORDER_DATE |
結果
取得したデータにに名称を含めて一覧表示するという、とてもオーソドックスなパターンですが、外部結合「JOINとサブクエリー」の違いと、内部結合「INNER JOIN」した結果も合わせて次の結果となりました。
単位：秒
| left join | サブクエリー | inner join | |
| pstgresql-9.6 | 1.388 | 2.252 | 1.391 |
| MySQL-15.1 | 0.570 | 0.580 | 2.697 |
| Oracle11g(XE) | 0.957 | 1.179 | 0.991 |
| SQLServer2016(Expless) | 0.980 | 0.954 | 0.992 |
| Access2016 | 1.484 | 2.652 | 1.498 |
この結果では、外部結合（LEFT JOIN）が優位だったのは、「postgresqlとAccessDB」で、「MySQL、Oracle、SQLServer」は、誤差程度でした。
全体的にJOIN系は手堅いと言えますが、「MySQL、Oracle、SQLServer」では、サブクエリーでもパフォーマンスによる大差は少なく、特にJOINが乱立しているようなSQLの場合など、これら3つのRDBでは状況次第でサブクエリーでかわすハードルは低いかもと感じました。
また、MySQLだけ内部結合「INNER JOIN」の結果が極端に性能が悪かったので、EXPLAINで調べてみましたが「rows Extra」に「Using temporary」が発生していました。（これが出るとオーバヘッドが大きくなるサインですが、詳しくは他のサイト等を参考にしてください。）
このリレーション条件では、「LEFT」「INNER」どちらで求めても出力結果は変わらないのですが、今回は理由は追及しませんが、MySQLでは、同様の条件で「INNER JOIN」を使用する場合は、注意が必要なのかも知れません。
一応、「INNER JOIN」でヒント句「IGNORE INDEX(PRODUCT_ID_IDX, PRODUCT_ID_DATE_IDX)」を入れても解消はしましたが。
この例では、MySQLの「INNER JOIN」が想定してなかった結果ともなり少しデリケートな印象と感じましたが、他のRDBもSQLにより思い込みで思わぬ落とし穴にハマることもあるので、改めて実行計画等でチェックしたり、最良のSQLを考察しておくことは改めて大切だと感じました。