SQLで結合を使用する方法

導入

多くのデータベース設計では、特定のデータポイント間の関係性に基づいて情報を複数のテーブルに分割します。このような場合でも、複数のテーブルから一度に情報を取得したい場合があるでしょう。.

構造化照会言語 (SQL) 操作で複数のテーブルのデータにアクセスする一般的な方法は、JOIN 句を使ってテーブルを結合することです。リレーショナル代数の結合操作に基づいて、JOIN 句は各テーブル内の互いに関連する行を一致させることで、別々のテーブルを結合します。通常、この関係は、あるテーブルの外部キーと、その外部キーが参照する別のテーブルの主キーなど、値を共有する2つの列（各テーブルから1つ）に基づいています。.

このガイドでは、JOIN句を含む様々なSQLクエリの構築方法を説明します。また、JOIN句の種類、複数のテーブルからデータを結合する方法、そしてJOIN操作の記述を簡素化するための列のエイリアス設定方法についても説明します。.

前提条件

このガイドに従うには、SQLを使用するリレーショナルデータベース管理システム（RDBMS）を搭載したコンピューターが必要です。このガイドの手順と例は、以下の環境で検証されています。

Ubuntu 20.04 を実行しているサーバー、管理者権限を持つ非ルートユーザー、および UFW で構成されたファイアウォール
MySQL はサーバー上にインストールされ、保護されています。
また、JOIN操作の練習に使用できるサンプルデータがロードされたテーブルがいくつかあるデータベースも必要です。MySQLサーバーへの接続方法と、このガイドの例で使用するテストデータベースの作成方法については、以下の「MySQLへの接続とサンプルデータベースの設定」セクションをご覧ください。.

MySQLへの接続とサンプルデータベースの設定

SQL データベースシステムがリモートサーバー上で実行されている場合は、ローカルマシンからサーバーに SSH で接続します。

ssh sammy@your_server_ip

次に、MySQL サーバープロンプトを開き、Sami を MySQL ユーザー名に置き換えます。

mysql -u sammy -p

joinsDB というデータベースを作成します。

CREATE DATABASE joinsDB;

データベースが正常に作成されると、次の出力が表示されます。

Output
Query OK, 1 row affected (0.01 sec)

joinsDB データベースを選択するには、次の USE ステートメントを実行します。

USE joinsDB;

Output
Database changed

joinsDB を選択したら、その中にいくつかのテーブルを作成します。このガイドの例では、工場を経営していて、生産ライン、営業チームの従業員、そして会社の売上に関する情報を SQL データベースで追跡することにしたとします。まず 3 つのテーブルを作成します。最初のテーブルには製品に関する情報を保存します。この最初のテーブルには 3 つの列が必要です。

商品ID: intデータ型で表される各商品の識別番号。この列はテーブルの主キーとして機能し、各値は対応する行の一意の識別子として機能します。主キーの各値は一意である必要があるため、この列にはUNIQUE制約も適用されています。
productName: 各製品の名前は、最大 20 文字の varchar データ型を使用して表現されます。.
価格: 各商品の価格。小数点以下の数値で表されます。この列の値は最大4桁に制限され、そのうち2桁は小数点以下となります。したがって、この列で許容される値の範囲は-99.99～99.99です。

次の 3 つの列を持つ、products という名前のテーブルを作成します。

CREATE TABLE products (
productID int UNIQUE,
productName varchar(20),
price decimal (4,2),
PRIMARY KEY (productID)
);

2つ目のテーブルには、会社の営業チームの従業員に関する情報が格納されています。このテーブルにも、以下の3つの列が必要であると判断しました。

empID: productID列と同様に、この列には営業チームの各従業員の一意のID番号がint型で格納されます。そのため、この列にはUNIQUE制約が設定され、teamテーブルの主キーとして機能します。.
empName: 最大 20 文字の varchar データ型を使用して表される各営業担当者の名前。.
productSpecialty: 営業チームの各メンバーには、専門分野として製品が割り当てられています。各メンバーは会社が製造するあらゆる製品を販売できますが、全体的な焦点は自分が専門とする製品に置かれます。これをテーブルで表現するために、各従業員が専門とする製品のproductID値を保持する列を作成します。.

