Sql杂货铺

select * from A left join B on …以A为主表
select * from A right join B on …以B为主表

oracle

左、右连接

1
2
3
4
5
6
7
8
9
10
11
select * from student s, class c where s.cid = c.id(+);
select * from student s left join class c on s.cid=c.id;

select * from student s, class c where s.cid(+) = c.id;
select * from student s right join class c on s.cid = c.id;

select * from student s, class c where s.cid(+) = c.id and s.age < 20;
select * from student s right join class c on s.cid = c.id where s.age < 20;

select * from student s, class c where s.cid(+) = c.id and s.age(+) < 20;
select * from student s right join class c on s.cid = c.id and s.age < 20;

其中

1
select * from student s, class c where s.cid(+) = c.id and s.age < 20;

与

1
select * from student s, class c where s.cid(+) = c.id and s.age(+) < 20;

的区别在于前者先按age<20扫描student表，然后内连接class表。后者扫描class表，按条件与student表右连接。