Apache Hive Join

通俗的讲：

A left join B 的连接的记录数与A表的记录数相同
A left join B 的连接额记录数与B表的记录数相同
A left join B 等价 B right join A

举个栗子：
假设a表和b表的数据是这个样的
a
id name
1 a
2 b
3 c
b
id score
1 15
2 20

SELECT * FROM a inner join b on a.id=b.id

这个语法是连接查询中的内连接，结果是两个表的匹配的记录出现在结果列表中
a.id name b.id score
1 a 1 15
2 b 2 20

SELECT * FROM a,b WHERE a.id=b.id

这个语法是内连接的另外一种写法，其执行结果与 inner join一样

SELECT * FROM a LEFT/RIGHT join b on a.id=b.id

这个是左外连接语法中的左外或者右外连接，如果是左外连接的话，他将显示a表的所有及记录

SELECT * FROM a.*,b.* FROM a LEFT JOIN b ON a.id=b.id

结果是这样的
a.id name b.id score
1 a 1 15
2 b 2 20
3 c null null
如果是右外连接的话，他将显示b表的所有记录

SELECT a.*,b.* FROM a RIGHT JOIN b ON a.id=b.id

查询结果是这样的
a.id name b.id score
1 a 1 15
2 b 2 20

SELECT a.*,b.* FROM a LEFT JOIN b on a.k=b.k
SELECT a.*,b.* FROM a LEFT OUTER JOIN b on a.k=b.k
--上面两种一样left join 是 left outer join 的简写

关系代数

合并数据集合的理论基础是关系代数，它是由E.F.Codd于1970年提出的。
在关系代数的形式化语言中：

• 用表、或者数据集合表示关系或者实体
• 用行表示元组
• 用列表示属性
  关系代数包含8个关系运算符

1. 选取-> 返回满足指定条件的行
2. 投影->从数据集合中返回指定的列
3. 笛卡尔积->关系的乘法，它将分别来自两个数据集合中的行以所有可能的方式进行组合
4. 并->关系的加法和剑法，它可以在行的方向上合并两个表中的数据，就像把一个表垒在另一个表之上一样
5. 交->返回两个集合共有的行
6. 差->返回只属于一个数据集合的行
7. 连接->在水平方向上合并两个表，其方法是：将两个表中在共同数据项上相互匹配的那些行合并起来
8. 除->返回两个数据集之间的精确匹配
   此外，SQL还提供了一种实现现代关系代数运算的方法

• 子查询->类似于连接，但是更灵活；在外部查询中，方式还可以使用表达式、列表、或者数据集合的地方都可以使用子查询的结果

连接类型

在关系代数中，连接运算是由一个笛卡尔积运算和一个选取运算构成的。首先用笛卡尔积完成对两个数据集合的乘运算，然后对生成的结果集合进行选取运算，确保只把分别来自两个数据集合并且具有重叠部分的行合并在一起。连接的全部意义在于在水平方向上合并两个数据集合（通常是表），并产生一个新的结果集合，其方法是将一个数据源中的行于另一个数据源中和它匹配的行组合成一个新元组。

SQL提供了多种类型的连接方式，它们之间的区别在于：从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。

连接类型 定义

• 内连接 只连接匹配的行

• 左外连接 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行

• 右外连接 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行

• 全外连接 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。

• (H)(theta)连接 使用等值以外的条件来匹配左、右两个表中的行

• 交叉连接 生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

如果FROM子句指定了多于一个表引用，则查询会连接来自多个表的行。连接条件指定各列之间（每个表至少一列）进行连接的关系。因为正在比较连接条件中的列，所以它们必须具有一致的数据类型。

SELECT语句的FROM子句可以指定以下几种类型的连接

FROM子句关键字 相应的结果集

CROSS JOIN 笛卡尔乘积（所有可能的行对）

INNER JOIN 仅对满足连接条件的CROSS中的列

LEFT OUTER JOIN 一个表满足条件的行，和另一个表的所有行

RIGHT OUTER JOIN 与LEFT相同，但两个表的角色互换

FULL OUTER JOIN LEFT OUTER 和 RIGHT OUTER中所有行的超集

1. 内连接
ANSI SQL - 92 标准

SELECT *
FROM STUDENT I
INNER JOIN SCORE T
ON I.STU_ID = T.STU_ID
WHERE I.STU_ID = '0001'

2. 外连接
2.1 左外连接

SELECT * 
FROM STUDENT I
LEFT OUTER JOIN SCORE T
ON I.STU_ID = B.STU_ID

2.2 右外连接

SELECT * 
FROM STUDENT I
RIGHT OUTER JOIN SCORE T
ON I.STU_ID = T.STU_ID

2.3 全外连接

SELECT * 
FROM STUDENT I
FULL OUTER JOIN SCORE T
ON I.STU_ID = T.STU_ID

2.4 自链接
自身连接是指同一表自己于自己进行连接。这种连接通常用于自反关系（也称递归关系）中抽取数据。例如人力资源数据库中雇员和老板的关系

select s.inst_no superior_inst, s.inst_name sup_inst_name, i.inst_no, i.inst_name
from t_institution i
join t_institution s
on i.superior_inst = s.inst_no
--结果是
superior_inst sup_inst_name        inst_no    inst_name
800           北京市               5801       朝阳区
800           北京市               5802       昌平区
800           北京市               5803       顺义区

2.5 交叉（无限制）连接
交叉连接用于对两个源表进行纯关系代数的乘运算。它不使用连接条件来限制结果集合，而是将分别来自两个数据源中的行以所有可能的方式进行组合。数据集合中一的每个行都要与数据集合二中的每一个行分别组成一个新的行。例如，如果第一个数据源中有5个行，而第二个数据源中有4个行，那么在它们之间进行交叉连接就会产生20个行。人们将这种类型的结果集称为笛卡尔乘积。

大多数交叉连接都是由于错误操作而造成的；但是它们却非常适合向数据库中填充例子数据，或者预先创建一些空行以便为程序执行期间所要填充的数据保留空间。

select *
from  t_institution i
cross join t_teller t

在交叉连接中没有on条件子句