productSpecialty列に有効な製品ID番号を表す値のみが含まれるようにするために、ProductsテーブルのproductID列を参照する列に外部キー制約を適用することにしました。外部キー制約は、適用先の列の値が参照先の列にも必ず存在することを条件とすることで、2つのテーブル間の関係を表現する方法です。次のCREATE TABLEステートメントでは、FOREIGN KEY制約により、teamテーブルのproductSpecialty列に追加される値は、ProductsテーブルのproductID列にも必ず存在することが条件となります。.

次の 3 つの列を持つ Team というテーブルを作成します。

CREATE TABLE team (
empID int UNIQUE,
empName varchar(20),
productSpecialty int,
PRIMARY KEY (empID),
FOREIGN KEY (productSpecialty) REFERENCES products (productID)
);

最後に作成するテーブルには、会社の売上記録が格納されます。このテーブルには4つの列があります。

saleID: productID列およびempID列と同様に、この列には各売上の一意の識別番号がint型で格納されます。また、この列にはUNIQUE制約が設定されているため、salesテーブルの主キーとして機能します。
数量: 各商品の販売個数を int データ型で表します。
製品 ID: 販売された製品の識別番号 (int として表現)。
販売員: 販売を行った従業員の識別番号。

teamテーブルのproductSpecialty列と同様に、productID列とsalesperson列の両方にFOREIGN KEY制約を適用することにしました。これにより、これらの列には、それぞれproductsテーブルのproductID列とteamテーブルのempID列に存在する値のみが含まれるようになります。.

次の 4 つの列を持つ Sales というテーブルを作成します。

CREATE TABLE sales (
saleID int UNIQUE,
quantity int,
productID int,
salesperson int,
PRIMARY KEY (saleID),
FOREIGN KEY (productID) REFERENCES products (productID),
FOREIGN KEY (salesperson) REFERENCES team (empID)
);

次に、次の INSERT INTO 操作を実行して、サンプルデータを Products テーブルにロードします。

INSERT INTO products
VALUES
(1, 'widget', 18.99),
(2, 'gizmo', 14.49),
(3, 'thingamajig', 39.99),
(4, 'doodad', 11.50),
(5, 'whatzit', 29.99);

次に、サンプルデータを含むチームテーブルをロードします。

INSERT INTO team
VALUES
(1, 'Florence', 1),
(2, 'Mary', 4),
(3, 'Diana', 3),
(4, 'Betty', 2);

サンプルデータを含む sales テーブルもロードします。

INSERT INTO sales
VALUES
(1, 7, 1, 1),
(2, 10, 5, 4),
(3, 8, 2, 4),
(4, 1, 3, 3),
(5, 5, 1, 3);

最後に、営業チームの誰も関与していない状態で会社が数件の売上を達成したと想像してください。これらの売上を記録するには、次の操作を実行して、SalesテーブルにSalesperson列に値がない3行を追加します。

INSERT INTO sales (saleID, quantity, productID)
VALUES
(6, 1, 5),
(7, 3, 1),
(8, 4, 5);

これで、ガイドの残りの部分に進み、SQL でテーブルを結合する方法を学習する準備が整いました。.

JOIN操作の構文を理解する

JOIN句は、UPDATE操作やDELETE操作など、さまざまなSQL文で使用できます。ただし、このガイドの例では、説明のため、SELECTクエリを使用してJOIN句の動作を説明します。.

次の例は、JOIN 句を含む SELECT ステートメントの一般的な構文を示しています。

SELECT table1.column1, table2.column2
FROM table1 JOIN table2
ON search_condition;

この構文は、2つの異なるテーブルから2つの列を返すSELECT文で始まります。JOIN句は複数のテーブルの内容を比較するため、この例の構文では、各列がどのテーブルから選択されるかを指定するために、列名の前にテーブル名とピリオドを付けることに注意してください。これは完全修飾列参照と呼ばれます。.

このような完全修飾列参照はどの操作でも使用できますが、技術的には異なるテーブルの2つの列が同じ名前を持つ操作でのみ必要です。ただし、複数のテーブルを操作する場合は、JOIN操作の可読性と理解度を向上させるため、完全修飾列参照を使用することをお勧めします。.

