本解説では、PostgreSQL 14を用いて各種JOIN操作の動作原理と実際の挙動を検証します。MySQLとは異なり、PostgreSQLはFULL OUTER JOINをネイティブにサポートするため、包括的な比較が可能です。

JOINの分類と意味論

SQLにおける結合操作は、テーブル間の関係性に基づき、以下の6種類に大別されます（各外結合にはOUTERキーワードの省略形も存在し、合計8通りの記法が実用上登場します）：

サンプルデータの準備

検証用に以下の2つのテーブルを作成し、テストデータを挿入します：

CREATE TABLE participants (
  pid SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  region TEXT
);

CREATE TABLE registrations (
  rid SERIAL PRIMARY KEY,
  participant_id INT,
  event_code TEXT,
  status TEXT DEFAULT 'active'
);

INSERT INTO participants (pid, name, region) VALUES
  (1, 'Aragorn', 'Gondor'),
  (2, 'Legolas', 'Mirkwood'),
  (5, 'Gimli', 'Erebor'),
  (6, 'Frodo', 'Shire');

INSERT INTO registrations (participant_id, event_code, status) VALUES
  (1, 'ELF-2024', 'confirmed'),
  (2, 'DWARF-2024', 'pending'),
  (3, 'HOBBIT-2024', 'cancelled'),
  (4, 'WIZARD-2024', 'confirmed');

INNER JOIN：共通キーを持つレコードのみを取得

参加者と登録情報の両方に存在するIDのみを抽出します。

SELECT 
  p.pid AS participant_id,
  p.name,
  r.event_code,
  r.status
FROM participants p
INNER JOIN registrations r ON p.pid = r.participant_id;

このクエリは、pid = 1 および pid = 2 のみを返します。他のID（3, 4, 5, 6）は片方のテーブルにしか存在しないため除外されます。

OUTER JOINの応用パターン

LEFT JOIN：主テーブルの完全なカバレッジ

すべての参加者を対象に、登録状況を付与します。

SELECT 
  p.name,
  COALESCE(r.event_code, 'not registered') AS event,
  COALESCE(r.status, 'N/A') AS reg_status
FROM participants p
LEFT JOIN registrations r ON p.pid = r.participant_id;

LEFT JOIN + NULLフィルタ：未登録者を特定

登録情報がまったくない参加者を抽出するには、右テーブルの結合キーがNULLである条件を追加します。

SELECT p.name
FROM participants p
LEFT JOIN registrations r ON p.pid = r.participant_id
WHERE r.rid IS NULL;

FULL OUTER JOIN：双方の孤児レコードを可視化

参加者テーブルにも登録テーブルにも存在しない「不整合」を一括で確認できます。

SELECT 
  p.name AS participant_name,
  r.event_code,
  CASE 
    WHEN p.pid IS NULL THEN 'orphan_in_registrations'
    WHEN r.rid IS NULL THEN 'orphan_in_participants'
    ELSE 'matched'
  END AS record_status
FROM participants p
FULL OUTER JOIN registrations r ON p.pid = r.participant_id;

CROSS JOIN：組み合わせ全探索

あらゆる参加者とイベントコードのペアを生成するユースケース（例：初期登録フォームのテンプレート生成）に利用されます。

SELECT 
  p.name,
  e.code AS available_event
FROM participants p
CROSS JOIN (VALUES ('ELF-2024'), ('DWARF-2024'), ('HOBBIT-2024')) AS e(code);

このクエリは、4人の参加者 × 3つのイベントコード = 合計12行の結果を返します。