自然连接实验分析

 1.自然连接实验

create table test_sp(sno int,pno int);
insert into test_sp(sno,pno) values(1,1);
insert into test_sp(sno,pno) values(1,2);
insert into test_sp(sno,pno) values(2,1);
select * from test_sp

create table test_pj(pno int,jno int);
insert into test_pj(pno,jno) values(1,2);
insert into test_pj(pno,jno) values(2,1);
insert into test_pj(pno,jno) values(1,1);
select * from test_pj

 

 

create table test_js(jno int,sno int);
insert into test_js(jno,sno) values(2,1)
insert into test_js(jno,sno) values(1,1)
insert into test_js(jno,sno) values(2,2)
select * from test_js

 

select sno,pno,jno from test_sp natural join test_pj natural join test_js

 2.分析

select sno,pno,jno from test_sp natural join test_pj
select jno,sno,pno from test_sp natural join test_pj
select jno,sno,pno from test_sp natural join test_pj natural join test_js

补记说明

上图，两个表的自然连接就是根据共性pno的组合。再和第三个表的合并，其实就是消除组合后的表的jno+sno,在第3个表中没有的组合。