SELECT の後に FROM 句が続きます。どのクエリでも、FROM 句は、必要なデータを返すために検索する必要があるデータセットを定義する場所です。ここでの唯一の違いは、FROM 句が JOIN キーワードで区切られた 2 つのテーブルで構成されることです。クエリを作成する際の便利な考え方として、どのテーブルからどの列が返されるかを選択することを忘れないようにすることが挙げられます。.

次に、検索条件を定義してクエリが2つのテーブルをどのように結合するかを指定するON句があります。検索条件とは、特定の条件に対して「true」、「false」、または「unknown」と評価される1つ以上の文または式の集合です。JOIN操作は、両方のテーブルのすべての行を結合し、ON句の検索条件が「true」と評価されるすべての行を返すと考えると分かりやすいでしょう。.

ON 句では、通常、関連する2つの列（あるテーブルの外部キーと、その外部キーが参照する別のテーブルの主キーなど）の値が等しいかどうかをテストする検索条件を含めることが適切です。これは、等価結合と呼ばれることもあります。.

equiが複数のテーブルから一致するデータを結合する方法の例として、先ほど追加したサンプルデータを使用して次のクエリを実行します。このクエリは、ProductsテーブルとTeamテーブルを、それぞれのproductID列とproductSpecialty列の一致する値をテストする検索条件で結合します。そして、各営業チームメンバーの名前、彼らが専門とする各製品の名前、そしてそれらの製品の価格を返します。

SELECT team.empName, products.productName, products.price
FROM products JOIN team
ON products.productID = team.productSpecialty

در اینجا مجموعه نتایج این پرس و جو است:

Output
+----------+-------------+-------+
| empName | productName | price |
+----------+-------------+-------+
| Florence | widget | 18.99 |
| Mary | doodad | 11.50 |
| Diana | thingamajig | 39.99 |
| Betty | gizmo | 14.49 |
+----------+-------------+-------+
4 rows in set (0.00 sec)

SQLがこれらのテーブルを結合して結果セットを作成する仕組みを理解するために、プロセスを詳しく見てみましょう。念のため言っておきますが、以下はデータベース管理システムが2つのテーブルを結合する具体的な方法ではありませんが、JOIN操作を手順として考えると分かりやすいでしょう。.

まず、クエリは FROM 句の最初のテーブルの各行と列の製品を出力します。

JOIN Process Example
+-----------+-------------+-------+
| productID | productName | price |
+-----------+-------------+-------+
| 1 | widget | 18.99 |
| 2 | gizmo | 14.49 |
| 3 | thingamajig | 39.99 |
| 4 | doodad | 11.50 |
| 5 | whatzit | 29.99 |
+-----------+-------------+-------+

سپس، به هر یک از این ردیف‌ها نگاه می‌کند و با هر ردیفی از جدول تیم که productSpecialty آن برابر با مقدار productID در آن سطر است، مطابقت می‌دهد:

JOIN Process Example
+-----------+-------------+-------+-------+----------+------------------+
| productID | productName | price | empID | empName | productSpecialty |
+-----------+-------------+-------+-------+----------+------------------+
| 1 | widget | 18.99 | 1 | Florence | 1 |
| 2 | gizmo | 14.49 | 4 | Betty | 2 |
| 3 | thingamajig | 39.99 | 3 | Diana | 3 |
| 4 | doodad | 11.50 | 2 | Mary | 4 |
| 5 | whatzit | 29.99 | | | |
+-----------+-------------+-------+-------+----------+------------------+

سپس، سطرهایی را که مطابقت ندارند را برش می‌دهد و ستون‌ها را بر اساس ترتیب آنها در عبارت SELECT مرتب می‌کند، ستون‌هایی را که مشخص نشده بودند حذف می‌کند، ردیف‌ها را متوسل می‌کند و مجموعه نتایج نهایی را برمی‌گرداند:

JOIN Process Example
+----------+-------------+-------+
| empName | productName | price |
+----------+-------------+-------+
| Florence | widget | 18.99 |
| Mary | doodad | 11.50 |
| Diana | thingamajig | 39.99 |
| Betty | gizmo | 14.49 |
+----------+-------------+-------+
4 rows in set (0.00 sec)

