MENU

SQLで複数のテーブルをJOINしてデータをCOUNTする方法

SQL

SQLで複数のテーブルにわたってデータを集計する場合、JOINを利用してテーブルを結合し、COUNT関数でデータの数を効率的に取得することが重要です。本記事では、SQLで複数のテーブルをJOINしてデータをCOUNTする方法を、具体的なクエリ例を用いてわかりやすく解説します。

件数が増えすぎる場合は、COUNTより先にJOIN後の1行が何を表すかを確認します。0件の親も表示するならLEFT JOINとCOUNT(子の主キー)、複数の1対多を結ぶなら事前集計を検討してください。名前だけでGROUP BYすると同名の別人・別商品が合算されるため、以下ではIDも指定します。

目次

JOINの基本概念

SQLにおけるJOINは、複数のテーブルを関連付けてデータを取得するための操作です。JOINにはいくつかの種類があり、それぞれの使用方法と目的が異なります。

INNER JOIN

INNER JOINは、両方のテーブルに存在する一致するレコードのみを取得します。共通のキーを使ってテーブルを結合し、完全一致した行だけが結果に含まれます。

LEFT JOIN

LEFT JOINは、左側のテーブルの全てのレコードと、右側のテーブルの一致するレコードを取得します。左側のテーブルに存在し、右側のテーブルに存在しない場合は、NULLが返されます。

RIGHT JOIN

RIGHT JOINは、右側のテーブルの全てのレコードと、左側のテーブルの一致するレコードを取得します。右側のテーブルに存在し、左側のテーブルに存在しない場合は、NULLが返されます。

FULL OUTER JOIN

FULL OUTER JOINは両側の不一致行も残します。DBMSによって構文の対応が異なるため、利用する製品のマニュアルを確認してください。以下の中心例ではINNER JOINとLEFT JOINを使います。

これらのJOINを理解することで、必要に応じて適切なJOINを選択し、複数のテーブルからデータを効果的に取得できます。

COUNT関数の基本

COUNT関数は、SQLで特定の条件に一致する行の数を返すために使用されます。データベースの集計において頻繁に使用される基本的な関数です。

COUNT(*)

COUNT(*)は、FROM・JOIN・WHEREで得られた結果の全ての行を数えます。NULLを含む全ての行がカウントされます。

SELECT COUNT(*)
FROM テーブル名;

COUNT(column_name)

COUNT(column_name)は、指定した列の値がNULLでない行だけを数えます。特定の列に限定してカウントする場合に使用します。

SELECT COUNT(列名)
FROM テーブル名;

COUNT(DISTINCT column_name)

COUNT(DISTINCT column_name)は、指定した列の異なる値を数えます。NULLを除外し、重複しない値の数を取得する場合に便利です。

SELECT COUNT(DISTINCT 列名)
FROM テーブル名;

これらのCOUNT関数を適切に使い分けることで、データの正確な集計が可能になります。次のセクションでは、JOINとCOUNTを組み合わせる方法を具体的なSQLクエリの例とともに解説します。

JOINとCOUNTを組み合わせる方法

JOINとCOUNTを組み合わせることで、複数のテーブルから関連データを集計できます。ここでは、具体的なSQLクエリの例を用いて説明します。

基本的なクエリの構造

まず、JOINを使って複数のテーブルを結合し、その後にCOUNT関数を用いて集計します。以下は基本的な構造です。

SELECT COUNT(*)
FROM テーブル1
JOIN テーブル2 ON テーブル1.共通列 = テーブル2.共通列
WHERE 条件;

例1: INNER JOINを使った集計

以下の例では、ordersテーブルとcustomersテーブルをINNER JOINで結合し、特定の顧客の注文数をカウントします。

SELECT COUNT(*)
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.customer_id = 1;

このクエリは、customer_idが1の顧客の注文数をカウントします。

例2: LEFT JOINを使った集計

次に、employeesテーブルとdepartmentsテーブルをLEFT JOINで結合し、各部門に所属する従業員の数をカウントします。

SELECT departments.department_id, departments.department_name, COUNT(employees.employee_id)
FROM departments
LEFT JOIN employees ON departments.department_id = employees.department_id
GROUP BY departments.department_id, departments.department_name;

このクエリは、各部門の従業員数をカウントし、部門ごとに結果をグループ化して表示します。

JOINとCOUNTを組み合わせることで、複雑なデータの集計を効率的に行うことができます。次のセクションでは、INNER JOINを使った集計方法についてさらに詳しく説明します。

INNER JOINを使った集計方法

