SQLにおける結合条件の違いを把握しよう！ON句とWHERE句に指定する場合の違いとは？

SQLでテーブル結合を実施する際、条件式をON句とWHERE句それぞれに指定する方法が存在します。

今回はそれぞれに指定する意味合いの違いを、サンプルSQLを掲載しながらご紹介していきます。

SQLで条件の指定場所による結合処理の違いを把握しよう！

SQLでテーブル結合を行う際、条件指定を行う場所に「ON句」と「WHERE句」が利用されますが、それぞれに役割が異なります。

SQL文によっては最終的な表示結果が同じとなることも少なくありませんが、処理の違いについて認識しておくことは重要です。

ON句での条件指定

ON句での条件指定は、結合条件を指定する意味合いで利用されます。

基本的には、ON句で指定された条件でテーブル結合を行い、後述するWHERE句の条件で絞り込みが行われます。

ポテパンダの一言メモ

データベースのオプティマイザによっては、WHERE句の条件を先に処理するケースもあるようなので一概には定義出来ません。

WHERE句での条件指定

WHERE句での条件指定は、対象データの抽出を行うために利用されます。

つまり、テーブル結合されたデータの中から、条件に一致するデータを抽出するために利用します。

簡単に言ってしまうと、結合前のデータで条件による絞り込みを行うのが「ON句」、結合後のデータで条件による絞り込みを行うのが「WHERE句」に記述した場合となります。

SQLの結合条件をON句に記述する

では実際にサンプルSQLでデータの取得結果を確認していきましょう。

今回のサンプルデータとして「office」テーブルと「region」テーブルを下記のデータで用意しています。

officeテーブル

+------+--------------------+-----------+
| id   | office_name        | region_id |
+------+--------------------+-----------+
|    1 | 新宿支店            |         3 |
|    2 | 渋谷支店            |         3 |
|    3 | 函館支店            |         1 |
|    4 | 梅田支店            |         5 |
|    5 | 那覇支店            |         9 |
|    6 | ソウル支店          |        99 |
|    7 | バンコク支店        |        99 |
+------+--------------------+-----------+

regionテーブル

+------+-------------+
| id   | region_name |
+------+-------------+
|    1 | 北海道       |
|    2 | 東北        |
|    3 | 関東        |
|    4 | 中部        |
|    5 | 近畿        |
|    6 | 中国        |
|    7 | 四国        |
|    8 | 九州        |
|    9 | 沖縄        |
+------+-------------+

内部結合

まずは内部結合でON句に条件を加えて取得してみましょう。

サンプルでは、officeテーブルの「region_id」とregionテーブルの「id」が一致するデータの中から、「関東」に該当するデータを抽出しています。

SELECT * 
FROM office 
INNER JOIN region 
ON region.id = office.region_id 
AND office.region_id = 3;

実行した結果が下記の通りです。

+------+--------------+-----------+------+-------------+
| id   | office_name  | region_id | id   | region_name |
+------+--------------+-----------+------+-------------+
|    1 | 新宿支店      |         3 |    3 | 関東         |
|    2 | 渋谷支店      |         3 |    3 | 関東         |
+------+--------------+-----------+------+-------------+

外部結合

次に外部結合で同じようにON句に条件を加えてみます。

SELECT * 
FROM office 
LEFT JOIN region 
ON region.id = office.region_id 
AND office.region_id = 3;

実行した結果が下記の通りです。

+------+--------------------+-----------+------+-------------+
| id   | office_name        | region_id | id   | region_name |
+------+--------------------+-----------+------+-------------+
|    1 | 新宿支店            |         3 |    3 | 関東         |
|    2 | 渋谷支店            |         3 |    3 | 関東         |
|    3 | 函館支店            |         1 | NULL | NULL        |
|    4 | 梅田支店            |         5 | NULL | NULL        |
|    5 | 那覇支店            |         9 | NULL | NULL        |
|    6 | ソウル支店          |        99 | NULL | NULL        |
|    7 | バンコク支店        |        99 | NULL | NULL        |
+------+--------------------+-----------+------+-------------+

予想した結果と異なった方もいるのではないでしょうか。

ON句に条件を指定した場合、上述したように結合前に条件による絞り込みが行われます。

外部結合の場合、軸となるテーブルのデータは全て取得する特徴があるため、今回の場合、関東以外のオフィス情報もデータとして取得されています。

SQLの結合で抽出条件をWHERE句に記述する

では次にWHERE句に条件を指定した場合のサンプルで確認していきましょう。

サンプルデータはON句と同じものを利用します。

内部結合

ON句に指定した条件をそのままWHERE句の条件として記述してみます。

SELECT * 
FROM office 
INNER JOIN region 
ON region.id = office.region_id 
WHERE office.region_id = 3; 

実行した結果が下記の通りです。

+------+--------------+-----------+------+-------------+
| id   | office_name  | region_id | id   | region_name |
+------+--------------+-----------+------+-------------+
|    1 | 新宿支店      |         3 |    3 | 関東         |
|    2 | 渋谷支店      |         3 |    3 | 関東         |
+------+--------------+-----------+------+-------------+

ON句と同じ結果となりましたね。

内部結合の場合、双方のテーブルが条件を満たすデータのみを取得するため、結合前の条件指定(ON句)でも結合後の条件指定(WHERE句)でも同じ結果が生まれます。

ポテパンダの一言メモ

最終的な取得結果だけを見ると内部結合の場合、ON句でもWHERE句でもどちらに条件を記載しても良いと言えます。
しかし、状況により可読性やパフォーマンスを意識しながら最適な記述方法を選択するべきでしょう。

外部結合

次に外部結合についても、ON句のサンプルをそのままWHERE句に記述して実行してみましょう。

SELECT * 
FROM office 
LEFT JOIN region 
ON region.id = office.region_id 
WHERE office.region_id = 3;

実行した結果が下記の通りです。

+------+--------------+-----------+------+-------------+
| id   | office_name  | region_id | id   | region_name |
+------+--------------+-----------+------+-------------+
|    1 | 新宿支店      |         3 |    3 | 関東         |
|    2 | 渋谷支店      |         3 |    3 | 関東         |
+------+--------------+-----------+------+-------------+

ON句に指定した場合と取得結果が異なっています。

WHERE句に条件指定をした場合、テーブル結合後のデータに対して抽出条件が適用されることにより、軸となるテーブルであっても、条件に該当しないテーブルのデータは削除されるという結果になります。

ポテパンダの一言メモ

ここで疑問として、WHERE句に条件指定した場合、内部結合も外部結合も同じ結果が取得されていますよね。
この場合一般的には、内部結合を選択する方がパフォーマンスが高いと言われています。
しかしオプティマイザなど環境により最善の方法は変わりますので、自分自身でSQL実行計画などを確認し、最適な記述方法を選択することが大切です。

さいごに：SQLの結合で優先される条件はオプティマイザにより異なる

本記事では、SQLでテーブル結合時の条件指定について、ON句とWHERE句に記述した際の処理の違いをサンプルを交えながらご紹介してきました。

最も抑えておきたいポイントは、外部結合の場合、ON句とWHERE句のどちらに条件指定するのかで、取得されるデータが変わってしまうということです。

外部結合での条件指定による取得結果の違いをしっかりと把握した上で、それぞれの条件指定時のパフォーマンスの違いについても確認出来るように取り組んでいきましょう。