关于多表连接的简单说明

根据你要获取的具体id作为基础表进行关联

create table test1
( id number primary key ,
col2 varchar2(10));
 insert into test1 values (1,'1'); 
 insert into test1 values (2,'2');
 insert into test1 values (3,'3');
create table test2(
id number primary key,
t1id number references test1(id),
col2 varchar2(10));
insert into test2 values (1,1,'1');
insert into test2 values (2,1,'2');
insert into test2 values (3,2,'3');
insert into test2 values (4,3,'4');
delete from test2 where id=4;
commit;
create table test3(
id number primary key,
col varchar2(10),
t2id number references test2(id));
insert into test3 values(1,'1',1);
insert into test3 values (2,'1',1);
insert into test3 values (3,'3',2);
insert into test3 values (4,'4',3);
delete from test3 where id=4;
commit;
commit;

我们来试验以下查询：select t1.id as idt1,t2.id as idt2 from test1 t1 left join test2 t2 on t1.id=t2.t1id;
--保留t1中所有主键的值，即id的值
select t1.id as idt1,t2.id as idt2,t3.id as idt3 from test1 t1 left join test2 t2 on t1.id=t2.t1id left join test3 t3 on t3.t2id=t2.id;
--前一个连接保留t1中所有主键的值，第二个连接将保留t3中所有外键的值，也会保留t3中所有id的值，且不会多余
select t1.id as idt1,t2.id as idt2,t3.id as idt3 from test1 t1 left join test2 t2 on t1.id=t2.t1id right join test3 t3 on t3.t2id=t2.id;
select t1.id as idt1,t2.id as idt2 from test1 t1 right join test2 t2 on t1.id=t2.t1id;
--以下保留t2中所有外键的值，所以t2中的id也都会被保留下来且不会多。
select t1.id as idt1,t2.id as idt2,t3.id as idt3   from test1 t1 right join test2 t2 on t1.id=t2.t1id left join test3 t3 on t3.t2id=t2.id;
select * from test2 t2 left join test1 t1 on t1.id=t2.t1id left join test3 t3 on t3.t2id=t2.id;
--保留t3中所有外键的值，所以也会保留t3中所有id，且不会有多余
select t1.id as idt1,t2.id as idt2,t3.id as idt3   from test1 t1 right join test2 t2 on t1.id=t2.t1id right join test3 t3 on t3.t2id=t2.id;
--保留t2中所有外键的值，所以也会保留t2中所有id的值，qie不会多余
select t1.id as idt1,t2.id as idt2,t3.id as idt3   from test2 t2 left join test1 t1 on t1.id=t2.t1id left join test3 t3 on t3.t2id=t2.id;
--保留t3中所有外键的值，且不会有多余
select t1.id as idt1,t2.id as idt2,t3.id as idt3   from test3 t3 left join test2 t2 on t3.t2id=t2.id left join test1 t1 on t2.t1id=t1.id;
--第一次联接保留t2中所有外键的值，所以自然也保留了id的值，第二次连接保留t2中所有主键的值，且不会有多余
select t1.id as idt1,t2.id as idt2,t3.id as idt3   from test3 t3 right join test2 t2 on t3.t2id=t2.id left join test1 t1 on t2.t1id=t1.id;

以上基础表不同查询结果也是不同的