【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连接其实就是将多张表的列进行拼接，合并成一张表。

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

格式：

↓CloseCode↓

FROM 表A 
JOIN 表B ON AB的关联条件 
JOIN 表C ON 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)⟳

适用于关联性极强的表，左表和右表同时存在才有意义，有一个不存在，那么就无意义，过滤掉，不需要展示（相当于WHERE中过滤掉整条数据了）

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

↓CloseCode↓

/* 内连接 */
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

↓CloseCode↓

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

右外连接(RIGHT JOIN)⟳

右外连接：RIGHT JOIN，RIGHT OUTER JOIN

↓CloseCode↓

/* 右连接 */
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+右外连接实现。

↓CloseCode↓

/* 全连接（不适用于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)⟳

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

↓CloseCode↓

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

其他连接⟳

自连接⟳

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

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

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

↓CloseCode↓

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

等值连接（相等连接）⟳

↓CloseCode↓

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

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

自然连接⟳

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

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

↓CloseCode↓

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

↓CloseCode↓

SELECT 
	* 
FROM 
	student 
NATURAL JOIN teacher

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

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

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

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

实例⟳

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

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

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

↓CloseCode↓

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

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

↓CloseCode↓

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

子查询⟳

单列子查询->EXISTS⟳

这里不是EXIST，而是EXISTS的原因是：EXISTS的主语是单数，它是针对每一行数据进行判断存在性的。

在SQL中，子查询通常用于在一个查询中嵌套另一个查询。子查询可以出现在多种不同的上下文中，比如 SELECT , INSERT , UPDATE , 或 DELETE 语句中。然而，有时候子查询可以被 EXISTS 子句替代，以提高查询效率。

EXISTS 是一个逻辑运算符，用于检查子查询是否返回任何行。
如果子查询找到数据了，EXISTS 将立即终止查询处理，并返回 TRUE ，否则返回 FALSE 。
因此，可以利用EXISTS运算符的此功能来提高查询性能。

以下是一些常见的将子查询转换为 EXISTS 的例子：

1.比较子查询: 原始子查询⟳

↓CloseCode↓

SELECT * 
FROM table1 
WHERE column1 = (SELECT column2 FROM table2 WHERE condition);

转换为 EXISTS :

↓CloseCode↓

SELECT * 
FROM table1 
WHERE EXISTS (
  SELECT 1 FROM table2 WHERE table2.column2 = table1.column1 AND condition
);

2.IN子查询: 原始子查询⟳

↓CloseCode↓

SELECT * 
FROM table1 
WHERE column1 IN (
  SELECT column2 FROM table2 WHERE condition
);

转换为 EXISTS :

↓CloseCode↓

SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.column2 = table1.column1 AND condition);

请注意，并不是所有的子查询都可以简单地转换为 EXISTS 。转换的可行性取决于子查询的具体逻辑和上下文。在某些情况下，使用 EXISTS 可以提高查询性能，因为它允许数据库引擎在找到第一个匹配的行时停止搜索。
然而，在其他情况下，子查询可能更合适，特别是当需要从子查询中返回多列或执行更复杂的聚合操作时。

---

当然，JOIN连接也可以替换单列子查询

↓CloseCode↓

SELECT TABLE1.* 
FROM TABLE1 
JOIN TABLE2 ON 
  TABLE2.COLUMN2 = TABLE1.COLUMN1

但是，INNER JOIN会更慢，原因：

• 产生两表合并的临时表
• ON后面的条件，需要扫描TABLE2全表才能确定连接完了；而EXISTS不需要扫描全表，只要存在值，那就直接返回

使用 EXISTS 代替 IN 和 inner join

多列子查询->JOIN连接、子查询⟳

多列子查询一般可以优化为join，但是，当实际数据量超过一定界限的时候，不如先筛掉大量的数据，之后再子查询，而不是先join join，完了之后再筛选。

效率对比⟳

虽然效率一致，但是还是推荐使用EXISTS，养成好习惯。

• 从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放：执行效率都一致

• 从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论：哈希半连接比哈希连接要快，对于Oracle来说，它的内部优化可以让IN和EXISTS一个执行效率