PostgreSQLで「NOT IN」句とサブクエリを使いこなす：詳細ガイドとサンプルコード集

PostgreSQLにおいて、「NOT IN」句とサブクエリを組み合わせることは、特定の条件を満たさないレコードを抽出する強力な方法です。このガイドでは、この機能の仕組み、構文、そして実用的な例をわかりやすく解説します。

「NOT IN」句とは？

「NOT IN」句は、ある列の値が、別のデータソース（例えば、別の表やサブクエリ）に存在する値リストに含まれないかどうかを判断するために使用されます。言い換えると、除外条件を指定するためのものです。

基本的な構文は以下の通りです。

SELECT *
FROM your_table
WHERE your_column NOT IN (value1, value2, ..., valueN);

上記の例では、your_table テーブル内の your_column 列の値が、value1 から valueN までのリストに存在しないすべてのレコードが抽出されます。

サブクエリは、別のクエリを括弧内に記述したものであり、別のクエリの中で結果セットとして利用することができます。サブクエリは、複雑なデータ操作を行う際に非常に有用です。

PostgreSQLにおいて、「NOT IN」句とサブクエリを組み合わせることで、より柔軟なデータ抽出が可能になります。

実用的な例

例1：特定の製品カテゴリに属さない製品を探す

products テーブルと categories テーブルがあり、products.category_id 列が categories.id 列と参照関係にある場合、特定のカテゴリに属さない製品をすべて抽出するには以下のクエリを使用します。

SELECT *
FROM products
WHERE category_id NOT IN (
    SELECT id
    FROM categories
    WHERE category_name = '家電'
);

このクエリは、categories テーブルから category_name が '家電' であるカテゴリの id をすべて取得し、products テーブルの category_id 列と比較します。一致しないレコード、つまり、'家電' カテゴリに属さない製品のみが抽出されます。

例2：過去1ヶ月間に注文されていない顧客を特定する

SELECT *
FROM customers
WHERE id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
);

このクエリは、過去1ヶ月間に作成されたすべての注文の customer_id を取得し、customers テーブルの id 列と比較します。一致しないレコード、つまり、過去1ヶ月間に注文を行っていない顧客のみが抽出されます。

まとめ

「NOT IN」句とサブクエリを組み合わせることで、PostgreSQLにおいて高度なデータ抽出を実行することができます。この機能をマスターすることで、複雑なデータ分析やレポート作成を効率的に行うことができます。

補足

• サブクエリは、性能に大きな影響を与える可能性があります。特に、大量のデータに対象となる場合は注意が必要です。
• 複雑なサブクエリの場合は、結合操作を使用して代替できる場合があります。
• PostgreSQLには、データ抽出をより効率的に行うための様々な機能が用意されています。詳細については、公式ドキュメントを参照してください。

この章では、PostgreSQLにおける「NOT IN」句とサブクエリを組み合わせた様々なクエリのサンプルコードを紹介します。これらの例は、この機能の使用方法をより深く理解し、実用的で効率的なデータ抽出クエリを作成するのに役立ちます。

注記: 以下のコード例は、employees テーブルと departments テーブルが存在することを前提としています。

従業員テーブル (employees)

部署テーブル (departments)

特定の部署に属さない従業員を抽出する

SELECT first_name, last_name
FROM employees
WHERE department_id NOT IN (
    SELECT department_id
    FROM departments
    WHERE department_name = '営業'
);

特定の給与額未満の従業員を抽出する

SELECT first_name, last_name, salary
FROM employees
WHERE salary NOT IN (
    SELECT salary
    FROM employees
    WHERE department_id = 1
    ORDER BY salary DESC
    LIMIT 10
);

このクエリは、employees テーブル内の department_id が 1 である上位10件の給与額を取得し、すべての従業員の給与と比較します。一致しないレコード、つまり、特定の給与額未満の従業員の氏名と給与のみが抽出されます。

過去1ヶ月間に活動していない従業員を抽出する

SELECT first_name, last_name
FROM employees
WHERE employee_id NOT IN (
    SELECT employee_id
    FROM activities
    WHERE activity_date >= CURRENT_DATE - INTERVAL '1 month'
);

特定の役職に就いていない管理者を抽出する

SELECT first_name, last_name, job_title
FROM employees
WHERE job_title = 'マネージャー'
AND employee_id NOT IN (
    SELECT manager_id
    FROM projects
);

このクエリは、employees テーブルから job_title が 'マネージャー' であるすべての従業員の employee_id を取得し、projects テーブルの manager_id 列と比較します。一致しないレコード、つまり、プロジェクトの管理者として割り当てられていないマネージャーの氏名と役職のみが抽出されます。

顧客注文履歴がない顧客を特定する

SELECT customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
);

これらのサンプルコードは、ほんの一例です。「NOT IN」句とサブクエリを組み合わせることで、様々なデータ抽出パターンを実現できます。

ヒント:

• より複雑なデータ分析やレポート作成の場合は、結合操作やウィンドウ関数などの代替方法を検討することも有効です。
• パフォーマンスを向上させるために、適切なインデックスを作成することが重要です。
• PostgreSQLの公式ドキュメントには、詳細な情報と様々なユースケースが記載されていますので、参考にしてください。

PostgreSQLにおける「NOT IN」句とサブクエリ以外にも、特定の条件を満たさないレコードを抽出する方法はいくつかあります。以下に、いくつかの代替方法とその概要を紹介します。

JOIN操作は、複数のテーブルを関連付け、結合して新しいテーブルを作成する方法です。NOT IN句とサブクエリを使用する代わりに、LEFT JOINを使用して除外条件を指定することができます。

例：特定の部署に属さない従業員を抽出する

SELECT e.first_name, e.last_name
FROM employees AS e
LEFT JOIN departments AS d
ON e.department_id = d.department_id
WHERE d.department_name IS NULL;

このクエリは、employees テーブル (e) と departments テーブル (d) を department_id 列で結合し、d.department_name が NULL であるレコード、つまり、departments テーブルに一致するレコードが存在しない従業員のみを選択します。

メリット:

• シンプルでわかりやすい構文
• 複雑な結合条件にも対応可能

• サブクエリよりも非効率な場合がある
• すべての結合カラムにインデックスが必要

EXISTS句は、あるサブクエリが結果を返すかどうかを確認するために使用されます。NOT IN句とサブクエリを使用する代わりに、NOT EXISTSを使用して除外条件を指定することができます。

例：過去1ヶ月間に活動していない従業員を抽出する

SELECT first_name, last_name
FROM employees AS e
WHERE NOT EXISTS (
    SELECT *
    FROM activities AS a
    WHERE a.employee_id = e.employee_id
    AND a.activity_date >= CURRENT_DATE - INTERVAL '1 month'
);

このクエリは、employees テーブル (e) の各レコードに対して、activities テーブル (a) で一致するレコードが存在するかどうかをサブクエリで確認します。サブクエリが結果を返さない場合、つまり、過去1ヶ月間に活動履歴がない従業員のみが抽出されます。

• 結合操作よりも柔軟性が高い

• NOT IN句よりも複雑な構文
• NULL値の扱い方が異なる場合がある

ウィンドウ関数は、特定の行グループに沿って集計や計算を行う関数です。NOT IN句とサブクエリを使用する代わりに、ROW_NUMBER()などのウィンドウ関数を使用して除外条件を指定することができます。

SELECT first_name, last_name, salary,
ROW_NUMBER() OVER (ORDER BY salary) AS rank
FROM employees
ORDER BY salary;

このクエリは、employees テーブル内のすべての従業員を給与額の昇順に並べ替え、各レコードに rank という列を追加します。rank 列には、その従業員の給与額が全体の何番目であるかが示されます。rank が11番目以降の従業員は、特定の給与額未満の従業員となります。

• 集計や計算と組み合わせた複雑な分析が可能

• ウィンドウ関数の種類や使用方法を理解する必要がある

これらの代替方法は、それぞれ異なる特性と利点を持っています。状況に応じて適切な方法を選択することが重要です。

• 上記の例はあくまでも基本的なものであり、より複雑なデータ分析やレポート作成の場合は、これらの方法を組み合わせたり、他のテクニックを活用したりする必要があります。