MySQL JOIN 教程：INNER, OUTER, LEFT, RIGHT, CROSS

什么是 JOIN？

JOIN 用于从两个或多个数据库表中检索数据。

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

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

id	first_name	last_name	movie_id
1	Adam	史密斯	1
2	Ravi	Kumar	2
3	Susan	Davidson	5
4	Jenny	Adrianna	8
5	Lee	Pong	10

id	title	category
1	刺客信条：圣影	动画
2	铁甲钢拳(2012)	动画
3	鼠来宝	动画
4	丁丁历险记	动画
5	安全（2012）	行动
6	安全屋 (2012)	行动
7	GIA	18+
8	最后通牒 2009	18+
9	情欲交易	18+
10	马利和我	浪漫

JOIN 类型

CROSS JOIN

CROSS JOIN 是最简单的 JOIN 类型，它将一个数据库表中的每一行与另一个表的所有行进行匹配。

换句话说，它将第一个表中的每一行与第二个表中的所有记录进行组合。

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

SELECT * FROM `movies` CROSS JOIN `members`

在 MySQL workbench 中执行上述脚本将得到以下结果。

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

INNER 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`

Outer JOINs

MySQL Outer JOINs 返回两个表中所有匹配的记录。

它可以检测没有匹配项的记录。如果没有找到匹配项，它将为关联表中的记录返回 **NULL** 值。

听起来很令人困惑？让我们看一个例子 –

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 workbench 中执行上述脚本将得到以下结果。您可以从下面列出的返回结果中看到，对于未租借的电影，会员姓名字段具有 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

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 workbench 中执行上述脚本将得到以下结果。

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” 子句

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

USING 子句也可以用于此目的。**USING 的区别在于它需要两个表中匹配的列具有相同的名称。**

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

让我们将“movies”表中的“id”字段重命名为“movie_id”。我们这样做是为了使匹配的字段名称相同。

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

接下来，让我们在上面的 LEFT JOIN 示例中使用 USING。

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

除了在 JOIN 中使用 **ON** 和 **USING** 之外，您还可以使用许多其他 MySQL 子句，如 **GROUP BY、WHERE** 甚至函数如 **SUM**、**AVG** 等。

为什么要使用 JOIN？

现在您可能会想，当我们能运行查询完成相同任务时，为什么要使用 JOIN？特别是如果您有一些数据库编程经验，您会知道我们可以逐个运行查询，并将每个查询的输出用于后续查询。当然，这是可能的。但是使用 JOIN，您可以使用任何搜索参数仅通过一个查询就能完成工作。另一方面，**MySQL 使用 JOIN 可以获得更好的性能**，因为它可以使用索引。简单地使用单个 JOIN 查询而不是运行多个查询可以减少服务器开销。使用多个查询会导致 MySQL 和应用程序（软件）之间更多的数据传输。此外，它还需要在应用程序端进行更多的数据操作。

很明显，通过使用 JOIN，我们可以获得更好的 MySQL 和应用程序性能。

摘要

• JOIN 允许我们将来自多个表的数据合并到一个结果集中。
• 与子查询相比，JOIN 具有更好的性能
• INNER JOINs 只返回满足给定条件的数据行。
• OUTER JOINs 也可以返回未找到匹配项的数据行。未匹配的数据行将以 NULL 关键字返回。
• 主要的 JOIN 类型包括 Inner、Left Outer、Right Outer、Cross JOINs 等。
• JOIN 操作中最常用的子句是“ON”。“USING” 子句要求匹配的列具有相同的名称。
• JOIN 也可以用于其他子句，如 GROUP BY、WHERE、子查询、聚合函数等。