等価結合はテーブルを結合する最も一般的な方法ですが、ON句の検索条件では<、>、LIKE、NOT LIKE、さらにはBETWEENなどの他のSQL演算子も使用できます。ただし、より複雑な検索条件を使用すると、結果セットに表示されるデータを予測することが難しくなる場合があることに注意してください。.

ほとんどの実装では、SQL標準で「qualified JOIN」データ型と呼ばれる列セットを使用してテーブルを結合できます。これは、一般的に、対応するデータ型に関係なく、数値データを保持する列を数値データを保持する他の任意の列と結合できることを意味します。同様に、文字値を保持する任意の列を文字データを保持する他の任意の列と結合することもできます。前述のように、2つのテーブルを結合するために一致させる列は通常、テーブル間の関係を表す列です。例えば、外部キーとそれが参照する別のテーブルの主キーなどです。.

多くのSQL実装では、ONではなくUSINGキーワードを使用して、同じ名前の列を結合できます。このような操作の構文は次のようになります。

SELECT table1.column1, table2.column2
FROM table1 JOIN table2
USING (related_column);

この例の構文では、USING ステートメントは ON table1.related_column = table2.related_column; と同等です。.

salesとproductsにはそれぞれproductIDという列があるため、USINGキーワードを使用してこれらの列を結合できます。次の文はこれを実行し、各saleのsaleID、販売数、販売された各製品の名前、および価格を返します。また、結果セットをsaleIDの値で昇順に並べ替えます。

SELECT sales.saleID, sales.quantity, products.productName, products.price
FROM sales JOIN products
USING (productID)
ORDER BY saleID;

Output
+--------+----------+-------------+-------+
| saleID | quantity | productName | price |
+--------+----------+-------------+-------+
| 1 | 7 | widget | 18.99 |
| 2 | 10 | whatzit | 29.99 |
| 3 | 8 | gizmo | 14.49 |
| 4 | 1 | thingamajig | 39.99 |
| 5 | 5 | widget | 18.99 |
| 6 | 1 | whatzit | 29.99 |
| 7 | 3 | widget | 18.99 |
| 8 | 4 | whatzit | 29.99 |
+--------+----------+-------------+-------+
8 rows in set (0.00 sec)

テーブルを結合する際、データベースシステムは予測しにくい方法で行を配置することがあります。このようにORDER BY句を含めることで、結果セットの一貫性と可読性が向上します。.

2つ以上のテーブルを結合する

2つ以上のテーブルのデータを結合する必要がある場合があります。JOIN句を他のJOIN句の中にネストすることで、任意の数のテーブルを結合できます。以下の構文は、3つのテーブルを結合する場合の例です。

SELECT table1.column1, table2.column2, table3.column3
FROM table1 JOIN table2
ON table1.related_column = table2.related_column
JOIN table3
ON table3.related_column = table1_or_2.related_column;

この例のステートメントの FROM 句は、テーブル 1 をテーブル 2 に結合することから始まります。この ON 句の後に、結合されたテーブルの最初のセットをテーブル 3 と組み合わせる 2 番目の JOIN が開始されます。3 番目のテーブルは、最初のテーブルまたは 2 番目のテーブルのいずれかの列に結合できることに注意してください。.

たとえば、従業員の売上がいくらなのかを知りたいが、従業員が専門とする製品の売上を含む売上記録だけを調べたいとします。.

この情報を取得するには、次のクエリを実行します。このクエリは、まずProductsテーブルとSalesテーブルを、それぞれのproductID列を一致させることで結合します。次に、最初のJOINの各行をproductSpecialty列と一致させることで、Teamテーブルを最初の2つのテーブルに結合します。そして、WHERE句を使用して結果をフィルタリングし、一致した従業員が売上を達成した人物でもある行のみを返します。また、このクエリにはORDER BY句が含まれており、最終結果をsaleID列の値で昇順で並べ替えます。

SELECT sales.saleID,
team.empName,
products.productName,
(sales.quantity * products.price)
FROM products JOIN sales
USING (productID)
JOIN team
ON team.productSpecialty = sales.productID
WHERE team.empID = sales.salesperson
ORDER BY sales.saleID;

