我的关注 |
¥0 SQL中"inner join"和"outer join"的区别?
0

请问SQL中”inner join”和”outer join”的区别?

hong
修改
评论(0)
1个回答
0
采纳

假设有A B两个表,数据分别为:

A B
1 3
2 4
3 5
4 6

其中A和B中都有元素3,4。

inner join

inner join会取两个表相同的元素,如下:

  1. mysql: select * from a INNER JOIN b on a.a = b.b;
  2. 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匹配的元素

  1. mysql: select * from a LEFT OUTER JOIN b on a.a = b.b;
  2. 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匹配的元素

  1. mysql: select * from a RIGHT OUTER JOIN b on a.a = b.b;
  2. 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所有的元素。

  1. 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
采纳答案
大智若愚
修改
评论 (0)
撰写回答