PostgreSQLで関連レコードが存在しない場合のデータ削除：3つの方法とサンプルコード

2024-05-21

PostgreSQLにおける左外部結合を用いた削除

動作原理

左外部結合では、左側テーブルのすべてのレコードと、右側テーブルに一致するレコード (存在する場合) が結合されます。一致するレコードがない場合、右側テーブルの結合カラムは NULL 値となります。

削除操作では、この結合結果を用いて、左側テーブルの特定の条件を満たすレコードを削除します。右側テーブルとの一致にかかわらず、条件を満たすレコードが削除されます。

具体的な手順

以下の手順は、customers テーブルと orders テーブルを例として説明します。

結合条件を定義: 削除対象となるレコードを決定するために、左側テーブル (customers) と右側テーブル (orders) の結合条件を定義します。一般的には、主キーと外部キーによる結合が行われます。

SELECT *
FROM customers
LEFT OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

WHERE句で削除条件を指定: 結合結果に対して、WHERE 句を用いて削除条件を指定します。この条件を満たすレコードが削除対象となります。

DELETE
FROM customers
LEFT OUTER JOIN orders
ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;

上記の例では、orders テーブルに一致するレコードが存在しない (orders.order_id IS NULL) すべての customers レコードが削除されます。

利点

左側テーブルのレコード整合性を保ちつつ、関連レコードが存在しないレコードを削除できます。
結合と削除を 1 つのクエリで実行できるため、コードの簡潔性と処理効率が向上します。

注意事項

誤った結合条件や削除条件を指定すると、意図しないレコードが削除される可能性があります。
結合と削除が同時に行われるため、ロールバック操作が複雑になる場合があります。

より複雑な条件での削除には、EXISTS サブクエリやウィンドウ関数なども利用できます。
データベース操作を行う前に、必ずバックアップを取ることを推奨します。

PostgreSQL ドキュメント:

-- customers テーブルと orders テーブルを作成
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- サンプルデータ挿入
INSERT INTO customers (customer_id, name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie');

INSERT INTO orders (order_id, customer_id) VALUES
  (1, 1),
  (2, 2),
  (3, 3);

-- orders テーブルに一致しない customers レコードを削除
DELETE
FROM customers
LEFT OUTER JOIN orders
ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;

-- 削除後のレコードを確認
SELECT * FROM customers;

このコードを実行すると、customers テーブルから customer_id 3 のレコードのみが削除され、customer_id 1 と 2 のレコードは保持されます。

補足

上記のサンプルコードは、あくまでも基本的な例です。実際の運用環境では、テーブル構造やデータ量に応じて、適切なクエリを設計する必要があります。
結合と削除を同時に行うため、ロールバック操作が複雑になる場合があります。重要な操作を行う前に、必ずバックアップを取ることを推奨します。

PostgreSQLにおける左外部結合を用いた削除: その他の方法

EXISTS サブクエリを用いる方法は、DELETE 句よりも複雑な条件での削除に適しています。

DELETE
FROM customers
WHERE EXISTS (
  SELECT 1
  FROM orders
  WHERE orders.customer_id = customers.customer_id
);

利点:

より複雑な条件での削除に柔軟に対応できる。

注意点:

サブクエリが複雑になると、処理効率が低下する可能性がある。

ウィンドウ関数を用いる方法は、特定のグループ内のレコードを削除したい場合に有効です。

DELETE
FROM customers
WHERE ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY customer_id) > 1;

上記の例では、各 customer_id グループ内で 2 番以降のレコードが削除されます。

特定のグループ内のレコードを効率的に削除できる。

ウィンドウ関数の使用方法が複雑な場合がある。

CREATE TRIGGER delete_orphaned_customers
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
  DELETE
  FROM customers
  WHERE customer_id = OLD.customer_id
  AND NOT EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.customer_id
  );
END;