INNER JOINを使用することで、複数のテーブル間で一致するデータを集計できます。ここでは、INNER JOINを用いた具体的な集計方法を解説します。

INNER JOINの基本構文

INNER JOINは、結合条件に一致するレコードを両方のテーブルから取得します。以下は基本的な構文です。

SELECT 列名1, 列名2, ...
FROM テーブル1
INNER JOIN テーブル2 ON テーブル1.共通列 = テーブル2.共通列
WHERE 条件;

実例: 商品と注文の集計

次に、productsテーブルとordersテーブルをINNER JOINで結合し、特定の商品の注文数をカウントします。

SELECT products.product_id, products.product_name, COUNT(orders.order_id) AS order_count
FROM products
INNER JOIN orders ON products.product_id = orders.product_id
GROUP BY products.product_id, products.product_name;

このクエリは、各商品の注文数をカウントし、商品ごとに結果をグループ化して表示します。

実例: コースごとの登録数の集計

次に、coursesテーブルとenrollmentsテーブルをINNER JOINで結合し、各コースの登録学生数をカウントします。

SELECT courses.course_id, courses.course_name, COUNT(enrollments.student_id) AS student_count
FROM courses
INNER JOIN enrollments ON courses.course_id = enrollments.course_id
GROUP BY courses.course_id, courses.course_name;

このクエリはコースごとの登録行数を数えます。student_idはNOT NULLを前提とします。同一学生の再登録を除いた人数ならCOUNT(DISTINCT enrollments.student_id)を使い、学期別に分ける場合は学期もグループ化します。

INNER JOINを使うことで、関連するデータを効率的に結合し、必要な集計を行うことができます。次のセクションでは、LEFT JOINを使った集計方法について説明します。

LEFT JOINを使った集計方法

LEFT JOINを使用すると、左側のテーブルのすべての行と、右側のテーブルで一致する行を結合し、不一致の場合はNULLを返します。これにより、左側のテーブルのデータを完全に保持しながら、関連するデータを集計できます。

LEFT JOINの基本構文

LEFT JOINの基本構文は次のとおりです。

SELECT 列名1, 列名2, ...
FROM テーブル1
LEFT JOIN テーブル2 ON テーブル1.共通列 = テーブル2.共通列
WHERE 条件;

実例: 部門と従業員の集計

以下の例では、departmentsテーブルとemployeesテーブルをLEFT JOINで結合し、各部門の従業員数をカウントします。

SELECT departments.department_id, departments.department_name, COUNT(employees.employee_id) AS employee_count
FROM departments
LEFT JOIN employees ON departments.department_id = employees.department_id
GROUP BY departments.department_id, departments.department_name;

このクエリは、各部門の全従業員数をカウントし、従業員がいない部門も結果に含めます。

実例: 顧客と注文の集計

次に、customersテーブルとordersテーブルをLEFT JOINで結合し、各顧客の注文数をカウントします。

SELECT customers.customer_id, customers.customer_name, COUNT(orders.order_id) AS order_count
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id, customers.customer_name;

このクエリは、各顧客の注文数をカウントし、注文がない顧客も結果に含めます。

LEFT JOINを使うことで、不一致のデータを含む完全な集計を行うことができます。次のセクションでは、実際のデータベースを使用して、JOINとCOUNTを組み合わせた実践例を紹介します。

実践例

実際のデータベースを使用して、JOINとCOUNTを組み合わせた集計クエリを作成します。ここでは、オンラインストアのデータベースを例に取ります。

実例: 商品別の注文数を集計

まず、productsテーブルとordersテーブルを使用して、各商品の注文数を集計します。

SELECT products.product_id, products.product_name, COUNT(orders.order_id) AS order_count
FROM products
INNER JOIN orders ON products.product_id = orders.product_id
GROUP BY products.product_id, products.product_name;

このクエリは、各商品の注文数を集計し、商品ごとに結果を表示します。以下の数値は表示イメージ用の仮定値です。実行結果ではなく、実際の値はデータによって変わります（ID列は表示例では省略）。

+----------------+-------------+
| product_name   | order_count |
+----------------+-------------+
| 商品A          | 10          |
| 商品B          | 5           |
| 商品C          | 8           |
+----------------+-------------+

実例: 顧客別の総購入金額を集計

次に、customersテーブルとordersテーブルを使用して、各顧客の総購入金額を集計します。

SELECT customers.customer_id, customers.customer_name, COALESCE(SUM(orders.total_amount), 0) AS total_spent
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id, customers.customer_name;

