MENU

SQLのLEFT JOINとサブクエリ｜最新注文の取得・NULL・重複対策

SQL

LEFT JOINとサブクエリを組み合わせると、注文のない顧客も残しながら、顧客別の集計や最新注文を取得できます。ただし、MAX(注文日)だけでは、その注文行の金額は取得できません。本記事では、最新日だけを取得する方法と、ROW_NUMBER()で最新の1行を選ぶ方法を分けて説明します。

主なクエリはPostgreSQL、MySQL 8.0以降、SQL Serverのウィンドウ関数を使う書き方です。日付の表示変換など、DBMS固有の例は別途明記します。サンプルは架空の顧客・注文であり、CREATE TABLEやINSERTは本番ではなく検証用データベースで実行してください。

目次

LEFT JOINの基本構文

LEFT JOINは、指定した結合条件に一致する場合は両方のテーブルからデータを取得し、一致しない場合でも左側のテーブルのすべての行を取得します。一致しない行では右側の列がNULLになります。右側に複数の一致行があれば、左側の1行もその件数分返ります。また、後続のWHERE条件によっては左側の行も除外される点に注意してください。

基本構文

SELECT 列名
FROM テーブル1
LEFT JOIN テーブル2
ON テーブル1.列名 = テーブル2.列名;

用途と利点

LEFT JOINは、データの有無にかかわらず左側のテーブルのすべてのデータを保持したまま、右側のテーブルと結合する際に有効です。例えば、顧客リストに対して、各顧客の注文が存在するかを調べる場合に役立ちます。

サブクエリの基本構文

サブクエリは、別のクエリの内部で実行されるクエリです。これにより、複雑なデータ取得やフィルタリングを簡単に行うことができます。サブクエリは、SELECT文、WHERE句、FROM句などの中で使用されます。

基本構文

SELECT 列名
FROM テーブル
WHERE 列名 IN (SELECT 列名 FROM 別のテーブル WHERE 条件);

用途と利点

サブクエリは、ネストされたクエリを使用して、1つのクエリ内で複数の条件を満たすデータを取得する際に役立ちます。例えば、特定の条件に一致する注文を持つ顧客を選択する場合や、特定の基準を満たすレコードをフィルタリングする場合に使用されます。これにより、クエリの柔軟性と表現力が向上します。

LEFT JOINとサブクエリの組み合わせ

LEFT JOINとサブクエリを組み合わせることで、複雑なデータ取得をより効果的に行うことができます。サブクエリを使って絞り込んだデータセットをLEFT JOINで結合することで、必要なデータを精緻に取得できます。

組み合わせの基本構文

SELECT t1.列名, sq.列名
FROM テーブル1 AS t1
LEFT JOIN (
    SELECT 結合列, 列名
    FROM テーブル2
    WHERE 条件
) AS sq
    ON t1.結合列 = sq.結合列;

具体例

まず、全顧客と各顧客の最新の注文日だけを取得する例です。結合に使う顧客IDをサブクエリのSELECTにも含め、顧客IDごとに集約します。

SELECT 顧客.名前, 最新注文.注文日
FROM 顧客
LEFT JOIN (
    SELECT 顧客ID, MAX(注文日) AS 注文日
    FROM 注文
    GROUP BY 顧客ID
) AS 最新注文
ON 顧客.顧客ID = 最新注文.顧客ID;

このようにして、すべての顧客と、それぞれの最新の注文日を取得できます。ここに未集約の金額列を追加しても「最新日の注文金額」にはなりません。PostgreSQLやSQL Serverでは通常エラーになり、MySQLも既定のONLY_FULL_GROUP_BYでは認められません。これを無効にしても、選ばれる金額を保証できないため解決になりません。

実践例: 顧客データと注文データの結合

ここでは、顧客テーブルと注文テーブルをLEFT JOINとサブクエリを用いて結合する具体的な例を示します。これにより、各顧客の最新の注文情報を取得する方法を詳しく説明します。

テーブル構造

まず、顧客テーブル（customers）と注文テーブル（orders）の構造を確認します。

customers テーブル

customer_idnameemail
1Alice[email protected]
2Bob[email protected]
3Charlie[email protected]

orders テーブル

