我遇到的问题是，删除的数据稍后仍会出现在同一查询中。自然，在完全独立的查询中，不会显示已删除的数据。

这不是我的用例，但是我认为这是显示问题的最简单方法：

CREATE TABLE company (id INT PRIMARY KEY, name TEXT);
CREATE TABLE employee (id INT PRIMARY KEY, company_id INT REFERENCES company(id), name TEXT);

INSERT INTO company VALUES (1, 'first company');
INSERT INTO company VALUES (2, 'second company');

INSERT INTO employee VALUES (1, 1, 'first employee');
INSERT INTO employee VALUES (2, 2, 'second employee');

-- this select can successfully query for the data which has just been deleted
WITH deleted_employee AS (DELETE FROM employee WHERE id = 1 RETURNING id)
SELECT id, name FROM employee JOIN deleted_employee USING (id);

-- this select shows it has been deleted
SELECT * FROM employee;

我把它放在这里了。

DELETE在整个查询完成之前，似乎没有提交正义，这感觉很奇怪，因为优先级要求DELETE发生在之前SELECT。

有什么方法可以在单个查询中实现此目标吗？

编辑

答案已经回答了直接的问题。根本的问题是，如果没有更多与该公司关联的员工，则删除该员工然后删除其关联的公司。

这是我可以解决的查询：

WITH affected_company AS (DELETE FROM employee WHERE id = 1 RETURNING company_id)
DELETE FROM company
USING affected_company
WHERE NOT EXISTS (
  SELECT 1
  FROM employee
  WHERE company_id = affected_company.company_id
);

SELECT * FROM company;
SELECT * FROM employee;

和更新的小提琴。

您可以看到该公司没有被删除。