SQL必知必会：SQL 中的连接

sql

连接

大家一定用过 LEFT JOIN、RIGHT JOIN 这样的操作符，这实际上就是连接，SQL 中的连接是多表操作的基础之一，对连接不了解很难去查询好多表。同时 SQL 有众多版本，每个版本对连接支持和使用会有不一致，常用的有：SQL92、SQL99等。

本文是基于 SQL 99 标准中的连接查询，还有其他 SQL 标准，有些语句并不适用其他 SQL 标准。

交叉连接

交叉连接 SQL99 采用的是 CROSS JOIN，常听听说的笛卡尔乘积其实是 SQL92 中的，而交叉连接实际上就是 SQL92 中的笛卡尔乘积，也就是说 交叉连接 == 笛卡尔乘积。

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。

select * from t1 CROSS JOIN t2 CROSS JOIN T3

自然连接

自然连接则是 SQL92 标准中的等值连接，自然连接是一种关联查询方式，它不需要使用 ON 子句指定连接条件，而是根据两个表之间列名相同的列自动进行连接,可自动查询两张连接表中所有相同的字段，然后进行等值连接。

// SQL92
select * from t1,t2 where t1.id = t2.t1_id

// SQL99
select * from t1 NATURAL JOIN t2

实际上，在 SQL99 中用 NATURAL JOIN 替代了 WHERE pt1.id = t2.t1_id。

需要注意的是，自然连接只适用于两个表具有相同列名的情况。如果有多个相同列名，自然连接会自动进行多个列之间的匹配，因此可能会出现一些意想不到的结果。在实际应用中，我们通常会使用明确的连接条件，以确保查询结果的正确性和可预测性。

ON 连接

ON 连接用来指定我们想要的连接条件

// 等值连接
select * from t1 JOIN t2 ON t1.id = t2.t1_id

// 非等值连接
select * from t1 JOIN t2 ON t1.field BETWEEN t2.field AND t2.field


// SQL92
select * from t1,t2 WHERE t1.field BETWEEN t2.field AND t2.field

一般来说在 SQL99 中，我们需要连接的表会采用 JOIN 进行连接，ON 指定了连接条件，后面可以是等值连接，也可以采用非等值连接。

当我们进行多表查询的时候，如果连接多个表的条件是等号时，就是等值连接，其他的运算符连接就是非等值查询。

USING 连接

进行连接的时候，可以用 USING 指定数据表里的同名字段进行等值连接

select * from t1 JOIN t2 USING(id)

USING 连接用户自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，只需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN USING 可以简化 JOIN ON 的等值连接。

外连接

1. 左外连接：LEFT JOIN 或 LEFT OUTER JOIN
2. 右外连接：RIGHT JOIN 或 RIGHT OUTER JOIN
3. 全外连接：FULL JOIN 或 FULL OUTER JOIN

什么是左外连接，什么是右外连接呢？

左外连接，就是指左边的表是主表，需要显示左边表的全部行，而右侧的表是从表。

左外连接

select * from t1 LEFT JOIN t2 on t1.name = t2.name

右外连接

select * from t1 RIGHT JOIN t2 on t1.name = t2.name

全外连接

全外连接也就是当表之间有匹配的行，会显示内连接的结果。当某行在另一个表中没有匹配时，那么会把另一个表中选择的列显示为空值。

也就是说，全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

select * from t1 FULL JOIN t2 on t1.name = t2.name

MySQL 不支持全外连接，否则的话全外连接会返回左表和右表中的所有行。

自连接

自连接是指在同一表中进行的连接操作。自连接通常涉及到使用别名，因为需要对同一表进行两次或多次引用。自连接可以用于在一个表中根据某些条件查找与其他记录有关系的记录。

自连接可以对多个表进行操作，也可以对同一个表进行操作。也就是说查询条件使用了当前表的字段。

// SQL92
select * from t1,t2 where t1.name = 'xxx';

// SQL99
select * from t1 JOIN t2 on t1.name = 'xxx';