NOT INをNOT EXISTSで書き直したい

質問

こんにちは。SQLでNOT INが実行速度が遅いため、EXISTSで書き直したいのですが、思っている結果がでません。

SQLのどこが間違っているかお教え頂けませんでしょうか？

■
・テーブル名：R_TABLE
・フィールド:id

データ：
id(ユニークではない)
001
002
003
003
003
007
008
009

■
・テーブル名：P_Master
・フィールド:id

データ：
id(ユニーク)
001
002
003

■やりたい事
R_TABLEのデータを条件によって削除したい。
条件はP_MASTERに無いR_TABLEの行を削除したいです。

■欲しい結果
R_TABLE
001
002
003
003
003

■書いたSQL
DELETE FROM R_TABLE WHERE R_TABLE.id NOT IN
(SELECT R_TABLE.id FROM R_TABLE INNER JOIN P_MASTER ON R_TABLE.id = P_MASTER.id)

これは正しい結果がでました。

書きなおしたSQL

DELETE FROM R_TABLE WHERE R_TABLE.id WHERE NOT EXISTS
(SELECT R_TABLE.id FROM R_TABLE INNER JOIN P_MASTER ON R_TABLE.id = P_MASTER.id)

結果はR_TABLEから何も消されていませんでした。
どこを修正すれば正しい結果が得られるでしょうか？

副問い合わせで、R_TABLEとP_MASTERのINNER JOINの結果が得られ、R_TABLEからINNER JOINに含まれないデータ削除しろという命令だと思っていたのですが違うみたいです。
どこを修正すればよろしいでしょうか？

よろしくお願いします。

回答No.3

yambejp

こんな感じでよくない？

DELETE R_TABLE FROM R_TABLE LEFT JOIN P_Master using(id) WHERE P_Master.id IS NULL

回答No.2

mpro-gram

not exists () は内部のサブクエリで１行もデータ取得でき無い時にのみtrue です。
NOT IN　で記述のサブクエリは、何行かのデータが有るはずなので、同じサブクエリをいれただけだと全部false（削除されない）になってしまうのは必然です。
しかし、mysqlでは、delete,update,insert のときには、サブクエリのfrom節に、編集対象テーブルを指定できないエラーがでると思うのですが、使ってるデータベースシステムは何ですか？SQLiteあたりだとエラーにならないようですが。

とりあえず、not exists ()を使うときには、外側クエリにおける各行のカラム値と比較するのが普通なので相関サブクエリとなります。以下はmysqlでも実行可能
DELETE FROM `R_TABLE` WHERE NOT EXISTS
(SELECT id FROM `P_MASTER` where R_TABLE.id = P_MASTER.id) ;

回答No.1

Siegrune

DELETE FROM R_TABLE WHERE R_TABLE.id WHERE NOT EXISTS
(SELECT R_TABLE.id FROM R_TABLE INNER JOIN P_MASTER ON R_TABLE.id = P_MASTER.id)
って「WHERE R_TABLE.id WHERE」のところでエラーにならなかったですか？
(MySQLはエラーにならないのかなあ？)

DELETE FROM R_TABLE AS x1 WHERE NOT EXISTS
(SELECT R_TABLE.id FROM R_TABLE AS x2
INNER JOIN P_MASTER
ON x2.R_TABLE.id = P_MASTER.id
AND x1.R_TABLE.id = x2.R_TABLE.id)

といった感じでうまくいくのでは？（MySQLがないので未検証ですが。）