postgresql - PostgreSQL SELECT query using same structure as a DELETE query

Question

With MySQL, I perform a delete with a join as follows:

DELETE t1
FROM t1
LEFT OUTER JOIN t2 ON t2.fk_id = t1.id
WHERE t2.id IS NULL;

Before executing the query, I would typical make sure I am okay with the outcome first by viewing the soon to be deleted records as follows:

SELECT t1.*
FROM t1
LEFT OUTER JOIN t2 ON t2.fk_id=t1.id
WHERE t2.id IS NULL;

Using PostgreSQL, I believe I should perform the same delete action as follows (EDIT below):

DELETE FROM t1
USING t2
WHERE t1.id = t2.fk_id
AND t2.id IS NULL;

Concerned that I might not be correct, I unsuccessfully tried the following:

SELECT t1.*
USING t2
WHERE t2.fk_id=t1.id
AND t2.id IS NULL;

Is there a way with PostgreSQL to perform a SELECT query using the same structure as a DELETE query?

EDIT - Not even correct since I am effectively performing an INNER JOIN and will not delete any records, and will need to change it as follows. Still would appreciate an answer to my original question.

DELETE FROM t1  
USING t1 t_1
LEFT OUTER JOIN t2 ON t2.fk_id=t_1.id
WHERE t1.id=t_1.id AND t2.id IS NULL;

标签： postgresqlsql-delete

Answer 1

我会使用 NOT EXISTS 条件：

delete from t1
where not exists (select *
                  from t2
                  where t1.id = t2.fk_id);

这通常比连接快，而且它也是符合标准的 SQL。