order_idcustomer_idorder_dateamount
10112023-05-10150
10222023-05-15200
10312023-06-01300

検証用のテーブルを用意する

上の表と同じデータを作るSQLです。既存のcustomers・ordersテーブルがない検証用環境で使います。order_dateはDATE型、order_idは主キーとします。

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255)
);
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    order_date DATE,
    amount DECIMAL(12, 2)
);
INSERT INTO customers (customer_id, name, email) VALUES
    (1, 'Alice', '[email protected]'),
    (2, 'Bob', '[email protected]'),
    (3, 'Charlie', '[email protected]');
INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES
    (101, 1, '2023-05-10', 150),
    (102, 2, '2023-05-15', 200),
    (103, 1, '2023-06-01', 300);

クエリ例

以下のクエリでは、顧客ごとに注文日降順で番号を付け、rn = 1だけを結合します。同じ日に複数の注文がある場合は、主キーであるorder_idの大きい行を採用するルールです。これは同日の時系列を証明するものではなく、結果を一意に決めるための規則です。時刻まで管理している場合は、業務上の注文日時を並べ替えに使ってください。

SELECT
    c.customer_id,
    c.name,
    c.email,
    lo.order_date,
    lo.amount
FROM customers AS c
LEFT JOIN (
    SELECT
        order_id,
        customer_id,
        order_date,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC
        ) AS rn
    FROM orders
    WHERE order_date IS NOT NULL
) AS lo
    ON c.customer_id = lo.customer_id
   AND lo.rn = 1
ORDER BY c.customer_id;

結果

このクエリを実行すると、以下の結果が得られます。

customer_idnameemailorder_dateamount
1Alice[email protected]2023-06-01300
2Bob[email protected]2023-05-15200
3Charlie[email protected]NULLNULL

この結果から、AliceとBobの最新の注文情報が取得でき、注文履歴がないCharlieについても顧客情報が表示されます。rn = 1を外側のWHEREに移すと、Charlieのような未一致行は除外されます。ここでは注文日がNULLの注文を最新候補から除外しているため、日付不明の注文しかない顧客も最新注文の列はNULLになります。

ONとWHEREで結果が変わる理由

「条件に合う注文だけ付け、注文がない顧客も残す」なら右側の絞り込みをON句、または右側サブクエリの中に置きます。次の例は金額200以上の注文を結合します。該当注文が複数ある顧客は複数行です。

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
   AND o.amount >= 200
ORDER BY c.customer_id, o.order_id;

一方、外側にWHERE o.amount >= 200と書くと、右側がNULLの未一致行は条件を満たさず、Charlieは残りません。どちらが正しいかは「全顧客を残すか」「該当注文がある顧客だけにするか」で決まります。

最新日の注文をすべて返したい場合

同じ最新日に複数の注文があり、すべて必要ならROW_NUMBER()で1行に絞らず、最新日を元の注文表へ結合します。次の結果は1顧客1行とは限りません。

SELECT c.customer_id, c.name,
       lo.order_id, lo.order_date, lo.amount
FROM customers AS c
LEFT JOIN (
    SELECT o.order_id, o.customer_id, o.order_date, o.amount
    FROM orders AS o
    INNER JOIN (
        SELECT customer_id, MAX(order_date) AS latest_date
        FROM orders
        GROUP BY customer_id
    ) AS m
        ON o.customer_id = m.customer_id
       AND o.order_date = m.latest_date
) AS lo
    ON c.customer_id = lo.customer_id
ORDER BY c.customer_id, lo.order_id;

MAXはNULLを無視し、日付がすべてNULLならlatest_dateもNULLです。NULL同士の等値比較は成立しないため、この場合も有効な日付の最新注文は結合されません。日付不明の注文を扱う要件は別に定めてください。

注意点とベストプラクティス

LEFT JOINとサブクエリを使用する際には、いくつかの注意点とベストプラクティスを守ることで、パフォーマンスを最適化し、クエリの効率を高めることができます。

パフォーマンスの最適化

  1. インデックスの検討: orders(customer_id, order_date, order_id)などを候補にし、データ量・選択率・並べ替え・更新コストを実行計画で確認します。インデックスやサブクエリがあるだけで高速になるとは限りません。
  2. 適切なサブクエリの使用: サブクエリが大規模なデータセットを返す場合、クエリ全体のパフォーマンスが低下することがあります。必要なデータだけを返すようにサブクエリを最適化します。

