【SQL】JOIN 连接：内连接、外连接、交叉连接、自连接、等值连接、自然连接

基本概念

关系模型（表）

关系模型由关系数据结构、关系操作集合和关系完整性约束三部分组成。

关系模型的数据结构非常简单：一张扁平的二维表。

• 元组：二维表中的具有相同数据类型的某一行
• 属性：二维表中的具有相同数据类型的某一列
• 笛卡尔积(Cartesian product)：又称直积，分别用集合A和集合B的一个元素作第一、第二元素构成有序对，所有这样的有序对的集合称为A和B的笛卡尔积，记作\(A×B\)。
  其符号化表示为：\(A×B = {<x, y> | x∈A∧y∈B}\)。
  易证：若\(|A| = m\)，\(|B| = n\)，则\(|AB| = m*n\)。（\(|A|\)表示集合A的模，即 集合A中元素的个数为m个）

例如：
\(A={a, b}\), \(B={0, 1, 2}\)，则
\(A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}\)
\(B×A={(0, a), (0, b), (1, a), (1, b), (2, a), (2, b)}\)

---

• 表：是实实在在地保存数据的实体，写入的数据都保存在表中
• 视图：是一个虚拟表，其内容由查询定义。
  同真实的表一样，视图包含一系列带有名称的列和行数据；但是，视图并不在数据库中以存储的数据值集形式存在。
  行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

码

• 候选码：若关系中的一个属性或属性组的值能够唯一地标识一个元组，且他的真子集不能唯一的标识一个元组，则称这个属性或属性组做候选码。

• 主键：如果在一个关系中，有多个候选码可以选择，则选定其中的一个作为该关系的主键。
  它的值用于唯一地标识表中的某一条记录。主关键字是一种唯一关键字。

• 码：是一个或多个属性的集合。

• 超码：是一个或多个属性的集合，超码中的这些属性可以让我们在一个实体集中唯一地标识一个实体。

注意：虽然超码可以唯一标识一个实体，但是可能大多数超码中含有多余的属性。所以我们需要候选码。

• 候选码：是极小的超码集，也就是它的任意真子集都不是超码，而他本身是超码。
• 主码：是被选中用来在一个关系中区分不同元组的候选码。

---

我来举个例子吧：
学生表（学号，身份证号，姓名，性别）

• 超码：****（学号，性别）→（姓名）。学号和性别能唯一标识姓名一点问题都没有，但是其实标识姓名，只用学号就能标识了，不需要性别。
• 候选码：****（学号）→（姓名），（身份证号）→（姓名）。学号或身份证号都能唯一标识姓名。
• 主码：****（学号）→（姓名）。这是人为选择的，其实身份证号也能做主码。

连接

多表查询经常用到连接，各种连接之间的区别应该注意总结。

首先大概认识各种连接的关系和由来：
表之间的连接常有以下两种：

• 以JOIN关键字指定的连接，T-SQL扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有所增强，以JOIN关键字指定的连接有三种类型：内连接、外连接、交叉连接（笛卡尔积）。

• 在SELECT语句的WHERE子句中使用比较运算符给出连接条件，对表进行连接，将这种表示形式称为连接谓词表示形式。连接谓词中的比较符可以是<、<=、=、>、>=、!=、<>、!< 和 !>，当比较符为“=”时，就是等值连接，等值连接的结果中有重复列，在目标列中去除相同的字段名就是自然连接。

JOIN 连接

所谓"连接JOIN"，就是两张表根据关联字段（就是ON后面的关联条件，一般把JOIN表的字段放在前面，方便看），组合成一个数据集。
格式：表A JOIN 表B ON AB的关联条件 JOIN 表C AC或AB的关联条件

• 内连接(inner join)：（以左右表内匹配的记录为主）表示只包含匹配的记录。只返回两张表匹配的记录。
• 外连接(outer join)：表示还包含不匹配的记录。
  • 左连接(left join)：（以左表所有的记录为主）又称左外连接，返回匹配的记录，以及表A多余不匹配的记录。
  • 右连接(right join)：（以右表所有的记录为主）又称右外连接，返回匹配的记录，以及表B多余不匹配的记录。
  • 全连接(full join)：（以两个表所有的记录为主）又称全外连接，返回匹配的记录，以及表A和表B各自多余不匹配的记录。
• 交叉连接(cross join)：即 做笛卡尔积运算。
  表A和表B不存在关联字段，这时表A（共有n条记录）与表B（共有m条记录）连接后，一对一组合配对，会产生一张包含n*m条记录的新表，返回新表。

上图中，表A的记录是123，表B的记录是ABC，颜色表示匹配关系。返回结果中，如果另一张表没有匹配的记录，则用null填充。

注意：多表连接查询会比直接使用自带的API查询表中的一个属性，再根据属性查询另一个表，一个一个查询来得快。所以，我们要善用多表连接查询。

实例

学生表：

• s_id：学生学号
• s_name：学生名称
• s_class：学生班级

老师表：

• t_id：老师id
• t_class：老师管理的班级
• t_name：老师名称

内连接(INNER JOIN)

内连接：,，JOIN，INNER JOIN
如果输入JOIN，那么默认就是INNER JOIN内连接

/* 内连接 */
SELECT 
	* 
FROM 
	student 
INNER JOIN teacher ON 
	s_class=t_class

