SQLのouter joinについてまとめてます。
outer joinは外部結合 結合条件次第でパフォーマンス劣化の可能性あり
外部結合とは、left側のテーブルに対応するデータがない場合もNULL値としてデータを取得する結合方法です。
以下のSQLは、employees（社員）テーブルとdept_emp（部署・社員紐付けテーブル）をemp_no（社員番号）でouter joinし、さらにdepartments（部署）テーブルとdept_no（部署番号）でouter joinするSQLです。
//データが完全なので、left joinと同じ結果 SELECT first_name, last_name, dept_name FROM employees left outer join dept_emp on employees.emp_no = dept_emp.emp_no left outer join departments on departments.dept_no = dept_emp.dept_no limit 20; +------------+-------------+--------------------+ | first_name | last_name | dept_name | +------------+-------------+--------------------+ | Georgi | Facello | Development | | Bezalel | Simmel | Sales | | Parto | Bamford | Production | | Chirstian | Koblick | Production | | Kyoichi | Maliniak | Human Resources | | Anneke | Preusig | Development | | Tzvetan | Zielinski | Research | | Saniya | Kalloufi | Development | | Sumant | Peac | Quality Management | | Duangkaew | Piveteau | Production | | Duangkaew | Piveteau | Quality Management | | Mary | Sluis | Customer Service | | Patricio | Bridgland | Development | | Eberhardt | Terkki | Human Resources | | Berni | Genin | Development | | Guoxiang | Nooteboom | Research | | Kazuhito | Cappelletti | Sales | | Cristinel | Bouloucos | Marketing | | Kazuhide | Peha | Production | | Kazuhide | Peha | Development | +------------+-------------+--------------------+ 20 rows in set (0.00 sec)
存在しない部署番号を持つ社員データを追加して、取得できるかどうか試してみましょう。
以下の通り、対応するdepartments（部署）テーブルのレコードがなくてもデータを取得できました。
// employeesテーブルのみ新規データ挿入 dept_empにデータが存在しない insert employees (emp_no, birth_date, first_name, last_name, gender, hire_date) values ( 500000,'1970-01-04','TARO', 'Yamada', 'M', '2001-04-07' ); // 部署名（dept_name）の紐付けができないのでNULL値を取得 SELECT first_name, last_name, dept_name FROM employees left outer join dept_emp on employees.emp_no = dept_emp.emp_no left outer join departments on departments.dept_no = dept_emp.dept_no WHERE employees.emp_no=500000; +------------+-----------+-----------+ | first_name | last_name | dept_name | +------------+-----------+-----------+ | TARO | Yamada | NULL | +------------+-----------+-----------+ 1 row in set (0.01 sec)
【関連記事】
▶SQL 外部結合サンプルコード 3テーブル結合とパフォーマンス比較
outer joinは、left側のデータを問答無用で全件取得するため、ケースによっては遅いクエリになりやすいので注意が必要です。
例えば、where句でインデックスのついていないカラムを指定した場合は、実行速度が10倍以上違ってきます。
// where句の条件にプライマリキー（emp_no）を指定した場合 mysql> SELECT first_name, last_name, dept_name FROM employees left outer join dept_emp on employees.emp_no = dept_emp.emp_no left outer join departments on departments.dept_no = dept_emp.dept_no WHERE employees.emp_no=500000; +------------+-----------+-----------+ | first_name | last_name | dept_name | +------------+-----------+-----------+ | TARO | Yamada | NULL | +------------+-----------+-----------+ 1 row in set (0.00 sec) where句の条件にインデックスのないカラム（first_name）を指定した場合 mysqlSELECT first_name, last_name, dept_name FROM employees left outer join dept_emp on employees.emp_no = dept_emp.emp_no left outer join departments on departments.dept_no = dept_emp.dept_no WHERE employees.first_name='TARO'; +------------+-----------+-----------+ | first_name | last_name | dept_name | +------------+-----------+-----------+ | TARO | Yamada | NULL | +------------+-----------+-----------+ 1 row in set (0.14 sec)
1つ目のSQLの実行時間は0.00sec、2つ目は0.14secで速度は14倍違います。explainを実行して実行計画を確認すると、後者はtype=ALL、key=NULLのテーブルフルスキャンを実行していることがわかります。
// where条件にプライマリキー(emp_no)を使っている場合
mysql> explain SELECT
-> first_name,
-> last_name,
-> dept_name
-> FROM
-> employees
-> left outer join dept_emp on employees.emp_no = dept_emp.emp_no
-> left outer join departments on departments.dept_no = dept_emp.dept_no
-> WHERE
-> employees.emp_no=500000;
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+------+--------
--+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtere
d | Extra |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+------+--------
--+-------------+
| 1 | SIMPLE | employees | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.0
0 | NULL |
| 1 | SIMPLE | dept_emp | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.0
0 | Using index |
| 1 | SIMPLE | departments | NULL | eq_ref | PRIMARY | PRIMARY | 16 | employees.dept_emp.dept_no | 1 | 100.0
0 | NULL |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+------+--------
--+-------------+
// where条件にインデックスのないカラムを指定している場合
mysql> explain SELECT
-> first_name,
-> last_name,
-> dept_name
-> FROM
-> employees
-> left outer join dept_emp on employees.emp_no = dept_emp.emp_no
-> left outer join departments on departments.dept_no = dept_emp.dept_no
-> WHERE
-> first_name='TARO';
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+------
----+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filte
red | Extra |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+------
----+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299247 | 10
.00 | Using where |
| 1 | SIMPLE | dept_emp | NULL | ref | PRIMARY | PRIMARY | 4 | employees.employees.emp_no | 1 | 100
.00 | Using index |
| 1 | SIMPLE | departments | NULL | eq_ref | PRIMARY | PRIMARY | 16 | employees.dept_emp.dept_no | 1 | 100
.00 | NULL |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+------
----+-------------+
クエリの速度を改善するには、where句で使用しているカラムにインデックス付与するなどの対策が必要です。
まとめ
ポテパンダの一言メモ
- outer joinは、left側のテーブルを全件取得するjoin。値がない場合はNULLとなる。
- whereの条件次第で大きく速度が落ちることがある。
- 件数が多いテーブルの場合は、explainで実行計画を確認しindexを付与するなどの対策をする。
