“INNER JOIN"和“INNER JOIN"有什么区别?和“外连接"? [英] What is the difference between "INNER JOIN" and "OUTER JOIN"?

问题描述

还有 LEFT JOIN、RIGHT JOIN 和 FULL JOIN 是如何适应的?

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:

• A 和 B 的内部连接给出了 A 与 B 相交的结果，即 维恩图 交集.

A 和 B 的外连接给出 A 联合 B 的结果，即维恩图联合的外部部分.

An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

示例

假设您有两个表，每个表只有一列，数据如下:

Suppose you have two tables, with a single column each, and data as follows:

A    B
-    -
1    3
2    4
3    5
4    6

请注意，(1,2) 是 A 独有的，(3,4) 是通用的，而 (5,6) 是 B 独有的.

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

内连接

使用任一等效查询的内部联接给出两个表的交集，即它们共有的两行.

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

左外连接

左外连接将给出 A 中的所有行，以及 B 中的所有公共行.

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

右外连接

右外连接将给出 B 中的所有行，以及 A 中的所有公共行.

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

全外连接

一个完整的外连接会给你 A 和 B 的联合，即 A 中的所有行和 B 中的所有行.如果 A 中的某些内容在 B 中没有相应的数据，则 B 部分为空，反之亦然.

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"和“INNER JOIN"有什么区别?和“外连接"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！