¥0
SQL中"inner join"和"outer join"的区别?
1个回答
0
采纳
假设有A B两个表,数据分别为:
A | B |
---|---|
1 | 3 |
2 | 4 |
3 | 5 |
4 | 6 |
其中A和B中都有元素3,4。
inner join
inner join会取两个表相同的元素,如下:
mysql: select * from a INNER JOIN b on a.a = b.b;
oracle: select a.*,b.* from a,b where a.a = b.b;
结果:
a | b |
---|---|
3 | 3 |
4 | 4 |
left outer join
a left outer join b
会显示a中所有的元素,b列只列出和a匹配的元素
mysql: select * from a LEFT OUTER JOIN b on a.a = b.b;
oracle: select a.*,b.* from a,b where a.a = b.b(+);
结果:
a | b |
---|---|
1 | null |
2 | null |
3 | 3 |
4 | 4 |
Right outer join
a left outer join b
会显示b中所有的元素,a列只列出和a匹配的元素
mysql: select * from a RIGHT OUTER JOIN b on a.a = b.b;
oracle: select a.*,b.* from a,b where a.a(+) = b.b;
结果:
a | b |
---|---|
3 | 3 |
4 | 4 |
null | 5 |
null | 6 |
Full outer join
a left outer join b
会列出a和b所有的元素。
select * from a FULL OUTER JOIN b on a.a = b.b;
结果:
a | b |
---|---|
1 | null |
2 | null |
3 | 3 |
4 | 4 |
null | 6 |
null | 5 |
撰写回答