SELECT文に列挙されている列の中に、SalesテーブルのValue列の値とProductsテーブルのPrice列の値を乗算する文があることに注目してください。Productsは、一致する行にこれらの値を返します。

Output
+--------+----------+-------------+-----------------------------------+
| saleID | empName | productName | (sales.quantity * products.price) |
+--------+----------+-------------+-----------------------------------+
| 1 | Florence | widget | 132.93 |
| 3 | Betty | gizmo | 115.92 |
| 4 | Diana | thingamajig | 39.99 |
+--------+----------+-------------+-----------------------------------+
3 rows in set (0.00 sec)

これまでの例はすべて、内部結合（inner JOIN）という1種類のJOIN式を使用していました。次のセクションでは、内部結合、外部結合、そしてそれらの違いについて概要を説明します。.

内部JOIN操作と外部JOIN操作

JOIN文には主に2つの種類があります。INNER結合とOUTER結合です。これら2種類の結合の違いは、返されるデータです。INNER結合は結合された各テーブルから一致する行のみを返しますが、OUTER結合は一致する行と一致しない行の両方を返します。.

前のセクションの構文とサンプルクエリはすべてINNER JOIN文を使用していますが、INNERキーワードは含まれていません。ほとんどのSQL実装では、明示的に指定されていない限り、すべてのJOIN文はINNER結合として扱われます。.

外部結合を指定したクエリは複数のテーブルを結合し、一致する行と一致しない行の両方を返します。これは、欠損値のある行を検索する場合や、部分一致が許容される場合に役立ちます。.

外部結合操作は、LEFT OUTER 結合、RIGHT OUTER 結合、および FULL OUTER 結合の 3 種類に分けられます。LEFT OUTER 結合 (または単に左結合) は、結合された 2 つのテーブルから一致するすべての行と、「左」のテーブルから一致しないすべての行を返します。JOIN 操作のコンテキストでは、「左」のテーブルは常に FROM キーワードの直後で JOIN キーワードの左側に指定される最初のテーブルです。同様に、「右」のテーブルは 2 番目のテーブル、つまり JOIN の直後に指定されるテーブルです。RIGHT OUTER 結合は、結合されたテーブルから一致するすべての行と、「右」のテーブルから一致しないすべての行を返します。FULL OUTER JOIN は、両方のテーブルからすべての行を返しますが、これにはいずれかのテーブルの一致しない行も含まれます。.

これらの異なる種類のJOIN句がどのようにデータを返すかを確認するために、前のセクション「サンプルデータベースへの接続と設定」で作成したテーブルに対して、以下のサンプルクエリを実行してください。これらのクエリは、それぞれ異なる種類のJOIN句を指定していることを除けば、同一です。.

この最初の例では、内部結合を使用して、対応する営業担当列と従業員ID列を照合することで、salesテーブルとteamテーブルを結合しています。ここでも、INNERキーワードは明示的に含まれていないにもかかわらず、暗黙的に使用されます。

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName 
FROM sales JOIN team
ON sales.salesperson = team.empID;

このクエリは内部 JOIN 句を使用するため、両方のテーブルから一致する行のみが返されます。

Output
+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName |
+--------+----------+-------------+----------+
| 1 | 7 | 1 | Florence |
| 4 | 1 | 3 | Diana |
| 5 | 5 | 3 | Diana |
| 2 | 10 | 4 | Betty |
| 3 | 8 | 4 | Betty |
+--------+----------+-------------+----------+
5 rows in set (0.00 sec)

このバージョンのクエリでは、代わりに LEFT OUTER JOIN 句を使用します。

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales LEFT OUTER JOIN team
ON sales.salesperson = team.empID;

前のクエリと同様に、このクエリも両方のテーブルから一致する値を返します。ただし、「左」テーブル（この場合はSales）の値のうち、「右」テーブル（Team）に一致する値がないものは返します。左テーブルのこれらの行は右テーブルに一致する行がないため、一致しない値はNULLとして返されます。

