postgres中执行DELETE … LEFT/RIGHT JOIN

问题：有两张表 users, addrs，需要从users表中删除一些行，条件为在“addrs”表中没有对应id的行。

事实上很简单，只是个left join查询，SELECT a.*,'|' s,b.* FROM users a LEFT JOIN addrs b ON a.id=b.id;，预览如下：

然而，postgres本身不支持 DELETE … LEFT JOIN … 这样的语句，这点不像MySQL那样方便。

有一个曲线救国方法：

把上面LEFT JOIN的结果表视为一张中间表，把原users表、按主键 inner join 到中中间表，然后从users表中按删除指定行。语句如下

DELETE FROM users
USING users a LEFT JOIN addrs b ON a.id=b.id
WHERE users.id=a.id AND b.name IS NULL;

问题得解。

当然也可以使用子查询，从中间表中取出id主键，以子查询形式删除。不推荐。

前文用表及数据

CREATE TABLE users (
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    name character varying(255)
);
ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY (id);

CREATE TABLE addrs (
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    addr character varying(255)
);
ALTER TABLE addrs ADD CONSTRAINT addrs_pkey PRIMARY KEY (id);

INSERT INTO "users" VALUES (1, 'Tom');
INSERT INTO "users" VALUES (2, 'Jarry');
INSERT INTO "users" VALUES (3, 'Alias');
INSERT INTO "users" VALUES (4, 'Bob');
INSERT INTO "users" VALUES (5, 'somebody');

INSERT INTO "addrs" VALUES (1, 'Luoyang');
INSERT INTO "addrs" VALUES (2, 'Shanghai');
INSERT INTO "addrs" VALUES (3, 'Hongkong');

EOF.

Last Updated on 2021/07/02