table1和table2各有一些记录相关,
select * from table1 left join table2 on table1.id = table2.id
之后,table2可能还剩了一些匹配不上的记录,
现在想把这些记录获取到,追加到查询结果后面,
该如何写呢
select * from table1 left join table2 on table1.id = table2.id
之后,table2可能还剩了一些匹配不上的记录,
现在想把这些记录获取到,追加到查询结果后面,
该如何写呢
select * from table1 full join table2 on table1.id = table2.id
如果你想追加到查询结果后面,可以拍下序 order by table1.id nulls last
select * from table1 left join table2 on table1.id = table2.id
union
select * from table1 right join table2 on table1.id = table2.id
--or
select * from table1 full outer join table2 on table1.id = table2.id
create table ta(a int,b int,c int);
create table tb(d int);insert into ta (A, B, C) values (2, 2, 2);insert into ta (A, B, C)
values (3, 3, 3);insert into tb (D)
values (1);insert into tb (D)
values (2);insert into tb (D)
values (3);insert into tb (D)
values (4);select * from ta a, tb b where a.a=b.d(+)
union
select * from ta a, tb b where a.a(+)=b.d
;
<==>
select * from ta left join tb on ta.a=tb.d
union
select * from ta right join tb on ta.a=tb.d;
or
select * from ta a full outer join tb b on a.a=b.d;
/*
A B C D
2 2 2 2
3 3 3 3
1
4
*/