SQL Academy · レッスン

同じテーブル内の行を比較する

Q: 「同じテーブル内の行を比較する」レッスンは無料ですか？

はい。「同じテーブル内の行を比較する」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

Q: 「同じテーブル内の行を比較する」で何を学びますか？

組み合わせと関係を見つけます。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

Q: SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン3/4です。

Q: 「同じテーブル内の行を比較する」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

Q: このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

組み合わせと関係を見つけます。

「同じテーブル内の行を比較する」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン3/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

自己結合とは

自己結合とは、テーブルを自分自身に結合することです。最初は特殊に聞こえますが、同じテーブル内の行を比較するための強力な手法です。

manager_idが同じテーブル内の別の行を指すemployeesテーブルを考えてみましょう。自己結合を使うと、1つのクエリで各従業員とそのマネージャーを対応付けられます。

例で使うテーブルの準備

このレッスン全体で使う、簡単なemployeesテーブルを作成しましょう。各行にはid、name、salary、同じテーブル内の別の従業員を参照するmanager_idがあります。

CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       VARCHAR(50),
  salary     INT,
  manager_id INT
);

INSERT INTO employees VALUES
  (1, 'Alice',   90000, NULL),
  (2, 'Bob',     70000, 1),
  (3, 'Carol',   65000, 1),
  (4, 'Dave',    55000, 2),
  (5, 'Eve',     60000, 2),
  (6, 'Frank',   48000, 3);

自己結合の基本構文

自己結合を行うには、同じテーブルを異なる2つのエイリアスで参照します。エイリアスによって、テーブルのコピーが2つあるかのように扱えます。次に、2つのコピーを結び付ける結合条件を指定します。

ここでは、employeesにe（従業員側）というエイリアスを付け、m（マネージャー側）と照合し、e.manager_id = m.idで一致させています。

SELECT
  e.name   AS employee,
  m.name   AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

LEFT JOINで最上位の行を含める

通常のINNER JOINでは、manager_idがNULLの行が除外されます。そのため、最上位の従業員（CEOのAlice）は結果から除外されます。

LEFT JOINを使うと、マネージャーがいない従業員も含め、すべての従業員を表示できます。その場合、マネージャー列は単にNULLになります。

SELECT
  e.name          AS employee,
  m.name          AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY m.name NULLS LAST, e.name;

同じ属性を持つ組み合わせを見つける

自己結合は、同じ値を持つ行の組み合わせを見つける場合にも役立ちます。たとえば、同じマネージャーに報告する従業員の組み合わせをすべて見つけられます。

a.id < b.idという条件により、(Bob, Carol)と(Carol, Bob)のような重複する組み合わせが両方とも結果に表示されるのを防ぎます。

SELECT
  a.name       AS employee_1,
  b.name       AS employee_2,
  a.manager_id AS shared_manager
FROM employees a
JOIN employees b
  ON a.manager_id = b.manager_id
 AND a.id < b.id;

行同士の給与を比較する

自己結合を使って、行をまたいで数値を比較できます。以下のクエリでは、自分のマネージャーより給与が高い従業員をすべて見つけます。これは典型的な分析チェックです。

SELECT
  e.name       AS employee,
  e.salary     AS emp_salary,
  m.name       AS manager,
  m.salary     AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

一致する行がない行を見つける

LEFT JOINとWHERE ... IS NULLの確認を組み合わせた自己結合により、一致する相手の行がない行を見つけられます。ここでは、直属の部下がいない従業員、つまり階層の末端ノードを見つけます。

SELECT e.name AS employee
FROM employees e
LEFT JOIN employees sub ON sub.manager_id = e.id
WHERE sub.id IS NULL
ORDER BY e.name;

直属の部下を数える

テーブルを自分自身に結合し、マネージャー側でグループ化すると、各マネージャーの直属の部下の人数を数えられます。直属の部下が0人の従業員も、LEFT JOINによって含まれます。

SELECT
  m.name           AS manager,
  COUNT(e.id)      AS direct_reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY direct_reports DESC;

CTEで自己結合を分かりやすくする

自己結合のクエリが複雑になった場合は、共通テーブル式（CTE）で包むとロジックが読みやすくなります。階層構造用のCTEを1つ定義し、そのCTEに対して分かりやすくクエリを実行します。

WITH hierarchy AS (
  SELECT
    e.id         AS emp_id,
    e.name       AS employee,
    e.salary     AS emp_salary,
    m.name       AS manager,
    m.salary     AS mgr_salary
  FROM employees e
  LEFT JOIN employees m ON e.manager_id = m.id
)
SELECT *
FROM hierarchy
WHERE mgr_salary IS NOT NULL
  AND emp_salary > mgr_salary;

階層構造ではないテーブルへの自己結合

自己結合は、マネージャーと従業員の階層構造に限られません。以下の例では、productsテーブルを使って同じカテゴリーに属する商品の組み合わせをすべて見つけます。これはレコメンデーションエンジンや類似性の確認に役立ちます。

CREATE TABLE products (
  id       INT PRIMARY KEY,
  name     VARCHAR(50),
  category VARCHAR(30)
);

INSERT INTO products VALUES
  (1, 'Laptop',  'Electronics'),
  (2, 'Phone',   'Electronics'),
  (3, 'Tablet',  'Electronics'),
  (4, 'Shirt',   'Clothing'),
  (5, 'Jeans',   'Clothing');

SELECT
  a.name AS product_1,
  b.name AS product_2,
  a.category
FROM products a
JOIN products b
  ON a.category = b.category
 AND a.id < b.id;

注意点：直積と重複

自己結合でよくある間違いは、直積（結合条件を完全に忘れること）と重複する組み合わせ（a.id < b.idの代わりにa.id <> b.idを使うこと）の2つです。

結合条件が正しいことを必ず確認してください。順序付きの組み合わせではなく順序なしの組み合わせだけが必要な場合は、<>ではなく<を使います。

-- Wrong: returns every ordered pair (A,B) AND (B,A)
SELECT a.name, b.name
FROM employees a
JOIN employees b ON a.manager_id = b.manager_id
                AND a.id <> b.id;

-- Correct: returns each unordered pair once
SELECT a.name, b.name
FROM employees a
JOIN employees b ON a.manager_id = b.manager_id
                AND a.id < b.id;

理解度チェック

自己結合と、同じテーブル内の行の比較について理解度を確認します。

レッスンのまとめ

このレッスンでは、自己結合を使用して同じテーブル内の行を比較する方法を学びました。

主なポイント：

テーブルに2つの別名（例：e と m）を付け、データベースがそれらを別々のデータソースとして扱うようにします。
一致する行だけが必要な場合はINNER JOINを使用し、一致しない行も含める場合はLEFT JOINを使用します。
a.id < b.idを使用して、順序付けされていないペアの重複を避けます。
自己結合は、階層構造（上司と従業員）、類似性検索（同じカテゴリ）、同じテーブル内の2つの行を関連付ける必要があるあらゆる比較に使用できます。
CTEを使用すると、複雑な自己結合クエリが大幅に読みやすくなり、保守もしやすくなります。

よくある質問