SQLの結合・集計とトランザクション：INNER/LEFT JOINとACID特性の実務解法

結合したら顧客が消えた。集計したら件数が変わった。何を確認すればよいでしょうか？

残す行、NULLの扱い、集計の順序を確認します。SQLの結果を具体的に読み、更新を安全に確定するトランザクションへつなげます。

テーブル結合の基礎：INNER JOIN と LEFT OUTER JOIN の確認

複数のテーブルから必要なデータを1つの結果セットとして取り出す操作が「結合（JOIN）」です。試験では特に次の2つの挙動の違いが厳密に問われます。

実戦例：注文履歴のない顧客を特定するテクニック

顧客テーブル（Customers）と注文テーブル（Orders）を結合し、「まだ一度も注文したことがない顧客」を取得する場合、LEFT JOIN と WHERE ... IS NULL を組み合わせます。

-- 注文履歴が0件の顧客のみを抽出するSQL
SELECT c.customer_id, c.name
FROM Customers c
LEFT OUTER JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

この例ではOrders.order_idにNOT NULL制約があるものとします。LEFT JOINで顧客を残し、結合できなかった行をIS NULLで選びます。右表の任意のNULL許容列を使うと、結合済みの行も混じるので注意します。

GROUP BY と HAVING 句による集計と実行順序の鉄則

テーブル内の行をグループ化して集計値を求める際、「WHERE句」と「HAVING句」の役割分担を混同しないことが極めて重要です。

-- 結果を理解する論理的な順序。実際の物理的な実行順序とは異なる
-- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
SELECT department_id, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM Employees
WHERE status = 'ACTIVE'
GROUP BY department_id
HAVING COUNT(*) >= 5
ORDER BY avg_sal DESC;

「NULL の集計ルール」にも要注意です。COUNT(*) は NULL を含む行全体をカウントしますが、COUNT(列名) や AVG(列名), SUM(列名) は NULL を無視して計算します。

トランザクションとACID特性の4大原則

データベースに対する一連の不可分な処理単位を「トランザクション（Transaction）」と呼びます。信頼性のあるデータ処理を行うため、RDBは以下のACID（アシッド）特性を完全に満たす必要があります。

排他制御（ロック機構）と2相コミットプロトコル

同時実行制御（Isolation）を実現する代表的な手法が「ロック」です。また、複数の独立したデータベースにまたがるトランザクションを管理するのが「2相コミット」です。

共有ロックと排他ロックの共存関係

2つのトランザクションが互いに相手の保持するリソースのロック解除を待ち続けて永久に停止する状態を「デッドロック（Deadlock）」と呼びます。

対策として「リソースのロック順序を全トランザクションで統一する」「タイムアウトを設けて一方を強制ロールバックする」などが採用されます。

2相コミット（Two-Phase Commit: 2PC）の流れ

分散データベースにおいて原子性を保証するため、主導役の「調整者（コーディネータ）」と複数の「参加者（コホート）」が2段階で合意を形成します。

1. 第1相（準備フェーズ / Prepare Phase）：調整者が全参加者に「コミット準備完了か？」を問い合わせる。全参加者がコミット可能なら「Ready」、不可能なら「Abort」を返答する。

2. 第2相：全参加者が準備済みなら、調整者はコミット決定を永続化して通知します。コミット決定前にAbortや準備応答のタイムアウトがあれば、取り消せます。決定後の応答不明を理由に勝手に全体をロールバックしてはいけません。

実戦演習問題とステップ別解説

問1：外部結合による未登録データの抽出

社員と資格保有の二つのテーブルがあります。資格保有.資格コードはNOT NULLで、社員ID同士を結合します。資格を一つも持たない社員の氏名を取得するSQLはどれでしょうか。

1. ア

   SELECT 社員.氏名 FROM 社員 INNER JOIN 資格保有 ON 社員.社員ID = 資格保有.社員ID WHERE 資格保有.資格コード IS NULL

2. イ

   SELECT 社員.氏名 FROM 社員 LEFT OUTER JOIN 資格保有 ON 社員.社員ID = 資格保有.社員ID WHERE 資格保有.資格コード IS NULL

3. ウ

   SELECT 社員.氏名 FROM 社員 RIGHT OUTER JOIN 資格保有 ON 社員.社員ID = 資格保有.社員ID WHERE 社員.社員ID IS NULL

4. エ

   SELECT 社員.氏名 FROM 社員 CROSS JOIN 資格保有 WHERE 資格保有.資格コード = 0

【正解】イ

【解説】社員全員をベースに残すため「社員 LEFT OUTER JOIN 資格保有」を用います。

資格を持たない社員は結合後に「資格コード」列が NULL になるため、WHERE 句で「IS NULL」を判定することで未保有者を漏れなく抽出できます。アの INNER JOIN では資格保有者しか残らないため空集合になります。

問2：トランザクション障害とログによる回復

ログ先行書き込み（WAL方式）を採用するデータベースで、チェックポイント後にシステム障害が発生した。コミット完了ログが記録されているトランザクションに対して実行すべき回復処理はどれか。

1. ア

   ロールバック（UNDO）を行い、処理開始前の状態に戻す。

2. イ

   ロールフォワード（REDO）を行い、コミットされた更新結果をディスクに反映・再現する。

3. ウ

   データベースを初期化し、直近のバックアップから全データをリストアする。

4. エ

   障害発生時に実行中だったトランザクションの処理を自動的に再開する。

【正解】イ

コミット済みでまだデータ領域に反映されていない更新は、ログからREDOして回復します。未確定の更新は確定状態に含めません。UNDOを行うか、版管理で不可視にするかなど、実際の回復方式はDBMSによって異なります。

LEFT JOINの条件はONかWHEREかで結果が変わる

Ordersに状態statusがある場合、「全顧客を残して完了注文だけを結合」するなら、条件をONへ書きます。WHERE o.status＝'DONE'では注文のない顧客のNULL行が落ちます。

SELECT c.customer_id, c.name, o.order_id
FROM Customers c
LEFT JOIN Orders o
  ON c.customer_id = o.customer_id AND o.status = 'DONE';

COUNT(*)は外部結合で残ったNULL行も数えます。注文数はNOT NULLの注文IDを使ってCOUNT(o.order_id)で数えます。全てNULLの列ではCOUNT(列)は0、SUM・AVGはNULLです。

ロック表と実際の読取りの違い

上のSロック・Xロック表は、同じ資源に明示的にロックするモデルです。MVCCを使うDBMSでは通常のSELECTが過去の確定版を読み、更新中の行のXロックで必ず待たされるとは限りません。

2相コミットでは、準備後に調整者が故障すると、参加者が確定判断を待つことがあります。原子性を保つ仕組みと、停止せず応答できる性質は別です。

出典と仕様を確認する

IPA：FEシラバス Ver.9.2

PostgreSQL：結合

PostgreSQL：隔離レベル

PostgreSQL：2相コミット