Output
+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName |
+--------+----------+-------------+----------+
| 1 | 7 | 1 | Florence |
| 2 | 10 | 4 | Betty |
| 3 | 8 | 4 | Betty |
| 4 | 1 | 3 | Diana |
| 5 | 5 | 3 | Diana |
| 6 | 1 | NULL | NULL |
| 7 | 3 | NULL | NULL |
| 8 | 4 | NULL | NULL |
+--------+----------+-------------+----------+
8 rows in set (0.00 sec)

次のバージョンのクエリでは、代わりに RIGHT JOIN 句を使用します。

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales RIGHT JOIN team
ON sales.salesperson = team.empID;

このクエリのJOIN句は、RIGHT OUTER JOINではなくRIGHT JOINと記述されていることに注意してください。INNER JOIN句を指定するためにINNERキーワードが必須ではないのと同様に、LEFT JOINまたはRIGHT JOINと記述する場合は常にOUTERが暗黙的に指定されます。.

このクエリの結果は前のクエリとは逆で、両方のテーブルのすべての行を返しますが、「右側」のテーブルからは一意の行のみが返されます。

Output
+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName |
+--------+----------+-------------+----------+
| 1 | 7 | 1 | Florence |
| NULL | NULL | NULL | Mary |
| 4 | 1 | 3 | Diana |
| 5 | 5 | 3 | Diana |
| 2 | 10 | 4 | Betty |
| 3 | 8 | 4 | Betty |
+--------+----------+-------------+----------+
6 rows in set (0.00 sec)

JOIN句のテーブルと列の別名

長い名前や非常に説明的な名前を持つテーブルを結合する場合、複数の完全修飾列参照を記述するのは面倒です。これを回避するには、テーブル名または列名に短いエイリアスを指定すると便利です。.

SQL でこれを行うには、FROM 句の各テーブル定義の後に AS キーワードを続け、その後に任意のエイリアスを続けます。

SELECT t1.column1, t2.column2
FROM table1 AS t1 JOIN table2 AS t2
ON t1.related_column = t2.related_column;

この例の構文では、SELECT 句でエイリアスを使用していますが、エイリアスは FROM 句の前に定義されていません。これは、SQL クエリでは実行順序が FROM 句から始まるためです。これは混乱を招く可能性がありますが、クエリの作成を始める前に、このことを覚えておいてエイリアスについて考えておくと便利です。.

たとえば、Sales テーブルと Products テーブルを結合し、それぞれに別名 S と P を指定する次のクエリを実行します。

SELECT S.saleID, S.quantity,
P.productName,
(P.price * S.quantity) AS revenue 
FROM sales AS S JOIN products AS P
USING (productID);

この例では、SalesテーブルのAmount列の値とProductsテーブルのPrice列の対応する値の積に、3つ目のエイリアス「Revenue」が作成されていることに注意してください。これは結果セットの列名からのみ明らかですが、このようなエイリアスを指定することで、クエリ結果の意味や意図を伝えるのに役立ちます。

Output
+--------+----------+-------------+---------+
| saleID | quantity | productName | revenue |
+--------+----------+-------------+---------+
| 1 | 7 | widget | 132.93 |
| 2 | 10 | whatzit | 299.90 |
| 3 | 8 | gizmo | 115.92 |
| 4 | 1 | thingamajig | 39.99 |
| 5 | 5 | widget | 94.95 |
| 6 | 1 | whatzit | 29.99 |
| 7 | 3 | widget | 56.97 |
| 8 | 4 | whatzit | 119.96 |
+--------+----------+-------------+---------+
8 rows in set (0.00 sec)

エイリアスを定義する際、AS キーワードは技術的にはオプションであることに注意してください。前の例は次のようにも記述できます。

SELECT S.saleID, S.quantity, P.productName, (P.price * S.quantity) revenue 
FROM sales S JOIN products P
USING (productID);

AS キーワードはエイリアスの定義に必須ではありませんが、含めることが推奨されます。これにより、クエリの意図が明確になり、読みやすさが向上します。.

結果

このガイドでは、JOIN操作を使用して複数のテーブルを1つのクエリ結果セットに結合する方法を学習しました。ここで紹介するコマンドはほとんどのリレーショナルデータベースで動作しますが、SQLデータベースごとに独自の言語実装が使用されていることに注意してください。各コマンドとそのオプションの詳細については、DBMSのドキュメントを参照してください。.