SQL获取失配的行

最新推荐文章于 2024-12-02 03:00:00 发布

波特王子

最新推荐文章于 2024-12-02 03:00:00 发布

阅读量579

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库开发 Greenplum相关文章标签： sql insert table join c null

本文链接：https://blog.youkuaiyun.com/sptoor/article/details/7644726

数据库开发同时被 2 个专栏收录

93 篇文章

订阅专栏

Greenplum相关

35 篇文章

订阅专栏

本文介绍了一种使用SQL查询两个表中不匹配行的方法。通过左连接和条件过滤，可以找出test_temp表中不存在于test_duplicate表中的记录。文章还展示了如何利用EXCEPT操作符来实现相同目的。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL获取失配的行

drop table test_duplicate;
create table test_duplicate(
a int,
b int,
c int
);
create table test_temp(
a int,
b int,
d int
);
insert into test_temp values(1,2,5);
insert into test_temp values(1,2,5);
insert into test_temp values(3,8,5);

insert into test_duplicate values(1,2,3);
insert into test_duplicate values(1,2,3);
insert into test_duplicate values(1,2,5);
insert into test_duplicate values(1,1,1);
insert into test_duplicate values(2,2,3);
insert into test_duplicate values(2,2,5);

select * from test_temp;
select distinct * from test_temp;

select t3.c1, t3.c2, t3.c3 from(
select t1.a as c1, t1.b as c2, t1.d as c3, t2.a as c4, t2.b as c5, t2.c as c6
 from test_temp as t1 left join test_duplicate as t2
on (t1.a=t2.a and t1.b=t2.b)
)  t3
where t3.c4 is null
;

select * from test_temp as t1
left join test_duplicate as t2
on (t1.a=t2.a and t1.b=t2.b)
where t2.a is null and t2.b is null;

select * from test_temp
where (a,b) in
(
(select a, b from test_temp)
except
(select a, b from test_duplicate)
);

test_temp:

test_duplicate: