SQL練習室
← SQLガイド

PostgreSQLのDELETE … USINGとは?別の表の条件で行を削除する書き方

DELETE … USINGは、別の表を結合して、その表の値を条件に行を削除するPostgreSQLの書き方です。退会申請のある会員を消す、特定の理由の申請を持つ行だけを消す、といった削除を、結合条件とあわせて書けます。標準SQLではなくPostgreSQLの拡張なので、EXISTSやINでの書き方との使い分けも合わせて覚えます。

例で使う表

membersは会員1人が1行で、idは会員番号、nameは名前、planは契約プラン(free・pro)です。withdrawalsは退会申請で、member_idがmembersのidに対応します。田中(id 3)は申請が2件あり、member_idが9の行はmembersに該当者がいません。loginsは会員のログイン記録で、ログインのない会員は行がありません。各節では実行前の状態から削除し、残ったmembersをid順に表示します。

members:会員5件(削除前)
idnameplan
1佐藤free
2鈴木pro
3田中free
4高橋pro
5伊藤free
withdrawals:退会申請4件
member_idreason
2転居
3不満
3料金
9不明
logins:ログイン記録4件
member_idchannel
1app
1web
2web
4app

DELETE … USINGの基本構文

sql
DELETE FROM members
USING withdrawals AS w
WHERE members.id = w.member_id;

USINGで別の表を指定して、一致する行を削除する

DELETE FROMの後ろにUSINGで別の表を並べ、結合条件をWHEREに書きます。結合に一致した行だけが削除されます。ここでは退会申請のある鈴木(id 2)と田中(id 3)が消え、佐藤・高橋・伊藤の3人が残ります。田中は申請が2件ありますが、削除されるのは1行です。member_idが9の申請は、一致する会員がいないので何も起きません。

sql
DELETE FROM members
USING withdrawals AS w
WHERE members.id = w.member_id;

SELECT * FROM members ORDER BY id;
実行結果
idnameplan
1佐藤free
4高橋pro
5伊藤free

別の表の値で絞って削除する

USINGにした表の列は、WHEREの条件に使えます。結合条件にANDで足すと、「理由が料金の申請がある会員」のように、別の表の値で削除対象を絞れます。ここでは理由が料金の田中だけが消え、転居の申請がある鈴木は残ります。別の表の値を条件に使えることが、USINGを使う主な理由です。

sql
DELETE FROM members
USING withdrawals AS w
WHERE members.id = w.member_id
  AND w.reason = '料金';

SELECT * FROM members ORDER BY id;
実行結果
idnameplan
1佐藤free
2鈴木pro
4高橋pro
5伊藤free

EXISTSで同じ削除を書いて比べる

WHEREにEXISTSを書く方法でも、最初の節と同じ会員が削除されます。EXISTSは「一致する行があるか」だけを見る標準SQLの書き方で、他のDBでも使えます。結合先の値を使わず、存在だけで決まる削除ならEXISTSで十分です。USINGは結合先の列も条件に使いたいときに向いています。

sql
DELETE FROM members
WHERE EXISTS (
  SELECT 1
  FROM withdrawals AS w
  WHERE w.member_id = members.id
);

SELECT * FROM members ORDER BY id;
実行結果
idnameplan
1佐藤free
4高橋pro
5伊藤free

INのサブクエリで条件に合う申請者を削除する

INを使うと、サブクエリが返したid一覧に含まれる行を削除できます。サブクエリの中で理由を転居に絞れば、鈴木だけが消えます。INは一覧に入るかどうかだけを判定し、結合先の列を外側で使えません。結合条件を書き忘れる心配は無い一方、サブクエリが返す値にNULLがあるとNOT INでは結果が変わるため、否定の条件では次の節の書き方にします。

sql
DELETE FROM members
WHERE id IN (
  SELECT member_id
  FROM withdrawals
  WHERE reason = '転居'
);

SELECT * FROM members ORDER BY id;
実行結果
idnameplan
1佐藤free
3田中free
4高橋pro
5伊藤free

一致しない行を削除するにはNOT EXISTSを使う

USINGは結合に一致した行を削除するため、「別の表に行が無い会員」を消す書き方には向きません。この場合はNOT EXISTSを使います。ここではloginsに記録の無い田中(id 3)と伊藤(id 5)が消え、ログインのある佐藤・鈴木・高橋が残ります。佐藤は2回ログインしていますが、NOT EXISTSは存在するかどうかだけを見るので、重複は問題になりません。

sql
DELETE FROM members
WHERE NOT EXISTS (
  SELECT 1
  FROM logins AS l
  WHERE l.member_id = members.id
);

SELECT * FROM members ORDER BY id;
実行結果
idnameplan
1佐藤free
2鈴木pro
4高橋pro

DELETE … USINGでよくある間違い

結合条件を書き忘れると、全会員が削除される

USINGに表を書いてWHEREを省くと、結合条件が無いため、すべての会員とすべての申請の組み合わせが対象になります。withdrawalsに1行でもあれば、membersの全行が削除されます。エラーも警告も出ません。USINGを書いたら、更新先との結合条件が必ずあるかを確認し、実行前に同じ条件のSELECTで対象を確かめます。

sql
DELETE FROM members
USING withdrawals AS w;

SELECT * FROM members ORDER BY id;

NOT INのサブクエリにNULLが混じると、1行も削除されない

NOT INは、比較する一覧にNULLが1つでもあると、どの行に対してもtrueにならず、0行を返します。NULLを許す列に対して「一覧にない行を削除する」と書くと、削除件数が0件になっても気づきにくい誤りです。次の例では、一覧に1とNULLが入るだけで、どの会員も選ばれません。未一致の行はNOT EXISTSで書きます。

sql
SELECT *
FROM members
WHERE id NOT IN (
  SELECT 1
  UNION ALL
  SELECT NULL
);

実際に1問解いてみる

DELETE USING

reports に報告が1件でもある投稿を、posts から削除してください。投稿は post_id と id で対応します。

テーブル

読み込み中…

実行結果

関連構文

DELETE … USINGについてよくある質問

USINGの中でJOIN … ONは使えますか?
USINGにはSELECTのFROMと同じ書き方ができるので、JOINを使えます。ただし、JOINのONの中から削除先の表を参照することはできないため、削除先との結合条件はWHEREに書きます。
USINGとEXISTSはどちらを使うべきですか?
他の表の存在だけで決まる削除なら、標準SQLで他のDBへも移せるEXISTSが向いています。他の表の列の値を条件に使いたいときや、RETURNINGで他の表の列も返したいときは、USINGが書きやすくなります。
別の表に一致しない行を削除するにはどう書きますか?
USINGは一致した行を対象にするので、NOT EXISTSで「一致する行が存在しない」条件を書きます。NOT INは一覧にNULLが混じると結果が変わるため、NULLを許す列では使いません。
うっかり全行を削除してしまったら戻せますか?
トランザクションの中でBEGINから実行していれば、COMMITの前ならROLLBACKで取り消せます。COMMIT後は戻せないため、削除前に同じ条件のSELECTで件数を確かめる、バックアップを取る、といった準備が必要です。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。