SQLの外部結合はLEFT JOINで記述可能。処理のボトルネック調査には実行計画を確認する

SQLの外部結合についてまとめています。

SQLの外部結合は軸テーブルのデータを全件取得する結合の一種

SQLの外部結合は、テーブル結合の一種。軸となるテーブルのデータに対応するデータが存在しなくても、すべてのデータを取得します。

具体的な外部結合の記述の例は以下の通りです。LEFT JOINあるいは、OUTER JOIN、RIGHT JOINで記述を行います。なお、RIGHT JOINはLEFT JOINで置き換え可能なため、実質外部結合とはLEFT JOINまたはOUTER JOINで記述可能です。

SELECT (テーブル名.)カラム名(, ....)
FROM テーブル1
LEFT (OUTER) JOIN テーブル2
ON テーブル2.カラム名 = テーブル1.カラム名
(LEFT JOIN テーブル3
ON テーブル3.カラム名 = テーブル1(2).カラム名)

【関連記事】
▶SQLの内部結合と外部結合の違いを把握しよう！【サンプルコードで解説】

以下は、外部結合の実行例です。employees（社員）テーブルと、salaries（年収）テーブルを外部結合して、社員の姓名（first_name、last_name）と年収（salary）を抽出しています。WHERE句の条件は、最新の年収額を抽出するためのものです。

SELECT 
	employees.first_name, 
	employees.last_name, 
	salaries.salary 
FROM 
	employees 
	left join salaries on employees.emp_no = salaries.emp_no 
WHERE 
	salaries.to_date = "9999-01-01";
 
+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Georgi     | Facello   |  88958 |
| Bezalel    | Simmel    |  72527 |
| Parto      | Bamford   |  43311 |
| Chirstian  | Koblick   |  74057 |
| Kyoichi    | Maliniak  |  94692 |
| Anneke     | Preusig   |  59755 |
| Tzvetan    | Zielinski |  88070 |
| Sumant     | Peac      |  94409 |
| Duangkaew  | Piveteau  |  80324 |
| Patricio   | Bridgland |  54423 |
 :
 :

軸となるemployeesに対応したsalaryのデータがすべて存在する場合、外部結合の結果と内部結合の結果は同じになります。対応するsalaryの結果がない場合、salaryの項目はNULLになります。

【関連記事】
▶SQL Joinサンプル集　Joinで遅いSQLの原因を調べる方法 

外部結合のクエリの処理に時間がかかる場合、いずれかの結合部分がボトルネックになっている場合があります。パフォーマンス低下の原因を探るには実行計画を確認しましょう。

以下は、explain（MySQL）で、クエリの実行結果を確認した例です。salariesテーブルにて、テーブルフルスキャン（type=ALL）が発生しています。

mysql> explain SELECT employees.first_name, employees.last_name, salaries.salary FROM employees left join salaries on employees.emp_no = salaries.emp_no WHERE salaries.to_date = "9999-01-01";
+----+-------------+-----------+------------+--------+---------------+---------+---------+---------------------------+---------+----------+-------------+
| id | select_type | table     | partitions | type   | possible_keys | key     | key_len | ref                       | rows    | filtered | Extra       |
+----+-------------+-----------+------------+--------+---------------+---------+---------+---------------------------+---------+----------+-------------+
|  1 | SIMPLE      | salaries  | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                      | 2838426 |    10.00 | Using where |
|  1 | SIMPLE      | employees | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | employees.salaries.emp_no |       1 |   100.00 | NULL        |
+----+-------------+-----------+------------+--------+---------------+---------+---------+---------------------------+---------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

3テーブルの外部結合の例

SQLで2つ以上の外部結合を記述することが可能です。以下は、employees（社員）テーブルと、dept_emp（部署・社員紐付け）テーブル、departments（部署）テーブルの３つを外部結合しています。

SELECT employees.emp_no,
       dept_name
FROM   employees
       LEFT JOIN dept_emp
              ON dept_emp.emp_no = employees.emp_no
       LEFT JOIN departments
              ON departments.dept_no = dept_emp.dept_no
LIMIT  10;

実行結果はこうなります。

+--------+--------------------+
| emp_no | dept_name          |
+--------+--------------------+
|  10001 | Development        |
|  10002 | Sales              |
|  10003 | Production         |
|  10004 | Production         |
|  10005 | Human Resources    |
|  10006 | Development        |
|  10007 | Research           |
|  10008 | Development        |
|  10009 | Quality Management |
|  10010 | Production         |
+--------+--------------------+
10 rows in set (0.05 sec)

【関連記事】
▶SQL 外部結合サンプルコード 3テーブル結合とパフォーマンス比較

まとめ

ポテパンダの一言メモ

• 外部結合は、結合の一種で、軸となるテーブルに対応するデータがなくても全件を取得する
• 外部結合は、outer joinまたはleft joinで記述可能
• 2テーブル以上の複数テーブルの外部結合が可能