“INNER JOIN”之间的区别是什么？和“OUTER JOIN”？ [英] What is the difference between "INNER JOIN" and "OUTER JOIN"?

问题描述

此外，如何 LEFT JOIN ， RIGHT JOIN 和 FULL JOIN fit in？

解决方案

假设你加入的列没有重复，这是一个很常见的情况： / p>

• A和B的内部连接给出A的交叉B的结果，即 Venn diagram intersection。




• A和B给出了A联合B的结果，即Venn图联合的外部部分。

示例

假设您有两个表格，每个表格各有一列，数据如下：

  AB 
  -   -  
 1 3 
 2 4 
 3 5 
 4 6 
  
 
 
 内部连接 
 
 
 
查询给出两个表的交集，即它们共有的两行。
  select * from an INNER JOIN b on aa = bb; 
从a，b中选择a。*，b。*其中a.a = b.b; 
 
 a | b 
  -  +  -  
 3 | 3 
 4 | 4 
  
 左外连接 
 
 
 
左外连接将给出A中的所有行，加上B中的任何公共行。
  select * from aa = bb上的LEFT OUTER JOIN b; 
从a，b中选择a。*，b。* a.a = b.b（+）; 
 
 a | b 
  -  + ----- 
 1 | null 
 2 | null 
 3 | 3 
 4 | 4 
  
 右外连接 
 
 
 
右外连接将给出B中的所有行，以及A中的所有公共行。
  select * from a a = bb上的右外连接b; 
从a，b中选择a。*，b。* a.a（+）= b.b; 
 
 a | b 
 ----- + ---- 
 3 | 3 
 4 | 4 
 null | 5 
 null | 6 
  
 全外连接 
 
 
 
一个完整的外连接将给你A和B的联合，即A中的所有行和B中的所有行。如果A中的某个东西在B中没有对应的数据，那么B部分
 从a全部外部加入b选择* aa = bb; 
 
 a | b 
 ----- + ----- 
 1 | null 
 2 | null 
 3 | 3 
 4 | 4 
 null | 6 
 null | 5 
  
 
Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?
解决方案
Assuming you're joining on columns with no duplicates, which is a very common case:


An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.


Examples

Suppose you have two tables, with a single column each, and data as follows:
A    B
-    -
1    3
2    4
3    5
4    6
Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.
select * from a INNER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4
Left outer join

A left outer join will give all rows in A, plus any common rows in B.
select * from a LEFT OUTER JOIN b on a.a = b.b;
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 right outer join will give all rows in B, plus any common rows in A.
select * from a RIGHT OUTER JOIN b on a.a = b.b;
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 full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.
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


                        
这篇关于“INNER JOIN”之间的区别是什么？和“OUTER JOIN”？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！