MySQL 连接方式：内连接、外连接、左连接、右连接、交叉连接

什么是 JOINS？

连接有助于从两个或多个数据库表中检索数据。

这些表使用主键和外键相互关联。

注意：JOIN 是 SQL 学习者中最容易误解的概念之一。为了简单易懂，我们将使用一个新的数据库来进行练习。如下所示。

以下所有示例均使用这两个表格。 电影 ID 列在 成员 指向 id 列在 电影 — 每个 JOIN 匹配的关系。

成员

电影

为什么要使用 JOIN？

在了解每种 JOIN 类型之前，有必要了解为什么 JOIN 比运行多个查询更可取。

现在你可能会想，既然我们可以运行查询来完成同样的任务，为什么还要使用 JOIN。特别是如果你有数据库编程经验，你就会知道我们可以逐个运行查询，在连续的查询中使用每个查询的输出。当然，这是可能的。但是使用 JOIN，你只需使用一个带有任何搜索参数的查询就可以完成工作。另一方面 MySQL 可以实现更好的性能 使用 JOIN 因为它可以使用索引。只需使用单个 JOIN 查询而不是运行多个查询即可减少服务器开销。使用多个查询会导致在 MySQL 和应用程序（软件）。此外，它还需要在应用程序端进行更多的数据操作。

显然，我们可以取得更好的成绩 MySQL 并通过使用 JOIN 来提高应用程序的性能。

连接类型

MySQL 支持多种 JOIN 类型，每种类型都针对同一对表的不同问题进行解答。下表对它们进行了比较；每种类型都通过一个查询及其输出进行了演示。

交叉加入

交叉连接 (Cross JOIN) 是连接 (JOIN) 的最简单形式，它将一个数据库表的每一行与另一个数据库表的所有行进行匹配。

换句话说，它为我们提供了第一个表的每一行与第二个表中的所有记录的组合。

假设我们想要获取所有电影记录的所有会员记录，我们可以使用下面显示的脚本来获得我们想要的结果。

SELECT * FROM `movies` CROSS JOIN `members`

在以下位置执行上述脚本 MySQL 工作台 给出了以下结果。

id
title

id
first_name
last_name
movie_id




1
ASSASSIN'S CREED: EMBERS
Animations
1
Adam
Smith
1


1
ASSASSIN'S CREED: EMBERS
Animations
2
Ravi
Kumar
2


1
ASSASSIN'S CREED: EMBERS
Animations
3
Susan
Davidson
5


1
ASSASSIN'S CREED: EMBERS
Animations
4
Jenny
Adrianna
8


1
ASSASSIN'S CREED: EMBERS
Animations
6
Lee
Pong
10


2
Real Steel(2012)
Animations
1
Adam
Smith
1


2
Real Steel(2012)
Animations
2
Ravi
Kumar
2


2
Real Steel(2012)
Animations
3
Susan
Davidson
5


2
Real Steel(2012)
Animations
4
Jenny
Adrianna
8


2
Real Steel(2012)
Animations
6
Lee
Pong
10


3
Alvin and the Chipmunks
Animations
1
Adam
Smith
1


3
Alvin and the Chipmunks
Animations
2
Ravi
Kumar
2


3
Alvin and the Chipmunks
Animations
3
Susan
Davidson
5


3
Alvin and the Chipmunks
Animations
4
Jenny
Adrianna
8


3
Alvin and the Chipmunks
Animations
6
Lee
Pong
10


4
The Adventures of Tin Tin
Animations
1
Adam
Smith
1


4
The Adventures of Tin Tin
Animations
2
Ravi
Kumar
2


4
The Adventures of Tin Tin
Animations
3
Susan
Davidson
5


4
The Adventures of Tin Tin
Animations
4
Jenny
Adrianna
8


4
The Adventures of Tin Tin
Animations
6
Lee
Pong
10


5
Safe (2012)
Action
1
Adam
Smith
1


5
Safe (2012)
Action
2
Ravi
Kumar
2


5
Safe (2012)
Action
3
Susan
Davidson
5


5
Safe (2012)
Action
4
Jenny
Adrianna
8


5
Safe (2012)
Action
6
Lee
Pong
10


6
Safe House(2012)
Action
1
Adam
Smith
1


6
Safe House(2012)
Action
2
Ravi
Kumar
2


6
Safe House(2012)
Action
3
Susan
Davidson
5


6
Safe House(2012)
Action
4
Jenny
Adrianna
8


6
Safe House(2012)
Action
6
Lee
Pong
10


7
GIA
18+
1
Adam
Smith
1


7
GIA
18+
2
Ravi
Kumar
2


7
GIA
18+
3
Susan
Davidson
5


7
GIA
18+
4
Jenny
Adrianna
8


7
GIA
18+
6
Lee
Pong
10


8
Deadline(2009)
18+
1
Adam
Smith
1


8
Deadline(2009)
18+
2
Ravi
Kumar
2


8
Deadline(2009)
18+
3
Susan
Davidson
5


8
Deadline(2009)
18+
4
Jenny
Adrianna
8


8
Deadline(2009)
18+
6
Lee
Pong
10


9
The Dirty Picture
18+
1
Adam
Smith
1


9
The Dirty Picture
18+
2
Ravi
Kumar
2


9
The Dirty Picture
18+
3
Susan
Davidson
5


9
The Dirty Picture
18+
4
Jenny
Adrianna
8


