笛卡尔积与全连接

建表：
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');
commit;

笛卡尔积
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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23135684/viewspace-624978/，如需转载，请注明出处，否则将追究法律责任。