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順に表示します。
| id | name | plan |
|---|---|---|
| 1 | 佐藤 | free |
| 2 | 鈴木 | pro |
| 3 | 田中 | free |
| 4 | 高橋 | pro |
| 5 | 伊藤 | free |
| member_id | reason |
|---|---|
| 2 | 転居 |
| 3 | 不満 |
| 3 | 料金 |
| 9 | 不明 |
| member_id | channel |
|---|---|
| 1 | app |
| 1 | web |
| 2 | web |
| 4 | app |
DELETE … USINGの基本構文
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の申請は、一致する会員がいないので何も起きません。
DELETE FROM members
USING withdrawals AS w
WHERE members.id = w.member_id;
SELECT * FROM members ORDER BY id;| id | name | plan |
|---|---|---|
| 1 | 佐藤 | free |
| 4 | 高橋 | pro |
| 5 | 伊藤 | free |
別の表の値で絞って削除する
USINGにした表の列は、WHEREの条件に使えます。結合条件にANDで足すと、「理由が料金の申請がある会員」のように、別の表の値で削除対象を絞れます。ここでは理由が料金の田中だけが消え、転居の申請がある鈴木は残ります。別の表の値を条件に使えることが、USINGを使う主な理由です。
DELETE FROM members
USING withdrawals AS w
WHERE members.id = w.member_id
AND w.reason = '料金';
SELECT * FROM members ORDER BY id;| id | name | plan |
|---|---|---|
| 1 | 佐藤 | free |
| 2 | 鈴木 | pro |
| 4 | 高橋 | pro |
| 5 | 伊藤 | free |
EXISTSで同じ削除を書いて比べる
WHEREにEXISTSを書く方法でも、最初の節と同じ会員が削除されます。EXISTSは「一致する行があるか」だけを見る標準SQLの書き方で、他のDBでも使えます。結合先の値を使わず、存在だけで決まる削除ならEXISTSで十分です。USINGは結合先の列も条件に使いたいときに向いています。
DELETE FROM members
WHERE EXISTS (
SELECT 1
FROM withdrawals AS w
WHERE w.member_id = members.id
);
SELECT * FROM members ORDER BY id;| id | name | plan |
|---|---|---|
| 1 | 佐藤 | free |
| 4 | 高橋 | pro |
| 5 | 伊藤 | free |
INのサブクエリで条件に合う申請者を削除する
INを使うと、サブクエリが返したid一覧に含まれる行を削除できます。サブクエリの中で理由を転居に絞れば、鈴木だけが消えます。INは一覧に入るかどうかだけを判定し、結合先の列を外側で使えません。結合条件を書き忘れる心配は無い一方、サブクエリが返す値にNULLがあるとNOT INでは結果が変わるため、否定の条件では次の節の書き方にします。
DELETE FROM members
WHERE id IN (
SELECT member_id
FROM withdrawals
WHERE reason = '転居'
);
SELECT * FROM members ORDER BY id;| id | name | plan |
|---|---|---|
| 1 | 佐藤 | free |
| 3 | 田中 | free |
| 4 | 高橋 | pro |
| 5 | 伊藤 | free |
一致しない行を削除するにはNOT EXISTSを使う
USINGは結合に一致した行を削除するため、「別の表に行が無い会員」を消す書き方には向きません。この場合はNOT EXISTSを使います。ここではloginsに記録の無い田中(id 3)と伊藤(id 5)が消え、ログインのある佐藤・鈴木・高橋が残ります。佐藤は2回ログインしていますが、NOT EXISTSは存在するかどうかだけを見るので、重複は問題になりません。
DELETE FROM members
WHERE NOT EXISTS (
SELECT 1
FROM logins AS l
WHERE l.member_id = members.id
);
SELECT * FROM members ORDER BY id;| id | name | plan |
|---|---|---|
| 1 | 佐藤 | free |
| 2 | 鈴木 | pro |
| 4 | 高橋 | pro |
DELETE … USINGでよくある間違い
結合条件を書き忘れると、全会員が削除される
USINGに表を書いてWHEREを省くと、結合条件が無いため、すべての会員とすべての申請の組み合わせが対象になります。withdrawalsに1行でもあれば、membersの全行が削除されます。エラーも警告も出ません。USINGを書いたら、更新先との結合条件が必ずあるかを確認し、実行前に同じ条件のSELECTで対象を確かめます。
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で書きます。
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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:DELETE — USING句の仕様、WHEREを省いたときの動作、削除件数、標準SQLとの互換性を確認しました。
- PostgreSQL公式:データの削除 — DELETEの基本的な考え方を確認しました。