このクエリは、各顧客の総購入金額を集計し、顧客ごとに結果を表示します。以下の数値は表示イメージ用の仮定値です。実行結果ではなく、実際の値はデータによって変わります（ID列は表示例では省略）。

+----------------+-------------+
| customer_name  | total_spent |
+----------------+-------------+
| 顧客A          | 20000       |
| 顧客B          | 15000       |
| 顧客C          | 30000       |
+----------------+-------------+

実例: カテゴリ別の登録商品数を集計

最後に、categoriesテーブルとproductsテーブルを使用して、各カテゴリの登録商品数を集計します。

SELECT categories.category_id, categories.category_name, COUNT(products.product_id) AS product_count
FROM categories
LEFT JOIN products ON categories.category_id = products.category_id
GROUP BY categories.category_id, categories.category_name;

このクエリは、各カテゴリの登録商品数を集計し、カテゴリごとに結果を表示します。以下の数値は表示イメージ用の仮定値です。実行結果ではなく、実際の値はデータによって変わります（ID列は表示例では省略）。

+----------------+--------------+
| category_name  | product_count|
+----------------+--------------+
| カテゴリA      | 12           |
| カテゴリB      | 8            |
| カテゴリC      | 5            |
+----------------+--------------+

これらの実践例を参考にすることで、さまざまなシナリオでJOINとCOUNTを組み合わせたデータ集計が可能です。次のセクションでは、集計クエリを最適化するためのヒントを紹介します。

3テーブルJOINで件数が増える原因と事前集計の実例

以下は独立した検証用の小さなデータです。実データの表では実行せず、空の検証用DBで試してください。注文2件と問い合わせ3件を同じ顧客へ結合すると6行になります。PythonのSQLite 3.53.1で、以下の3つのSELECTの結果を確認しています。他のDBMSでの実行検証や速度検証はしていません。

CREATE TABLE demo_customers (customer_id INTEGER PRIMARY KEY, customer_name TEXT);
CREATE TABLE demo_orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
CREATE TABLE demo_tickets (ticket_id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO demo_customers VALUES (1, '同名'), (2, '同名'), (3, '注文なし');
INSERT INTO demo_orders VALUES (101, 1, 100), (102, 1, 200), (103, 2, 50);
INSERT INTO demo_tickets VALUES (201, 1), (202, 1), (203, 1);

0件なのにCOUNT(*)が1になる理由

SELECT c.customer_id, c.customer_name,
       COUNT(*) AS joined_rows, COUNT(o.order_id) AS order_count
FROM demo_customers AS c
LEFT JOIN demo_orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

customer_idが1・2・3の順に、joined_rowsは2・1・1、order_countは2・1・0です。不一致の親にもNULLで補完された1行があるため、COUNT(*)では0になりません。子の存在を数える列はNULLにならない主キーを使います。

COUNT(DISTINCT)で直せること・直せないこと

SELECT c.customer_id, COUNT(o.order_id) AS inflated_count,
       COUNT(DISTINCT o.order_id) AS unique_orders,
       SUM(o.amount) AS inflated_total
FROM demo_customers AS c
LEFT JOIN demo_orders AS o ON o.customer_id = c.customer_id
LEFT JOIN demo_tickets AS t ON t.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY c.customer_id;

customer_id=1ではinflated_count=6、unique_orders=2、inflated_total=900になります。本来の注文金額は100+200=300です。COUNT(DISTINCT 主キー)は注文の重複を除けますが、SUMの水増しは直しません。SUM(DISTINCT 金額)も同額の別注文を落とすため代用できません。

各子テーブルを顧客単位に集計してから結合する

SELECT c.customer_id, c.customer_name,
       COALESCE(o.order_count, 0) AS order_count,
       COALESCE(o.total_amount, 0) AS total_amount,
       COALESCE(t.ticket_count, 0) AS ticket_count
FROM demo_customers AS c
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
    FROM demo_orders GROUP BY customer_id
) AS o ON o.customer_id = c.customer_id
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS ticket_count
    FROM demo_tickets GROUP BY customer_id
) AS t ON t.customer_id = c.customer_id
ORDER BY c.customer_id;

結果はID=1が注文2件・総額300・問い合わせ3件、ID=2が1件・50・0件、ID=3が0件・0・0件です。各子を1顧客1行へ揃えることで掛け合わせを防ぎます。COALESCEは一致しなかった集計値を0にします。集計前後で数える単位を変えないことが重要です。

