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テーブル以上の複数テーブルの外部結合が可能
