关于笛卡尔积、全连接、union的测试
环境:
create table test111 (id number,name varchar2(20));
create table test222 (id number,name varchar2(20));
insert into test111 values(111,'aaa');
insert into test111 values(222,'bbb');
insert into test111 values(444,'ddd');
insert into test222 values(222,'bbb');
insert into test222 values(333,'ccc');
insert into test222 values(444,'eee');
insert into test222 values(444,'fff');
笛卡尔积
select * from test111,test222;
111 aaa 222 bbb
111 aaa 333 ccc
111 aaa 444 eee
111 aaa 444 fff
222 bbb 222 bbb
222 bbb 333 ccc
222 bbb 444 eee
222 bbb 444 fff
444 ddd 222 bbb
444 ddd 333 ccc
444 ddd 444 eee
444 ddd 444 fff
全连接
select * from test111 full join test222 on test111.id=test222.id;
222 bbb 222 bbb
444 ddd 444 eee
444 ddd 444 fff
111 aaa
333 ccc
等同于
select * from test111 inner join test222 on test111.id=test222.id
union all
select * from test111 left join test222 on test111.id=test222.id where test222.id is null
union all
select * from test111 right join test222 on test111.id=test222.id where test111.id is null;