MySQL统计函数记录——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多表》。
JOIN按照功能大致分为如下三类:
INNERJOIN(内连接):取得两个表中存在连接匹配关系的记录。
LEFTJOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录。
RIGHTJOIN(右连接):与LEFTJOIN相反,取得右表(table2)完全记录,即是左表(table1)并无匹配对应记录。
MySQL没有提供SQL标准中的FULLJOIN(全连接):两个表记录都取出,而不管彼此是否有对应记录。要解决此问题,可以使用UNION关键字来合并LEFTJOIN与RIGHTJOIN,达到模拟FULLJOIN的目的。
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即交叉连接,在不指定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
下面是两个原始数据表:
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语句一样对结果集做一个范围限制。
在上面的例子中,对于右表中没有对应匹配的数据记录,其所有的列都被置为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
下面是两个原始数据表:
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返回的结果与其刚好“相反”。
在上面的例子中,对于左表中没有对应匹配的数据记录,其所有的列都被置为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
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
草稿
我们使用 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
小明
普通文章
使用 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
普通文章
使用 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混用,其返回结果与各关键字顺序有关,感兴趣可自行测试。
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。
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// LEFTSELECT article.aid,article.title,user.username FROM article NATURAL LEFT JOIN user// RIGHTSELECT article.aid,article.title,user.username FROM article NATURAL RIGHT JOIN user