/* 我比较喜欢这种，但是上面那种效率会更高，因为 JOIN 的优先级高于 “逗号,”，所以如果可以的话，尽量使用上面的 */
SELECT 
	* 
FROM 
	student, 
	teacher 
WHERE 
	s_class=t_class

补充：
在使用 join 时，on 和 where 条件的区别如下：
1、on 条件是在生成临时表时使用的条件，它不管 on 上的条件是否为真都会返回 left 或 right 表中的记录，full 则具有 left 和 right 的特性的并集。
2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边或者右边表的记录）了，条件不为真的就全部过滤掉。

而 inner jion 没这个特殊性，则条件放在 on 中和 where 中，返回的结果集是相同的。

外连接(OUTER JOIN)

左外连接(LEFT JOIN)

左外连接：LEFT JOIN，LEFT OUTER JOIN

/* 左连接 */
SELECT 
	* 
FROM 
	student 
LEFT JOIN teacher ON 
	s_class=t_class

右外连接(RIGHT JOIN)

右外连接：RIGHT JOIN，RIGHT OUTER JOIN

/* 右连接 */
SELECT 
	* 
FROM 
	student 
RIGHT JOIN teacher ON 
	s_class=t_class

全外连接(FULL JOIN)

全外连接：FULL JOIN，FULL OUTER JOIN

注意：Oracle数据库支持full join，mysql是不支持full join的，但仍然可以同过左外连接+union+右外连接实现。

/* 全连接（不适用于MYSQL） */
SELECT 
	* 
FROM 
	student 
FULL JOIN teacher ON 
	s_class=t_class

/* 全连接 */
SELECT * FROM student LEFT JOIN teacher ON s_class=t_class
UNION
SELECT * FROM student RIGHT JOIN teacher ON s_class=t_class

交叉连接（笛卡尔积）(CROSS JOIN)

笛卡尔积：对所有元素一一映射，排列组合

/* 交叉连接 */
SELECT 
	* 
FROM 
	student 
CROSS JOIN teacher 

其他连接

自连接

自连接作为一种特例，可以将一个表与它自身进行连接，称为自连接。

若要在一个表中查找具有相同列值的行，则可以使用自连接。

使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。

SELECT 
	a.学号, a.课程号, b.课程号, a.成绩
FROM 
	student a 
JOIN student b ON 
	a.成绩=b.成绩 
	AND a.学号=b.学号 
	AND a.课程号!=b.课程号

等值连接（相等连接）

SELECT 
	student.* , teacher.*
FROM 
	student , teacher
WHERE 
	student.class = teacher.class

使用“=”关系将表连接起来的查询，其查询结果中列出被连接表中的所有列，包括其中的重复列。

自然连接

数据库应用中最常用的是“自然连接”，它在目标列中去重相同的字段名，只留下一个。

我们将student表和teacher表中的s_class和t_class字段统一命名为相同的字段名class，方便展示自然连接。

SELECT 
	student.* , teacher.课程号, teacher.成绩
FROM 
	student , teacher
WHERE 
	student.class = teacher.class

SELECT 
	* 
FROM 
	student 
NATURAL JOIN teacher

进行自然连接运算要求两个表有共同属性（列），自然连接运算的结果表是在参与操作的两个表的共同属性上进行等值连接后，再去除重复的属性后所得的新表。

等值连接和自然连接的区别：

• 等值连接中不要求相等属性值的属性名相同，而自然连接要求相等属性值的属性名必须相同，即两关系只有在同名属性才能进行自然连接。

• 等值连接不将重复属性去掉，而自然连接去掉重复属性，也可以说，自然连接是去掉重复列的等值连接。

实例

直接JOIN，还是先子查询筛选再JOIN

最近，一个朋友的项目经理指出他的 SQL 写得有问题。

朋友的 SQL 大致如下，他的想法是常规操作，直接使用 JOIN … ON … 做联表查询：

select needed cols... from t1 
inner join t2 on t1.col12 = t2.col12 
inner join t3 on t2.col23 = t3.col23;

项目经理的建议是修改为：

select needed cols... from t1
inner join (select needed cols... from t2) tmp2 on t1.col12 = t1.col12
inner join (select needed cols... from t3) tmp3 on t2.col23 = t3.col23;

经理的想法是先用子查询查出各个从表中需要展示的列，再用 JOIN … ON … 做联表查询。

乍一看很有道理，先用子查询选出从表中需要展示的列，形成一张列较少的临时表，再进行 inner join ，似乎确实能够节省查询时间。那么，事实是否真的如该经理所愿呢？实践是检验真理的唯一标准。

。。。。

根据三次测试的样本结果，直接 JOIN 查询比先子查询再 JOIN 快了 7.5%

所以该经理的理论非常值得怀疑。直接 JOIN 查询虽然会关联更多的无关列，但子查询不关联无关列的代价是增加了建立、销毁临时表的开销，两权相害取其轻，而后者的开销在本文的实验中被证明是更大的，所以该经理的想法是值得商榷的，如果经过多场景大数据量下的反复试验，先子查询再 JOIN 相比于直接 JOIN 仍是耗时更多的一方，那么该想法则应当被彻底舍弃。

所以我们推荐直接使用join，而不是子查询。

https://blog.51cto.com/u_12831/6519436