PostgreSQL学习笔记-3.基础知识：CROSS、INNER、LEFT OUTER、RIGHT OUTER、FULL OUTER、UNION

PostgreSQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

在 PostgreSQL 中，JOIN 有五种连接类型：

CROSS JOIN ：交叉连接
INNER JOIN：内连接
LEFT OUTER JOIN：左外连接
RIGHT OUTER JOIN：右外连接
FULL OUTER JOIN：全外连接

 

交叉连接

交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。

cross join 称为“交叉连接”或者“笛卡尔连接”。sql cross join 连接用于从两个或者多个连接表中返回记录集的笛卡尔积，即将左表的每一行与右表的每一行合并。
什么是笛卡尔积？
笛卡尔积（cartesian product）是指两个集合 a 和 b 的乘积。

例如，a 集合和 b 集合分别包含如下的值：

A = {1,2}
B = {3,4,5}

A×B 和 B×A 的结果集分别表示为：

A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };

A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。

从以上结果可以看出：

• 笛卡尔积不满足交换率，即 A×B≠B×A。
• 笛卡尔积的元素个数 = A 集合元素个数 × B 集合元素个数。

由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。

笛卡尔连接有两种语法，可以使用 cross join 关键字，也可以使用不带 where 子句的 select from 命令，如下所示：

#第一种写法
select table1.column1, table2.column2...
from table1 cross join table2

#第二种写法
select table1.column1, table2.column2...
from  table1, table2

第一种写法见名知意，是 sql 标准推荐的写法。

之所以会产生笛卡尔积，是因为以上两种写法既没有使用 where 子句也没有使用 on 子句，数据库引擎不知道根据什么条件来连接两个表，也不知道根据什么条件来筛选结果集，只能返回笛卡尔积。如果给 cross join 加上 on 子句或者 where 子句，它返回的结果和 inner join 是一样的。

cross join 需要把两个表的每一行都一一合并，并产生一个结果集，这个结果集可能会非常巨大。在生产环境中，一个表有一万条记录非常普遍，两个这样的表进行 cross join 连接，结果集就包含10000 * 10000 = 10000 0000 = 1亿条记录，数据库引擎将花费大量的时间和资源去创建和处理这个结果集。

如非必要，请慎用 cross join 连接！

 

内连接（INNER JOIN）

外部连接是内部连接的扩展。SQL 标准定义了三种类型的外部连接: LEFT、RIGHT 和 FULL, PostgreSQL 支持所有这些。

 

左外连接（ LEFT OUTER JOIN ）

对于左外连接，首先执行一个内连接。然后，对于表 T1 中不满足表 T2 中连接条件的每一行，其中 T2 的列中有 null 值也会添加一个连接行。因此，连接的表在 T1 中每一行至少有一行。

右外连接（ RIGHT OUT JOIN）语法

首先，执行内部连接。然后，对于表T2中不满足表T1中连接条件的每一行，其中T1列中的值为空也会添加一个连接行。这与左联接相反;对于T2中的每一行，结果表总是有一行。

外连接

全连接(FULL OUTER JOIN)

首先，执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。

PostgreSQL UNION

PostgreSQL UNION 操作符合并两个或多个 SELECT 语句的结果。

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

 

UNION ALL 子句

UNION ALL 操作符可以连接两个有重复行的 SELECT 语句，默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。