MySQL连接查询(inner join,left join和right join的区别）

小灰灰 2021-10-24 05:22 350阅读 0赞

关系数据库由多个相关表组成，这些表使用已知为[外键][Link 1]列的常用列链接在一起。 因此，从业务角度来看，每个表中的数据是不完整的。

例如，在[示例数据库(yiibaidb)][yiibaidb]中，使用`orderNumber`列链接的`orders`和`orderdetails`表。

`orders`和`orderdetails`表的 ER 图如下所示 -

![277170719_30000.png][]

要获取完整的订单数据，需要从订单(`orders`)和订单详细(`orderdetails`)表中查询数据。

这就是为什么要使用连接表了。

MySQL连接是一种基于表之间的公共列的值来链接来自一个(自连接)或更多表的数据的方法。

MySQL支持以下类型的连接：

*  [交叉连接(Cross join)][Cross join]
 *  [内连接(Inner join)][Inner join]
 *  [左连接(Left join)][Left join]
 *  [右连接(Right join)][Right join]

要连接表，可以对相应类型的连接使用`CROSS JOIN`，`INNER JOIN`，`LEFT JOIN`或`RIGHT JOIN`子句。 在[SELECT语句][SELECT]中的`FROM`子句之后使用了连接子句。

> 请注意，MySQL不支持完全外部连接。

为了方便您了解每种类型的连接，我们将使用具有以下结构的两个表：`t1`和`t2`表：

USE testdb; CREATE TABLE t1 ( id INT PRIMARY KEY, pattern VARCHAR(50) NOT NULL ); CREATE TABLE t2 ( id VARCHAR(50) PRIMARY KEY, pattern VARCHAR(50) NOT NULL );

SQL

`t1`和`t2`表中都有`pattern`列，此列也是这两个表之间的公共列。执行以下查询语句将数据插入到`t1`和`t2`表中：

INSERT INTO t1(id, pattern) VALUES(1,'Divot'), (2,'Brick'), (3,'Grid'); INSERT INTO t2(id, pattern) VALUES('A','Brick'), ('B','Grid'), ('C','Diamond');

SQL

现在两个表中的数据如下所示 -

![850170756_76795.png][]

## 2. MySQL交叉连接(CROSS JOIN) ##

`CROSS JOIN`生成来自多个表的行的笛卡尔乘积。假设您使用`CROSS JOIN`来连接`t1`和`t2`表，结果集将包括`t1`表中的行与`t2`表中的行的组合。

要执行交叉连接(最后得到迪卡尔乘积)，请使用`CROSS JOIN`子句，如以下语句所示：

SELECT t1.id, t2.id FROM t1 CROSS JOIN t2;

SQL

执行上面语句，得到以下结果 -

mysql> SELECT t1.id, t2.id FROM t1 CROSS JOIN t2; +----+----+ | id | id | +----+----+ | 1 | A | | 2 | A | | 3 | A | | 1 | B | | 2 | B | | 3 | B | | 1 | C | | 2 | C | | 3 | C | +----+----+ 9 rows in set

SQL

如您所见，`t1`表中的每一行与`t2`表中的行结合形成笛卡尔乘积。

下图显示了`t1`和`t2`表之间的`CROSS JOIN`连接 -

![423180701_88270.png][]

## 3. MySQL内连接(INNER JOIN) ##

要形成一个`INNER JOIN`连接子句，需要一个称为连接谓词的条件。 `INNER JOIN`需要两个连接的表中的行具有匹配的列值。 `INNER JOIN`通过组合基于连接谓词的两个连接表的列值来创建结果集。

要连接两个表，`INNER JOIN`将第一个表中的每一行与第二个表中的每一行进行比较，以找到满足连接谓词的行对。每当通过匹配非`NULL`值来满足连接谓词时，两个表中每个匹配的行对的列值将包含在结果集中(可以简单地理解为两个表的交集)。

以下语句使用`INNER JOIN`子句来连接`t1`和`t2`表：

SELECT t1.id, t2.id FROM t1 INNER JOIN t2 ON t1.pattern = t2.pattern;

SQL

在上面语句中，以下表达式是连接谓词：

t1.pattern = t2.pattern

SQL

这意味着`t1`和`t2`表中的行必须在`pattern`列中具有相同的值才能包含在结果中。

以下查询的结果可以说明：

+----+----+ | id | id | +----+----+ | 2 | A | | 3 | B | +----+----+

SQL

下图显示了`t1`和`t2`表之间的`INNER JOIN`：

![791200745_93575.png][]

在此图中，两个表中的行必须具有相同`pattern`列值，才能包含在结果集中。

## 4. MySQL左连接(LEFT JOIN) ##

类似于`INNER JOIN`，`LEFT JOIN`也需要连接谓词。当使用`LEFT JOIN`连接两个表时，介绍了左表和右表的概念。

与`INNER JOIN`不同，`LEFT JOIN`返回左表中的所有行，包括满足连接谓词的行。 对于不匹配连接谓词的行，右表中的列将使用`NULL`值显示在结果集中。

以下语句使用`LEFT JOIN`子句来连接`t1`和`t2`表：

SELECT t1.id, t2.id FROM t1 LEFT JOIN t2 ON t1.pattern = t2.pattern ORDER BY t1.id;

SQL

执行上面查询，得到以下结果 -

+----+------+ | id | id | +----+------+ | 1 | NULL | | 2 | A | | 3 | B | +----+------+ 3 rows in set

SQL

如上所见，`t1`表中的所有行都包含在结果集中。 对于`t2`表(右表)中没有任何匹配`t1`表(左表)中的行，在`t2`表中的列使用`NULL`显示。

下图显示了在`t1`和`t2`表上使用`LEFT JOIN`子句：

![397200753_25587.png][]

在此图中，以下行具有相同的`pattern`：(`2`和`A`)，(`3`和`B`)。 `t1`表中`ID`为`1`的行在`t2`表中没有匹配的行，因此，在结果集中的`t2`表的列使用`NULL`代替显示。

## 5. MySQL右连接(RIGHT JOIN) ##

右连接(`RIGHT JOIN`)类似于右连接(`LEFT JOIN`)，除了表的处理是相反的。使用`RIGHT JOIN`，右表格(`t2`)中的每一行将显示在结果集中。 对于右表中没有左表(`t1`)中的匹配行的行，左表(`t1`)中的列会显示`NULL`。

以下语句使用`RIGHT JOIN`连接`t1`和`t2`表：

SELECT t1.id, t2.id FROM t1 RIGHT JOIN t2 on t1.pattern = t2.pattern ORDER BY t2.id;

SQL

执行上面是查询语句，得到下面结果 -

+------+----+
    | id   | id |
    +------+----+
    |    2 | A  |
    |    3 | B  |
    | NULL | C  |
    +------+----+
    3 rows in set

Shell

在此结果中，来自右表(`t2`)的所有行都显示在结果集中。对于左表(`t1`)中没有匹配右表(`t2`)中的行，则左表(`t1`)的列将使用`NULL`代替显示。

下图显示了`t1`和`t2`表之间的右连接(`RIGHT JOIN`)：

![906210707_20913.png][]

在本教程中，您已经学习了各种MySQL连接语句，包括交叉连接，内部连接，左连接和右连接，以从两个或多个表查询数据。

转载于:https://www.cnblogs.com/mafeng/p/10150013.html

[Link 1]: http://www.yiibai.com/mysql/foreign-key.html
[yiibaidb]: http://www.yiibai.com/mysql/sample-database.html
[277170719_30000.png]: /images/20211023/42649ae33f9c4ea49308670cd4d25b7d.png
[Cross join]: http://www.yiibai.com/mysql/cross-join.html
[Inner join]: http://www.yiibai.com/mysql/inner-join.html
[Left join]: http://www.yiibai.com/mysql/left-join.html
[Right join]: http://www.yiibai.com/mysql/right-join.html
[SELECT]: http://www.yiibai.com/mysql/select-statement-query-data.html
[850170756_76795.png]: /images/20211023/a724a1ee9eee4874b9ebec6bedf2d005.png
[423180701_88270.png]: /images/20211023/0d3f298a23074222a9aaac303614f7ed.png
[791200745_93575.png]: /images/20211023/a3fb9ab20d4a437e9a0acedfd69a2d5a.png
[397200753_25587.png]: /images/20211023/8d559f43daad41fc81c3ece195e2861c.png
[906210707_20913.png]: /images/20211023/8e05d5f65eef4c1a9c8db131d0806725.png