postgres中执行DELETE ... LEFT/RIGHT JOIN

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

table-users

table-users

table-addrs

table-addrs

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

overview-join

overview-join

然而,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.

 

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据