【データベース】「SQLの実践」簡単速習‼【⑧テーブル結合/MySQL】

こんにちはヤク学長です。
データサイエンティスト兼ファーマシストで、アルゴリズムやBI開発を行っています。

本記事の目的は、「SQLの基本操作を知る」ことを目的としています。

【本記事のもくじ】

まず、「SQL」に真剣に取り組むための概要を解説します。
下記の方法で、簡単に概要を抑えることができます。

• １２．テーブル結合

それでは、上から順番に見ていきます。
なお、本上記の方法を順番に抑えれば成果が出ます。

記事の内容は「転載 & 引用OK」問題ありません。

１２. テーブル結合

テーブル結合について

テーブル結合は、複数のテーブルのデータを組み合わせて1つのテーブルにすることで、関連するデータを取得するために使用される方法です。テーブル結合には、いくつかの種類がありますが、最も一般的な結合方法はINNER JOINです。

INNER JOINは、2つのテーブルの共通の列を基準に、それらを結合する方法です。つまり、INNER JOINを使用すると、2つのテーブルの共通の列に一致する行だけが抽出され、それらが1つのテーブルにまとめられます。

例えば、顧客情報を管理するcustomersテーブルと注文情報を管理するordersテーブルがある場合、2つのテーブルをINNER JOINを使用して結合することができます。

以下に、INNER JOINを使用した2つのテーブルの結合の例を示します。

SELECT * FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id;

このクエリは、customersテーブルとordersテーブルをcustomer_id列で結合します。つまり、customersテーブルとordersテーブルのcustomer_id列に一致する行のみが抽出され、それらが1つのテーブルにまとめられます。

テーブル結合には、INNER JOIN以外にも、LEFT JOIN、RIGHT JOIN、FULL OUTER JOINなどがあります。それぞれの結合方法には、結合する2つのテーブルにどのような行が含まれるかに違いがあります。

SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id;

SELECT * FROM customers JOIN orders ON customers.customer_id = orders.customer_id;

SELECT * FROM students JOIN student_classes ON students.student_id = student_classes.student_id JOIN classes ON student_classes.class_id = classes.class_id;

JOINは、複数のテーブルからデータを結合するために使用されるSQLのキーワードです。JOINを使用することで、異なるテーブルのデータを組み合わせて1つのテーブルとして表示することができます。

JOINにはいくつかの種類があります。最も基本的なJOINはINNER JOINで、2つのテーブルの共通の列を使用して、2つのテーブルを結合します。LEFT JOINは、1つのテーブルの全てのレコードを取得し、もう1つのテーブルから一致するレコードを取得します。RIGHT JOINは、LEFT JOINの逆で、もう1つのテーブルの全てのレコードを取得し、1つのテーブルから一致するレコードを取得します。FULL OUTER JOINは、両方のテーブルの全てのレコードを取得します。

JOINを使用する場合は、結合に使用する列を指定する必要があります。これらの列には、同じ名前を持つ列が含まれている場合がありますが、異なる名前の列を結合することもできます。また、複数の列を使用して結合することもできます。

以下に、INNER JOINの例を示します。

SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;

このクエリは、ordersテーブルとcustomersテーブルをcustomer_id列でINNER JOINしています。つまり、ordersテーブルとcustomersテーブルのcustomer_id列に一致する行のみが抽出され、それらが1つのテーブルにまとめられます。INNER JOINを使用すると、2つのテーブルの共通のレコードのみが取得されるため、比較的シンプルなクエリを使用して、必要な情報だけを抽出することができます。

SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

外部結合：LEFT JOIN, RIGHT JOIN

外部結合は、2つのテーブルを結合する際に、共通のデータだけでなく、片方のテーブルにしか存在しないデータを含めることができるようにするためのSQLのキーワードです。LEFT JOINとRIGHT JOINは、それぞれ左側のテーブルと右側のテーブルのどちらかにしか存在しないデータを含めるために使用されます。

LEFT JOINは、左側のテーブルに存在する全てのレコードを取得し、右側のテーブルからは、左側のテーブルの該当するレコードが存在しない場合にはNULL値を取得します。つまり、左側のテーブルを基準にして、右側のテーブルと結合するということになります。

