这篇博客详细介绍了MySQL中的JOIN操作，包括INNER JOIN、LEFT JOIN、RIGHT JOIN、CROSS JOIN及其变体，以及STRAIGHT_JOIN和NATURAL JOIN的用法。通过实例展示了如何在多表查询中使用JOIN来获取所需数据。
MySQL JOIN 语法概述
SQL（MySQL）JOIN用于根据两个或多个表中的字段之间的关系，从这些表中得到数据。
JOIN通常与ON关键字搭配使用，基本语法如下：
... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona
table1通常称为左表，table2称为右表。ON关键字用于设定匹配条件，用于限定在结果集合中想要哪些行。如果需要指定其他条件，后面可以加上 WHERE条件 或者 LIMIT 以限制记录返回数目等。
下面以最常见的两表连接来说明MySQLJOIN 的用法，关于多表JOIN请参见《MySQLJOIN 多表》。
MySQLJOIN 分类
JOIN按照功能大致分为如下三类：
-
INNERJOIN（内连接）：取得两个表中存在连接匹配关系的记录。
-
LEFTJOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。
-
RIGHTJOIN（右连接）：与LEFTJOIN 相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。
关于MySQLFULL JOIN 全连接
MySQL没有提供SQL标准中的FULLJOIN（全连接）：两个表记录都取出，而不管彼此是否有对应记录。要解决此问题，可以使用UNION关键字来合并LEFTJOIN 与RIGHTJOIN，达到模拟FULLJOIN 的目的。
MySQLINNER JOIN
INNERJOIN 用于取得两个表中存在连接匹配关系的记录。下面是两个原始数据表：
article文章表:
| aid | title | content | uid |
|---|---|---|---|
| 1 | 文章1 | 文章1正文内容... | 1 |
| 2 | 文章2 | 文章2正文内容... | 1 |
| 3 | 文章3 | 文章3正文内容... | 2 |
| 4 | 文章4 | 文章4正文内容... | 4 |
user用户表:
| uid | username | |
|---|---|---|
| 1 | admin | admin@5idev.com |
| 2 | 小明 | xiao@163.com |
| 3 | Jack | jack@gmail.com |
article表中文章的所属用户是通过uid这个字段与user表关联起来的。通过观察数据不难发现，对于uid=3的用户，并没有发表任何文章；而文章中aid=4却无法在uid表中找到对应记录（可能是该用户被删除而其所属的文章却被保留了下来）。
我们列出所用文章与用户一一对应的数据。
SELECT... INNER JOIN ... ON 语句如下：
SELECT article.aid,article.title,user.username FROM article INNER JOIN user ON article.uid = user.uid
返回查询结果如下：
| aid | title | username |
|---|---|---|
| 1 | 文章1 | admin |
| 2 | 文章2 | admin |
| 3 | 文章3 | 小明 |
对于INNERJOIN，等同与下面的SQL语句：
SELECT article.aid,article.title,user.username FROM article,user WHERE article.uid = user.uid
CROSSJOIN
CROSSJOIN 即交叉连接，在不指定ON条件下：
SELECT article.aid,article.title,user.username FROM article CROSS JOIN user
得到的结果是被连接的两个数据表的乘积，即笛卡尔积。
实际上，在MySQL中（仅限于MySQL）CROSSJOIN 与INNERJOIN 的表现是一样的，在不指定ON条件得到的结果都是笛卡尔积，反之取得两个表完全匹配的结果。
INNERJOIN 与CROSSJOIN 可以省略INNER或CROSS关键字，因此下面的SQL效果是一样的：
... FROM table1 INNER JOIN table2 ... FROM table1 CROSS JOIN table2 ... FROM table1 JOIN table2
MySQLLEFT JOIN 语法
SQL（MySQL）LEFTJOIN 会取得左表（table1）全部记录，即使右表（table2）并无对应匹配记录。LEFTJOIN 基本语法如下：
... FROM table1 LEFT JOIN table2 ON condition ...
MySQLLEFT JOIN 用法实例
下面是两个原始数据表：
article文章表:
| aid | title | content | uid |
|---|---|---|---|
| 1 | 文章1 | 文章1正文内容... | 1 |
| 2 | 文章2 | 文章2正文内容... | 1 |
| 3 | 文章3 | 文章3正文内容... | 2 |
| 4 | 文章4 | 文章4正文内容... | 4 |
user用户表:
| uid | username | |
|---|---|---|
| 1 | admin | admin@5idev.com |
| 2 | 小明 | xiao@163.com |
| 3 | Jack | jack@gmail.com |
我们列出所有的文章及对应的所属用户，即使没有用户的文章也列出。
SELECT... LEFT JOIN ... ON 语句如下：
SELECT article.aid,article.title,user.username FROM article LEFT JOIN user ON article.uid = user.uid
返回查询结果如下：
| aid | title | username |
|---|---|---|
| 1 | 文章1 | admin |
| 2 | 文章2 | admin |
| 3 | 文章3 | 小明 |
| 4 | 文章4 | NULL |
可以看出来，与 INNERJOIN 明显的区别是，左表记录被全部取出，即使右表无对应匹配记录。
提示
这里所谓记录被“全部”取出，是相对于INNERJOIN 的限制来说的。其实可以在上面的SQL语句后面加个WHERE条件或者LIMIT等关键字以同一般SQL语句一样对结果集做一个范围限制。
ISNULL
在上面的例子中，对于右表中没有对应匹配的数据记录，其所有的列都被置为NULL，因此要查询这部分记录（如在上面例子中体现为查找aid=4这类无对应用户的文章记录），可以附加ISNULL 条件：
SELECT article.aid,article.title,user.username FROM article LEFT JOIN user ON article.uid = user.uid WHERE user.uid IS NULL
MySQLRIGHT JOIN 语法
SQL（MySQL）RIGHTJOIN 会取得右表（table2）全部记录，即使左表（table2）并无对应匹配记录。RIGHTJOIN 基本语法如下：
... FROM table1 RIGHT JOIN table2 ON condition ...
MySQLRIGHT JOIN 用法实例
下面是两个原始数据表：
article文章表:
| aid | title | content | uid |
|---|---|---|---|
| 1 | 文章1 | 文章1正文内容... | 1 |
| 2 | 文章2 | 文章2正文内容... | 1 |
| 3 | 文章3 | 文章3正文内容... | 2 |
| 4 | 文章4 | 文章4正文内容... | 4 |
user用户表:
| uid | username | |
|---|---|---|
| 1 | admin | admin@5idev.com |
| 2 | 小明 | xiao@163.com |
| 3 | Jack | jack@gmail.com |
我们列出所有的用户，以及他们可能拥有的文章。
SELECT... RIGHT JOIN ... ON 语句如下：
SELECT article.aid,article.title,user.username FROM article RIGHT JOIN user ON article.uid = user.uid
返回查询结果如下：
| aid | title | username |
|---|---|---|
| 1 | 文章1 | admin |
| 2 | 文章2 | admin |
| 3 | 文章3 | 小明 |
| NULL | NULL | Jack |
对比 LEFTJOIN 返回的查询结果，RIGHTJOIN 返回的结果与其刚好“相反”。
ISNULL
在上面的例子中，对于左表中没有对应匹配的数据记录，其所有的列都被置为NULL，因此要查询这部分记录（如在上面例子中体现为查找username=Jack这类无对应文章的所有用户），可以附加ISNULL 条件：
SELECT article.aid,article.title,user.username FROM article LEFT JOIN user ON article.uid = user.uid WHERE article.aid IS NULL
MySQLJOIN 多表连接
除了常用的两个表连接之外，SQL（MySQL）JOIN语法还支持多表连接。多表连接基本语法如下：
... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON condition INNER|LEFT|RIGHT JOIN table3 ON condition ...
JOIN多表连接实现了从多个表中获取相关数据，下面是三个原始数据表：
article文章表:
| aid | title | content | uid | tid |
|---|---|---|---|---|
| 1 | 文章1 | 文章1正文内容... | 1 | 1 |
| 2 | 文章2 | 文章2正文内容... | 1 | 2 |
| 3 | 文章3 | 文章3正文内容... | 2 | 1 |
| 5 | 文章5 | 文章5正文内容... | 4 | 1 |
user用户表:
| uid | username | |
|---|---|---|
| 1 | admin | admin@5idev.com |
| 2 | 小明 | xiao@163.com |
| 3 | Jack | jack@gmail.com |
type文章类型表:
| tid | typename |
|---|---|
| 1 | 普通文章 |
| 2 | 精华文章 |
| 3 | 草稿 |
MySQLINNER JOIN 多表
我们使用 INNERJOIN 列出三个表中都具有关联关系的数据：
SELECT article.aid,article.title,user.username,type.typename FROM article INNER JOIN user ON article.uid=user.uid INNER JOIN type ON article.tid=type.tid
返回查询结果如下：
| aid | title | username | typename |
|---|---|---|---|
| 1 | 文章1 | admin | 普通文章 |
| 2 | 文章2 | admin | 精华文章 |
| 3 | 文章3 | 小明 | 普通文章 |
MySQLLEFT JOIN 多表
使用 LEFTJOIN 三个表查询：
SELECT article.aid,article.title,user.username,type.typename FROM article LEFT JOIN user ON article.uid=user.uid LEFT JOIN type ON article.tid=type.tid
返回查询结果如下：
| aid | title | username | typename |
|---|---|---|---|
| 1 | 文章1 | admin | 普通文章 |
| 2 | 文章2 | admin | 精华文章 |
| 3 | 文章3 | 小明 | 普通文章 |
| 4 | 文章4 | NULL | 普通文章 |
MySQLRIGHT JOIN 多表
使用 RIGHTJOIN 三个表查询：
SELECT article.aid,article.title,user.username,type.typename FROM article RIGHT JOIN user ON article.uid=user.uid RIGHT JOIN type ON article.tid=type.tid
返回查询结果如下：
| aid | title | username | typename |
|---|---|---|---|
| 1 | 文章1 | admin | 普通文章 |
| 2 | 文章2 | admin | 精华文章 |
| 3 | 文章3 | 小明 | 普通文章 |
| NULL | NULL | NULL | 草稿 |
可见，在RIGHTJOIN 右连接中，只是列出最后一个右连接表的所有数据。
说明
对于MySQL多表JOIN，还可以INNER、LEFT和RIGHT混用，其返回结果与各关键字顺序有关，感兴趣可自行测试。
MySQLSTRAIGHT_JOIN
STRAIGHT_JOIN是MySQL对标准SQL的扩展，用于在多表查询时指定表载入的顺序。在JOIN表连接中，同样可以指定表载入的顺序，本文只讲述STRAIGHT_JOIN在表连接JOIN中的应用。
MySQLSTRAIGHT_JOIN 语法如下：
... FROM table1 STRAIGHT_JOIN table2 ON condition ...
STRAIGHT_JOIN实际上与内连接INNERJOIN 表现完全一致，不同的是使用了STRAIGHT_JOIN后，table1会先于table2载入。
提示
MySQL在执行INNERJOIN 的时候，会根据自己内部的优化规则来决定先载入table1还是table2，如果您确认MySQL载入表的顺序并不是最优化的时候，就可以使用STRAIGHT_JOIN以替代INNERJOIN。
MySQLSTRAIGHT_JOIN 例子
SELECT article.aid,article.title,user.username FROM article STRAIGHT_JOIN user ON article.uid=user.uid
注意：该SQL仅仅是STRAIGHT_JOIN使用示例，并不表示其合理性。
如果有更多表进行连接，那么使用STRAIGHT_JOIN后，其载入顺序就遵循从左往右的规则。最后，STRAIGHT_JOIN无法应用于LEFTJOIN 或RIGHTJOIN。
MySQLNATURAL JOIN
NATURALJOIN 也叫自然连接，实际是属于JOIN的一种。
MySQLNATURAL JOIN 语法如下：
... FROM table1 NATURAL JOIN table2 ...
使用NATURALJOIN 时，MySQL将表中具有相同名称的字段自动进行记录匹配，而这些同名字段类型可以不同。因此，NATURALJOIN 不用指定匹配条件。
NATURALJOIN 默认是同名字段完全匹配的INNERJOIN，也可以使用 LEFTJOIN 或 RIGHTJOIN。一些例子如下：
SELECT article.aid,article.title,user.username FROM article NATURAL JOIN user // LEFT SELECT article.aid,article.title,user.username FROM article NATURAL LEFT JOIN user // RIGHT SELECT article.aid,article.title,user.username FROM article NATURAL RIGHT JOIN user
扫一扫
1394
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
