Zenn
🦁

WHERE句とON句の適切な使い方

2023/12/10に公開
MySQL
SQL
tech

はじめに

データベースを利用する際、テーブル同士を内部結合して条件を指定することがあります。
条件指定には、WHERE句とON句のどちらかを利用することが可能です。
この記事内ではMySQLを利用し、例として製品在庫管理システムを想定します。

MySQLバージョン: 5.7.24

CREATE TABLE supplier (
  supplier_id int(11) NOT NULL,
  name varchar(30) NOT NULL,
  address varchar(100) NOT NULL,
  PRIMARY KEY (supplier_id)
);

CREATE TABLE product (
  product_id int(11) NOT NULL,
  supplier_id int(11) NOT NULL,
  name varchar(30) NOT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT supplier_idfk_1 FOREIGN KEY (supplier_id) REFERENCES supplier (supplier_id) ON DELETE CASCADE
);

内部結合条件の指定

特定の供給者から提供されるすべての製品をリストアップすることになりました。
次の2つのクエリで取得可能ですが、内部結合条件の指定の記述に違いがあります。

WHERE句で条件指定する場合は、次のように記述します。

SELECT s.supplier_id, s.name, p.name
FROM supplier s
JOIN product p ON s.supplier_id = p.supplier_id
WHERE s.name = 'Supplier A';

ON句で条件指定する場合は、次のように記述します。

SELECT s.supplier_id, s.name, p.name
FROM supplier s
JOIN product p ON s.supplier_id = p.supplier_id
AND s.name = 'Supplier A';

結果セット例

+-------------+------------+-----------+
| supplier_id | name       | name      |
+-------------+------------+-----------+
|           1 | Supplier A | Product 1 |
|           1 | Supplier A | Product 2 |
|           1 | Supplier A | Product 3 |
+-------------+------------+-----------+

内部結合におけるON句とWHERE句の機能的な違い

先ほどの2つのSQLは、実行順序にも違いがあります。

WHERE句で条件した場合

  1. supplierテーブルとproductテーブルを結合
  2. s.name = 'Supplier A'で絞り込み
  3. 結果セットを取得

WHERE句で条件指定する場合は、JOIN処理が重い場合、効率的ではない可能性があります。
JOIN処理が重たいとは、結合するテーブルの行数が多いなどが要因になります。

ON句で条件した場合

  1. s.name = 'Supplier A'を満たす行に対して、productテーブルを結合
  2. 結果セットを取得

ON句で条件した場合、結合がどのように行われるかを定めます。
この場合、s.name = 'Supplier A'という条件は結合の基準として機能し、supplierテーブルとproductテーブルの結合時にこの条件が考慮されます。
その結果、該当する条件に合致する行のみが結合され、結果セットとして取得されます。

外部結合の動作の違い

先ほどは内部結合を使用したため、結果セットは変わりませんでした。
しかし、外部結合を使用する場合は、結果セットが変わる可能性があります。

次のように複数のsupplierが存在するとします。

+-------------+------------+---------------+
| supplier_id | name       | address       |
+-------------+------------+---------------+
|           1 | Supplier A | 1234 A Street |
|           2 | Supplier B | 5678 B Avenue |
|           3 | Supplier C | 9101 C Road   |
+-------------+------------+---------------+

WHERE句で条件指定し、外部結合する場合

SELECT s.supplier_id, s.name, p.name
FROM supplier s
LEFT OUTER JOIN product p ON s.supplier_id = p.supplier_id
WHERE s.name = 'Supplier A';

次の結果セットとなり、内部結合の結果セットとは変わらない結果です。

+-------------+------------+-----------+
| supplier_id | name       | name      |
+-------------+------------+-----------+
|           1 | Supplier A | Product 1 |
|           1 | Supplier A | Product 2 |
|           1 | Supplier A | Product 3 |
+-------------+------------+-----------+

ON句で条件指定し、外部結合する場合

SELECT s.supplier_id, s.name, p.name
FROM supplier s
LEFT OUTER JOIN product p ON s.supplier_id = p.supplier_id
AND s.name = 'Supplier A';

次のように、WHERE句で条件指定した場合の結果セットとは異なる結果になりました。

+-------------+------------+-----------+
| supplier_id | name       | name      |
+-------------+------------+-----------+
|           1 | Supplier A | Product 1 |
|           1 | Supplier A | Product 2 |
|           1 | Supplier A | Product 3 |
|           2 | Supplier B | NULL      |
|           3 | Supplier C | NULL      |
+-------------+------------+-----------+

こちらはproductテーブルへの結合条件を満たさないため、productテーブルの列にNULLがセットされた状態で取得されます。
結果セットの通り、結合条件を満たしてない行も結果セットに含まれるようになっています。

まとめ

この記事では、ON句とWHERE句の違いと、それらが結合クエリにどのように影響するかを見てきました。

  • WHERE句での条件は、結合されたテーブル全体に対する「絞り込む条件」として考えることができます。
  • ON句で指定した条件は、結合の「方法」を定義すると考えることができます。

私としては、「特定の供給者から提供されるすべての製品をリストアップする」場合、ON句でテーブル間の結合条件を設定し、WHERE句で特定の供給者を指定する方法が、適している気がしました。

GitHubで編集を提案

Discussion