SQLのJOIN・サブクエリ・集計関数を身につける
前回は初級編としてデータベースの基本からSQLの基礎まで書いたので、
今回は、少し高度なSQLを中心に紹介します。
ジョイン（JOIN）
ジョイン（JOIN）
ジョインは、複数のテーブルから関連するデータを結合して取得する方法です。
以下のタイプのジョインがあります。
よく使うのは、INNER JOIN 、LEFT JOIN です。
| ジョインタイプ | 説明 |
|---|---|
| INNER JOIN | 両方のテーブルに存在するデータのみを取得します。 |
| LEFT JOIN | 左のテーブルの全てのデータと、右のテーブルに一致するデータを取得します。 右のテーブルに一致するデータがない場合はNULLが表示されます。 |
| RIGHT JOIN | 右のテーブルの全てのデータと、左のテーブルに一致するデータを取得します。 左のテーブルに一致するデータがない場合はNULLが表示されます。 |
| FULL OUTER JOIN | 両方のテーブルの全てのデータを取得します。 一致するデータがない場合はNULLが表示されます。 |
| CROSS JOIN | 両方のテーブルの全ての組み合わせを取得します。 |
INNER JOIN
両方のテーブルに存在する共通のデータのみを取得します。
基本形:
SELECT カラム名1, カラム名2, ...
FROM テーブル1
INNER JOIN テーブル2 ON テーブル1.カラム名 = テーブル2.カラム名;
以下の例では、orders テーブルと customers テーブルを結合し、注文と対応する顧客の名前を取得します。
両方のテーブルに存在するデータのみを取得するため、NULLのデータのレコードは削除されてしまいます。
クエリ:
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
実行前のテーブル (orders):
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 101 | 2024-01-01 |
| 2 | 102 | 2024-01-02 |
| 3 | 103 | 2024-01-03 |
実行前のテーブル (customers):
| customer_id | customer_name |
|---|---|
| 101 | Alice |
| 103 | Carol |
（↑customer_id102が存在しない = NULL）
実行結果:
| order_id | customer_name |
|---|---|
| 1 | Alice |
| 4 | Carol |
LEFT JOIN
左のテーブルの全てのデータと、右のテーブルに一致するデータを取得します。
右のテーブルに一致するデータがない場合はNULLが表示されます。
ちなみに、LEFT JOINにおける「左のテーブル」とは、JOIN 句の前に記述されたテーブルのことを指します。
(以下の例では、orders)
基本形:
SELECT カラム名1, カラム名2, ...
FROM テーブル1
LEFT JOIN テーブル2 ON テーブル1.カラム名 = テーブル2.カラム名;
以下の例では、orders テーブルの全ての注文と、それに対応する顧客の名前を取得します。
対応する顧客がいない場合はNULLを表示します。
クエリ:
SELECT orders.order_id, customers.customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;
実行前のテーブル (orders):
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 101 | 2024-01-01 |
| 2 | 102 | 2024-01-02 |
| 3 | 103 | 2024-01-03 |
実行前のテーブル (customers):
| customer_id | customer_name |
|---|---|
| 101 | Alice |
| 103 | Carol |
（↑customer_id102が存在しない = NULL）
実行結果:
| order_id | customer_name |
|---|---|
| 1 | Alice |
| 2 | NULL |
| 3 | Carol |
RIGHT JOIN
右のテーブルの全てのデータと、左のテーブルに一致するデータを取得します。左のテーブルに一致するデータがない場合はNULLが表示されます。
基本形:
SELECT カラム名1, カラム名2, ...
FROM テーブル1
RIGHT JOIN テーブル2 ON テーブル1.カラム名 = テーブル2.カラム名;
以下の例では、customers テーブルの全ての顧客と、それに対応する注文を取得します。
対応する注文がない場合はNULLを表示します。
クエリ:
SELECT orders.order_id, customers.customer_name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;
実行前のテーブル (orders):
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 101 | 2024-01-01 |
| 3 | 103 | 2024-01-03 |
（↑order_id2が存在しない = NULL）
実行前のテーブル (customers):
| customer_id | customer_name |
|---|---|
| 101 | Alice |
| 102 | Bob |
| 103 | Carol |
実行結果:
| order_id | customer_name |
|---|---|
| 1 | Alice |
| NULL | Bob |
| 3 | Carol |
FULL OUTER JOIN
両方のテーブルの全てのデータを取得します。一致するデータがない場合はNULLが表示されます。
基本形:
SELECT カラム名1, カラム名2, ...
FROM テーブル1
FULL OUTER JOIN テーブル2 ON テーブル1.カラム名 = テーブル2.カラム名;
以下の例では、全ての注文と全ての顧客のデータを取得し、対応しない場合はNULLを表示します。
クエリ:
SELECT orders.order_id, customers.customer_name
FROM orders
FULL OUTER JOIN customers ON orders.customer_id = customers.customer_id;
この FULL OUTER JOIN のクエリ実行結果を得るためには、orders テーブルと customers テーブルの内容が以下のようになっている必要があります。
実行前のテーブル (orders):
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 101 | 2024-01-01 |
| 2 | 102 | 2024-01-02 |
| 3 | 104 | 2024-01-03 |
| 4 | 101 | 2024-01-04 |
実行前のテーブル (customers):
| customer_id | customer_name |
|---|---|
| 101 | Alice |
| 102 | Bob |
| 103 | Carol |
実行結果:
| order_id | customer_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | NULL |
| 4 | Alice |
| NULL | Carol |
CROSS JOIN
CROSS JOIN は、2つのテーブルのすべての組み合わせを取得します。
これにより、片方のテーブルの行数にもう片方のテーブルの行数を掛けた結果が得られます。
あまり使う機会はないですが、こういうこともできるんだなと程度で覚えておくと良いです。
（全ての組み合わせを生成するので、結果の行数が膨大になって、パフォーマンスに悪影響を及ぼす可能性があります。）
クエリ:
SELECT orders.order_id, customers.customer_name
FROM orders
CROSS JOIN customers;
実行前のテーブル (orders):
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 101 | 2024-01-01 |
| 2 | 102 | 2024-01-02 |
| 3 | 105 | 2024-01-03 |
実行前のテーブル (customers):
| customer_id | customer_name |
|---|---|
| 101 | Alice |
| 102 | Bob |
| 103 | Carol |
orders テーブルには 3 行、customers テーブルには 3 行あります。
この場合、CROSS JOIN を実行すると、結果は 3 × 3 = 9 行になります。
実行結果:
| order_id | customer_name |
|---|---|
| 1 | Alice |
| 1 | Bob |
| 1 | Carol |
| 2 | Alice |
| 2 | Bob |
| 2 | Carol |
| 3 | Alice |
| 3 | Bob |
| 3 | Carol |
サブクエリ（Subquery）
サブクエリは、クエリの内部で他のクエリを使用する方法です。
これにより、より複雑なデータの抽出が可能となり、複数のクエリを組み合わせて条件を設定することができます。
サブクエリもよく使うことになると思います。
（余談ですが、サブクエリは、副問合せって言ったりもします）
サブクエリの基本形
サブクエリは、メインクエリのSELECT、FROM、WHEREなどの節の中で使用できます。
特にWHERE節で使用される場合が多く、ある条件を満たすデータを絞り込む際に便利です。
基本形:
SELECT カラム名1, カラム名2, ...
FROM テーブル名
WHERE カラム名 IN (SELECT サブクエリのカラム名 FROM サブクエリのテーブル名 WHERE 条件);
サブクエリの例
以下の例では、orders テーブルで2024年1月1日以降に注文した顧客のcustomer_idを取得し、その顧客のcustomer_idとcustomer_nameをcustomersテーブルから取得します。
クエリ:
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date > '2024-01-01');
実行前のテーブル (customers):
| customer_id | customer_name |
|---|---|
| 101 | Alice |
| 102 | Bob |
| 103 | Carol |
| 104 | Dave |
実行前のテーブル (orders):
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 101 | 2024-01-01 |
| 2 | 102 | 2024-01-02 |
| 3 | 103 | 2023-12-31 |
| 4 | 101 | 2024-01-03 |
実行結果:
| customer_id | customer_name |
|---|---|
| 101 | Alice |
| 102 | Bob |
集計関数
集計関数は、データを特定のグループごとに集計し、各グループに対して統計的な情報を計算するためのものです。これにより、データの要約や全体的な傾向を把握することができます。
代表的な集計関数
| 関数 | 説明 |
|---|---|
| COUNT() | グループ内の行数を数えます。 |
| SUM() | グループ内の特定カラムの合計を計算します。 |
| AVG() | グループ内の特定カラムの平均を計算します。 |
| MIN() | グループ内の特定カラムの最小値を取得します。 |
| MAX() | グループ内の特定カラムの最大値を取得します。 |
集計関数の基本使用方法
集計関数は、通常SELECT文とGROUP BY句を組み合わせて使用します。
GROUP BY句は、どのカラムでデータをグループ化するかを指定し、各グループに対して集計処理を行います。
基本構文:
SELECT グループ化するカラム, 集計関数(カラム名) AS 別名, ...
FROM テーブル名
GROUP BY グループ化するカラム;
集計関数の使用例
以下の例では、employees テーブルを使用して、各部署ごとの従業員数、給与の合計、平均給与、最小給与、最大給与を取得します。
クエリ:
SELECT department,
COUNT(*) AS num_employees,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department;
実行前のテーブル (employees):
| id | name | department | salary |
|---|---|---|---|
| 1 | Alice | Sales | 5000 |
| 2 | Bob | HR | 4000 |
| 3 | Carol | Sales | 5500 |
| 4 | Dave | IT | 6000 |
| 5 | Eve | IT | 6500 |
| 6 | Frank | HR | 4200 |
実行結果:
| department | num_employees | total_salary | avg_salary | min_salary | max_salary |
|---|---|---|---|---|---|
| Sales | 2 | 10500 | 5250 | 5000 | 5500 |
| HR | 2 | 8200 | 4100 | 4000 | 4200 |
| IT | 2 | 12500 | 6250 | 6000 | 6500 |
Discussion