既存の商品例は「ordersの1行が1商品を含む注文」を仮定した単純化モデルです。実際に注文ヘッダーと明細が分かれる場合、明細行数・注文数・数量合計は別物です。注文数なら注文IDの重複、販売数量ならSUM(quantity)、商品種類数なら商品IDの重複を考慮してください。

最適化のヒント

JOINとCOUNTを使用した集計クエリは、適切に最適化することでパフォーマンスを向上させることができます。以下に、集計クエリを最適化するためのヒントとベストプラクティスを紹介します。

インデックスの利用

結合キーや絞り込み条件に合うインデックスを検討します。COUNTの対象列に機械的に追加するのではなく、既存索引と実行計画を確認してください。次は候補例であり、書き込み負荷・容量・作成時のロックも考慮します。必ず速くなるとは限りません。

CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_product_id ON orders(product_id);

必要な列のみを選択

SELECT文では、必要な列だけを指定してデータを取得します。不要な列を除外することで、データ転送量を減らし、クエリのパフォーマンスを向上させます。

SELECT products.product_id, products.product_name, COUNT(orders.order_id) AS order_count
FROM products
INNER JOIN orders ON products.product_id = orders.product_id
GROUP BY products.product_id, products.product_name;

適切なJOINの種類を選択

使用するJOINの種類を適切に選択することも重要です。INNER JOINやLEFT JOINの使用を検討し、データ量や集計対象に応じて最適なJOINを選びます。

WHERE句の使用

親を残したまま対象期間の注文だけ集計する場合、右表の条件はON句に書きます。下記の条件をWHEREへ移すと、対象注文がない顧客は結果から消えます。親自体の絞り込みはWHERE、集計後の件数条件はHAVINGで指定します。日付は例なので要件に合わせて変更してください。

SELECT customers.customer_id, customers.customer_name, COALESCE(SUM(orders.total_amount), 0) AS total_spent
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
  AND orders.order_date >= '2023-01-01'
GROUP BY customers.customer_id, customers.customer_name;

集計関数の使用に注意

COUNT、SUM、AVGなどの集計関数は、使用するデータ量が多い場合、パフォーマンスに影響を与えることがあります。必要な場合のみ使用し、データ量を制限する工夫を行います。

サブクエリの利用

派生テーブルで集計結果を外側のクエリから参照できます。ただし一時保存や高速化は保証されません。次の例の件数条件はHAVING COUNT(orders.order_id) > 0でも表せます。実行計画と実測で比較してください。

SELECT customer_name, order_count
FROM (
    SELECT customers.customer_id, customers.customer_name, COUNT(orders.order_id) AS order_count
    FROM customers
    LEFT JOIN orders ON customers.customer_id = orders.customer_id
    GROUP BY customers.customer_id, customers.customer_name
) AS subquery
WHERE order_count > 0;

これらの最適化ヒントを活用することで、JOINとCOUNTを組み合わせた集計クエリのパフォーマンスを向上させることができます。次のセクションでは、この記事の内容をまとめます。

まとめ

SQLで複数のテーブルをJOINしてデータをCOUNTする方法について、基本から具体的な実例まで幅広く解説しました。以下に、本記事の要点をまとめます。

  • JOINの基本概念: INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOINなどの種類とその使用方法を理解することが重要です。
  • COUNT関数の基本: COUNT(*)、COUNT(column_name)、COUNT(DISTINCT column_name)の違いと使用方法を把握しましょう。
  • JOINとCOUNTの組み合わせ: 実際のSQLクエリ例を用いて、JOINとCOUNTを組み合わせたデータ集計の方法を学びました。
  • INNER JOINを使った集計方法: 商品や学生のデータを基にした具体的なINNER JOINの集計クエリを紹介しました。
  • LEFT JOINを使った集計方法: 部門や顧客のデータを基にした具体的なLEFT JOINの集計クエリを解説しました。
  • 実践例: オンラインストアのデータベースを使って、商品別注文数や顧客別総購入金額などの実践的なクエリを示しました。
  • 最適化のヒント: インデックスの利用、必要な列のみを選択、適切なJOINの種類の選択、WHERE句の使用など、クエリパフォーマンスの向上方法を紹介しました。

これらのポイントを理解し、実践することで、複雑なデータベースの集計クエリを効率的に作成できるようになります。SQLのJOINとCOUNTを駆使して、効果的なデータ集計を実現しましょう。

参考資料

PostgreSQL公式：JOIN・WHERE・GROUP BY / SQLite公式：COUNT・SUMなどの集計関数

SQL
データ分析 SQL JOIN

この記事を書いた人

佐藤健司のアバター 佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

関連記事

コメント

コメントする

目次