选择表1中的所有行,以及表2中不在表1中的行

yvt65v4c  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(332)

我尝试了很多解决方案(联合,联合等),但到目前为止,我没有得到想要的结果,现在我只是困惑。。。
我有两张table:
表1

ID - Name 
0 - John 
1 - Jack 
2 - Anna 
3 - Liam 
4 - Luke

表2

ID - FullName - City 
0 - John Smith - New York 
1 - Jack Smith - Houston 
2 - Anna Smith - Houston 
'' - Owen Smith - Chicago 
'' - Rosa Smith - Chicago

有些行可能在表1中,但不在表2中;有些行在表2中,但不在表1中。
我需要选择表1的所有行和表2中的所有行减去两个表中与id匹配的行,作为结果,我需要所有需要的列来知道结果来自哪里。
比如:
结果:

ID - Name - FullName - City 
0 - John - John Smith - '' (name!='' so i know it's from table1) 
1 - Jack - Jack Smith - '' (name!='' so i know it's from table1) 
2 - Anna - Anna Smith - '' (name!='' so i know it's from table1) 
3 - Liam - '' - '' (name!='' so i know it's from table1) 
4 - Luke - '' - '' (name!='' so i know it's from table1) 
'' - '' - Owen Smith - Dallas (name='', city!='' so i know it's from table2) 
'' - '' - Rosa Smith - Las Vegas (name='', city!='' so i know it's from table2)

我希望我举的例子足够清楚,谢谢。

v6ylcynt

v6ylcynt1#

假设两个表通过各自的 id ,这应该是您想要的:

select id, name, t2.fullName, t2.city
from table1 t1
left join table2 t2 on t2.id = t1.id
union all
select t2.id, null, t2.fullName, t2.city
from table2 t2
where not exists (select 1 from table1 t1 where t1.id = t2.id)

另一种选择是聚合:

select id, max(name) name, max(fullName) fullName, max(city) city
from (
    select id, name, null fullName, null city from table1 
    union all
    select id, null, fullName, city from table2
) t
group by id

相关问题