【徹底解説】LEFT JOINとWHERE句を使って、あるテーブルに存在するレコードのうち、別のテーブルに存在しないレコードを選択する方法

2024-04-02

SQLでテーブル間で存在しないレコードを選択する方法

このチュートリアルでは、SQL Server、MySQL、PostgreSQLなどのデータベースで、あるテーブルに存在するレコードのうち、別のテーブルに存在しないレコードを選択する方法を解説します。

問題

customers テーブルと orders テーブルがあるとします。customers テーブルには顧客情報、orders テーブルには注文情報が格納されています。

このとき、customers テーブルに存在する顧客のうち、orders テーブルに注文履歴がない顧客 をすべて選択したい場合があります。

解決方法

この問題を解決するには、LEFT JOIN と WHERE 句を使用します。

例

SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

このクエリは、以下の処理を行います。

customers テーブルと orders テーブルを c.id と o.customer_id で結合します。
orders テーブルの customer_id が NULL であるレコードのみを選択します。

解説

LEFT JOIN は、左側のテーブルのすべてのレコードを返し、右側のテーブルと一致するレコードがあればそれを追加します。
WHERE 句は、条件に一致するレコードのみを抽出します。
o.customer_id IS NULL は、orders テーブルに customer_id が存在しないことを意味します。

EXISTS キーワードを使用する方法もあります。

SELECT c.*
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
);

LEFT JOIN と WHERE 句、または EXISTS キーワードを使用することで、あるテーブルに存在するレコードのうち、別のテーブルに存在しないレコードを選択することができます。

-- テーブル定義
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  order_date DATETIME
);

-- データ挿入
INSERT INTO customers (id, name) VALUES (1, 'John Doe');
INSERT INTO customers (id, name) VALUES (2, 'Jane Doe');
INSERT INTO customers (id, name) VALUES (3, 'Peter Smith');

INSERT INTO orders (id, customer_id, order_date) VALUES (1, 1, '2023-01-01');
INSERT INTO orders (id, customer_id, order_date) VALUES (2, 2, '2023-02-01');

-- クエリ実行
SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

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

id | name
------- | --------
3 | Peter Smith

SELECT c.*
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
);

このクエリは、customers テーブルの各レコードについて、orders テーブルに customer_id が存在するかどうかを確認します。orders テーブルに customer_id が存在しないレコードのみが返されます。

他の方法

NOT IN 句を使用すると、あるテーブルの列の値が別のテーブルの列の値のリストに存在しないレコードを選択できます。

SELECT c.*
FROM customers c
WHERE c.id NOT IN (
  SELECT customer_id
  FROM orders
);

このクエリは、customers テーブルの id 列の値が orders テーブルの customer_id 列の値のリストに存在しないレコードをすべて選択します。

SELECT c.*
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL OR c.id IS NULL;

このクエリは、customers テーブルと orders テーブルを c.id と o.customer_id で結合し、orders テーブルの customer_id が NULL または customers テーブルの id が NULL であるレコードをすべて返します。

サブクエリを使用して、別のテーブルに存在しないレコードを選択することもできます。

SELECT c.*
FROM customers c
WHERE c.id IN (
  SELECT customer_id
  FROM (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) = 0
  ) AS t
);