以下に、LEFT JOINの例を示します。

SELECT * FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

このクエリは、table1テーブルを基準にして、table2テーブルをLEFT JOINしています。つまり、table1テーブルに存在する全てのレコードを取得し、table2テーブルからは、table1テーブルの該当するレコードが存在しない場合にはNULL値を取得します。

RIGHT JOINは、LEFT JOINの逆で、右側のテーブルに存在する全てのレコードを取得し、左側のテーブルからは、右側のテーブルの該当するレコードが存在しない場合にはNULL値を取得します。

以下に、RIGHT JOINの例を示します。

SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

このクエリは、table2テーブルを基準にして、table1テーブルをRIGHT JOINしています。つまり、table2テーブルに存在する全てのレコードを取得し、table1テーブルからは、table2テーブルの該当するレコードが存在しない場合にはNULL値を取得します。

外部結合は、共通の列を持たない2つのテーブルの間で関連性を見つけたい場合に特に有用です。また、外部結合においては、結合する方のテーブルにNULL値が含まれる可能性があるため、取得したデータの解釈には注意が必要です。

FULL OUTER JOIN

FULL OUTER JOINは、LEFT JOINとRIGHT JOINの両方を実行することで、2つのテーブルのどちらかにしか存在しないデータも含めて、2つのテーブルを結合するSQLのキーワードです。つまり、LEFT JOINとRIGHT JOINの結果を結合したものになります。

FULL OUTER JOINは、MySQLではサポートされていないため、LEFT JOINとRIGHT JOINを組み合わせることで代用する必要があります。

以下に、FULL OUTER JOINの代替となるクエリを示します。

SELECT * FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name UNION SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

このクエリは、LEFT JOINとRIGHT JOINをUNIONで結合したものになります。つまり、table1テーブルとtable2テーブルの両方に存在するデータだけでなく、table1テーブルにしか存在しないデータとtable2テーブルにしか存在しないデータも含めて、2つのテーブルを結合しています。

FULL OUTER JOINは、LEFT JOINとRIGHT JOINを組み合わせることで代用できますが、クエリが複雑になるため、2つのテーブルの結合方法を慎重に検討する必要があります。また、FULL OUTER JOINにおいても、結合する方のテーブルにNULL値が含まれる可能性があるため、取得したデータの解釈には注意が必要です。

自己結合（SELF JOIN）

自己結合（SELF JOIN）は、同じテーブル内で行われる結合のことを指します。つまり、自分自身との結合を行うことができます。自己結合を使用することで、1つのテーブル内で複数の関連性を見つけたり、階層的なデータの取得など、さまざまな目的で使用することができます。

自己結合を行う際には、別名を使用することで区別する必要があります。別名はASキーワードを使用して指定します。

以下に、自己結合の例を示します。

例として、顧客テーブルがあり、そのテーブルの中に、顧客名とマネージャー名を格納する列があるとします。この場合、自己結合を使用して、同じマネージャーを持つ顧客を検索することができます。

SELECT a.顧客名, b.顧客名 as マネージャー名 FROM 顧客 a, 顧客 b WHERE a.マネージャー名 = b.顧客名;

このクエリでは、顧客テーブルをaとbの2つの別名で参照しています。WHERE句で、a.マネージャー名とb.顧客名が一致するレコードを取得することで、同じマネージャーを持つ顧客を検索しています。

自己結合を使用する際には、クエリが複雑になりやすいため、注意が必要です。また、自己結合を使用する場合には、テーブル内に複数のレコードが存在することを前提としているため、1つしかない場合には正しい結果が得られない場合があります。

WITH
ビュー名1 AS (サブクエリ1),
ビュー名2 AS (サブクエリ2),
...
SELECT ...
FROM テーブル1
INNER JOIN ビュー名1 ON ...
LEFT JOIN ビュー名2 ON ...
WHERE ...

WITH
売上合計 AS (
SELECT 上司の従業員ID, SUM(売上) AS 合計
FROM 従業員
GROUP BY 上司の従業員ID
)
SELECT 従業員名, 合計
FROM 従業員
LEFT JOIN 売上合計
ON 従業員.ID = 売上合計.上司の従業員ID
WHERE 合計 >= 100000;