MySQL JOINS 教程：INNER、OUTER、LEFT、RIGHT、CROSS

什么是 JOINS？

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

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

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

id	名字	姓	电影 ID
1	Adam	SMITH	1
2	拉维	库马尔	2
3	Susan	戴维森	5
4	珍妮	阿德里安娜	8
5	李	庞	10

id	标题	类别
1	刺客信条：余烬	动画
2	铁甲钢拳（2012）	动画
3	艾尔文与花栗鼠	动画
4	丁丁历险记	动画
5	安全 (2012)	操作
6	安全屋（2012）	操作
7	GIA	18+
8	截止日期2009	18+
9	肮脏的图片	18+
10	马利和我	浪漫

联接类型

交叉连接

交叉连接 (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

内部 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`

外连接

MySQL 外连接 (Outer JOIN) 返回两个表中匹配的所有记录。

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

听起来有点困惑？让我们来看一个例子——

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 显然与 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” 子句

在上面的 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等等。

为什么我们应该使用连接？

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

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

总结

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