sql连接查询包括：inner join、left join、right join和full join。连接查询有时作为中间视图处理结果的语句，会由于数据集重复导致最终的数据统计结果不正确。解决：必须确保连接条件的唯一性。参考：https://www.cnblogs.com/lijingran/p/9001302.html
前置数据准备
商品表（goods） 类别表（sort）
连接条件不唯一，导致结果集数据可能发生重复，导致结果不正确
inner join：保留两张表中匹配的结果集
select * from goods g
inner join sort s
on
g
.sortcode
= s
.scode
;
left join: 保留左表所有的行，即使右表没有匹配的记录
select * from goods g
left join sort s
on
g
.sortcode
= s
.scode
;
right join：保留右表所有的行，即使右表没有匹配的记录
select * from goods g
right join sort s
on
g
.sortcode
= s
.scode
;
full join：返回包括左右表中不匹配的记录
select * from goods g
full join sort s
on
g
.sortcode
= s
.scode
;
连接条件唯一时
inner join
select * from goods g
inner join sort s
on
g
.sortcode
= s
.scode
and
g
.sortname
= s
.sname
;
left join
select * from goods g
left join sort s
on
g
.sortcode
= s
.scode
and
g
.sortname
= s
.sname
;
right join
select * from goods g
right join sort s
on
g
.sortcode
= s
.scode
and
g
.sortname
= s
.sname
;
full join
select * from goods g
full join sort s
on
g
.sortcode
= s
.scode
and
g
.sortname
= s
.sname
;
sql脚本
CREATE TABLE goods
(
gid
varchar(32) NOT NULL,
gcode
varchar(30) NOT NULL,
gname
varchar(100) NOT NULL,
price
numeric(20, 4) NOT NULL,
sortcode
varchar(30) NOT NULL,
sortname
varchar(100) NOT NULL,
PRIMARY KEY (gid
)
);
CREATE TABLE sort
(
sid
varchar(32) NOT NULL,
scode
varchar(30) NOT NULL,
sname
varchar(100) NOT NULL,
PRIMARY KEY (sid
)
);
INSERT INTO "public"."goods"("gid", "gcode", "gname", "price", "sortcode", "sortname") VALUES ('g0001', '0001', '康师傅可口可乐', '3.0000', '100002', '碳酸饮料A');
INSERT INTO "public"."goods"("gid", "gcode", "gname", "price", "sortcode", "sortname") VALUES ('g0002', '0002', '百世可乐', '4.0000', '100002', '碳酸饮料B');
INSERT INTO "public"."goods"("gid", "gcode", "gname", "price", "sortcode", "sortname") VALUES ('g0003', '0003', '酸奶', '2.0000', '100001', '牛奶');
INSERT INTO "public"."goods"("gid", "gcode", "gname", "price", "sortcode", "sortname") VALUES ('g0004', '0004', '雪碧', '3.5000', '100003', '碳酸饮料C');
INSERT INTO "public"."sort"("sid", "scode", "sname") VALUES ('s001', '100001', '牛奶');
INSERT INTO "public"."sort"("sid", "scode", "sname") VALUES ('s002', '100002', '碳酸饮料A');
INSERT INTO "public"."sort"("sid", "scode", "sname") VALUES ('s003', '100002', '碳酸饮料B');
INSERT INTO "public"."sort"("sid", "scode", "sname") VALUES ('s005', '200005', '面食');