クエリの読みやすさと保守性

  1. エイリアスの使用: テーブルやサブクエリにエイリアスを付けることで、クエリの可読性を向上させます。特に複数のテーブルが関わる複雑なクエリでは効果的です。
  2. 段階的な確認: まずサブクエリ単独の行数と順位を確認し、その後にLEFT JOINします。CTEで処理を整理する方法もありますが、書き換えだけで高速化するとは限りません。複数の独立したSQLに分ける場合は、その間の更新による不整合にも注意します。

データの整合性

  1. NULL値の取り扱い: LEFT JOINでは右側のテーブルに一致する行がない場合、結果セットにNULL値が含まれることがあります。NULL値の取り扱いを考慮して、必要に応じてCOALESCE関数などを使用してデフォルト値を設定します。
  2. 行数が増える理由を確認: 1対多の結合は、条件が正しくても複数行になります。1顧客1行が必要なら、結合前の集約やROW_NUMBER()で粒度を揃えます。DISTINCTで症状を隠すと必要な注文を落とす可能性があります。

例: COALESCE関数の使用

-- PostgreSQL: 表示用の文字列へ変換してからN/Aを補う
SELECT
    c.customer_id,
    c.name,
    lo.order_date,
    COALESCE(to_char(lo.order_date, 'YYYY-MM-DD'), 'N/A')
        AS order_date_display,
    COALESCE(lo.amount, 0) AS amount_display,
    CASE WHEN lo.order_id IS NULL THEN 'no dated order'
         ELSE 'has dated order' END AS order_status
FROM customers AS c
LEFT JOIN (
    SELECT
        order_id, customer_id, order_date, amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC, order_id DESC
        ) AS rn
    FROM orders
    WHERE order_date IS NOT NULL
) AS lo
    ON c.customer_id = lo.customer_id
   AND lo.rn = 1
ORDER BY c.customer_id;

COALESCEの引数は共通の型に変換できる必要があります。日付型の列に直接'N/A'を混ぜるのではなく、このPostgreSQL例では表示列だけを文字列に変換しました。元の日付列はNULLのまま残しているため、後続の日付計算にも使えます。金額のNULLを0にすると「注文なし」「金額不明」「0円」を同一視しかねません。表示上0にする業務要件がある場合に限り、注文の有無を示す列も残してください。MySQLやSQL Serverでは、そのDBMSの書式変換関数に置き換えます。

検証するポイント

通常の3顧客に加え、同日の追加注文、注文日がNULLの注文、注文のない顧客を用意して確認します。最新1件のクエリは常に1顧客1行になり、同日なら指定したorder_id規則で金額も同じ注文行から返ることを確認してください。最新日全件のクエリは同日注文をすべて返します。

参考資料

PostgreSQL: Table Expressions / PostgreSQL: Window Functions / PostgreSQL: Conditional Expressions / MySQL 8.4: GROUP BYの扱い / SQL Server: ROW_NUMBER

まとめ

LEFT JOINとサブクエリの組み合わせにより、複雑なクエリを簡潔かつ効率的に作成することができます。LEFT JOINは、結合条件に一致しない場合でも左側のテーブルのすべての行を返すため、完全なデータセットを保持しつつ結合を行う際に有効です。サブクエリは、複雑なフィルタリングやデータの絞り込みに役立ちます。

今回の実践例では、顧客データと注文データを結合し、各顧客の最新の注文情報を取得しました。このように、LEFT JOINとサブクエリを組み合わせることで、ビジネス要件に応じた柔軟なデータ取得が可能になります。

最新日だけならMAX、最新の1行なら同順位時の規則を持つROW_NUMBER()、最新日全件なら集約結果と元テーブルの再結合、と使い分けます。未一致行を残す条件の位置、NULLの意味、データ型を確認してから、実行計画で性能を判断してください。

SQL
データベース 高度な処理 SQL サブクエリ LEFT JOIN

この記事を書いた人

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

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

関連記事

コメント

コメントする

目次