9
The Dirty Picture
18+
6
Lee
Pong
10


10
Marley and me
Romance
1
Adam
Smith
1


10
Marley and me
Romance
2
Ravi
Kumar
2


10
Marley and me
Romance
3
Susan
Davidson
5


10
Marley and me
Romance
4
Jenny
Adrianna
8


10
Marley and me
Romance
6
Lee
Pong
10

INNER JOIN

CROSS JOIN 会返回所有可能的配对，这通常不是你想要的。INNER JOIN 则会将结果缩小到真正相关的配对。

内部 JOIN 用于返回两个表中满足给定条件的行。

假设你想获取所有租过电影的会员列表以及他们租过的电影名称。你可以简单地使用 INNER JOIN 来实现，它会返回两个表中满足给定条件的行。

SELECT members.`first_name` , members.`last_name` , movies.`title`
FROM members ,movies
WHERE movies.`id` = members.`movie_id`

执行上述脚本

first_name
last_name
title




Adam
Smith
ASSASSIN'S CREED: EMBERS


Ravi
Kumar
Real Steel(2012)


Susan
Davidson
Safe (2012)


Jenny
Adrianna
Deadline(2009)


Lee
Pong
Marley and me

注意上述结果脚本也可以按如下方式编写以实现相同的结果。

SELECT A.`first_name` , A.`last_name` , B.`title`
FROM `members` AS A
INNER JOIN `movies` AS B
ON B.`id` = A.`movie_id`

外连接

INNER JOIN 会静默删除没有匹配项的行。当这些未匹配的行很重要时，OUTER JOIN 才是正确的选择。

MySQL 外连接会返回两个表中所有匹配的记录。

它可以检测连接表中没有匹配的记录。它返回 无 如果没有找到匹配项，则为连接表的记录的值。

听起来很复杂？我们来看一个例子——

LEFT JOIN

假设现在你想获取所有电影的标题以及租借过这些电影的会员姓名。很明显，有些电影还没有被任何人租借。我们可以简单地使用 LEFT JOIN 为目的。

即使在右侧表中未找到匹配的行，LEFT JOIN 也会返回左侧表中的所有行。 如果在右侧的表中未找到任何匹配项，则返回 NULL。

SELECT A.`title` , B.`first_name` , B.`last_name`
FROM `movies` AS A
LEFT JOIN `members` AS B
ON B.`movie_id` = A.`id`

在以下位置执行上述脚本 MySQL 工作台返回的结果如下所示。对于未被租借的电影，成员名称字段的值为 NULL。这意味着在成员表中没有找到与该电影匹配的成员。

title
first_name
last_name




ASSASSIN'S CREED: EMBERS
Adam
Smith


Real Steel(2012)
Ravi
Kumar


Safe (2012)
Susan
Davidson


Deadline(2009)
Jenny
Adrianna


Marley and me
Lee
Pong


Alvin and the Chipmunks
NULL
NULL


The Adventures of Tin Tin
NULL
NULL


Safe House(2012)
NULL
NULL


GIA
NULL
NULL


The Dirty Picture
NULL
NULL



Note: Null is returned for non-matching rows on right

正确加入

RIGHT JOIN 显然与 LEFT JOIN 相反。即使在左侧表中未找到匹配的行，RIGHT JOIN 也会返回右侧表中的所有列。如果在左侧表中未找到匹配项，则返回 NULL。

在我们的示例中，假设您需要获取会员的姓名以及他们租借的电影。现在我们有一位新会员，他还没有租借任何电影

SELECT A.`first_name` , A.`last_name`, B.`title`
FROM `members` AS A
RIGHT JOIN `movies` AS B
ON B.`id` = A.`movie_id`

在以下位置执行上述脚本 MySQL 工作台给出以下结果。

first_name
last_name
title




Adam
Smith
ASSASSIN'S CREED: EMBERS


Ravi
Kumar
Real Steel(2012)


Susan
Davidson
Safe (2012)


Jenny
Adrianna
Deadline(2009)


Lee
Pong
Marley and me


NULL
NULL
Alvin and the Chipmunks


NULL
NULL
The Adventures of Tin Tin


NULL
NULL
Safe House(2012)


NULL
NULL
GIA


NULL
NULL
The Dirty Picture



Note: Null is returned for non-matching rows on left

“ON” 和 “USING” 子句

到目前为止，每个查询都匹配到了带有 ON 子句的行。 MySQL 当匹配的列共享名称时，提供更简洁的替代方案。

在上面的 JOIN 查询示例中，我们使用了 ON 子句来匹配表之间的记录。

USING 子句也可用于相同目的。 使用 是 两个表中匹配的列需要具有相同的名称。

到目前为止，我们在“movies”表中使用名为“id”的主键。我们在“members”表中使用名为“movie_id”的主键。

让我们将“电影”表的“id”字段重命名为“movie_id”。我们这样做是为了获得完全相同的匹配字段名称。

ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;

接下来让我们将 USING 与上面的 LEFT JOIN 示例一起使用。

SELECT A.`title` , B.`first_name` , B.`last_name`
FROM `movies` AS A
LEFT JOIN `members` AS B
USING ( `movie_id` )

除了使用 ON 和 与 JOIN 一起使用 你可以使用许多其他 MySQL 类似条款 通过...分组， 在哪里 甚至像 SUM, AVG等等。

常见问题