mysql cross join的用法_MySQL JOIN 用法示例详解

本文将图文并茂的讲解 MySQL 的查询时 JOIN 的用法。这里用到的两个示例表及示例数据如下：

Default

CREATE TABLE `user` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`email` varchar(255) NOT NULL,

`name` varchar(255) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `users_email_unique` (`email`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES (1,'xiaoming@qq.com','小明'),(2,'xiaowang@qq.com','小王'),(6,'xiangzhang@qq.com','小张'),(7,'xiaoli@qq.com','小李');

CREATE TABLE `user_info` (

`user_id` int(10) NOT NULL,

`age` int(10) NOT NULL,

`name` varchar(255) NOT NULL,

PRIMARY KEY (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user_info` VALUES (1,22,'小明'),(2,21,'小王'),(3,33,'小郑');

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

CREATETABLE`user`(

`id`int(10)unsignedNOTNULLAUTO_INCREMENT,

`email`varchar(255)NOTNULL,

`name`varchar(255)NOTNULL,

PRIMARYKEY(`id`),

UNIQUEKEY`users_email_unique`(`email`)

)ENGINE=InnoDBDEFAULTCHARSET=utf8;

INSERTINTO`user`VALUES(1,'xiaoming@qq.com','小明'),(2,'xiaowang@qq.com','小王'),(6,'xiangzhang@qq.com','小张'),(7,'xiaoli@qq.com','小李');

CREATETABLE`user_info`(

`user_id`int(10)NOTNULL,

`age`int(10)NOTNULL,

`name`varchar(255)NOTNULL,

PRIMARYKEY(`user_id`)

)ENGINE=InnoDBDEFAULTCHARSET=utf8;

INSERTINTO`user_info`VALUES(1,22,'小明'),(2,21,'小王'),(3,33,'小郑');

插入数据库后数据如下：

MySQL 官网上介绍 JOIN 一共有以下几种形式，接下来笔者将一一介绍：

Default

table_reference [INNER | CROSS] JOIN table_factor [join_condition]

| table_reference STRAIGHT_JOIN table_factor

| table_reference STRAIGHT_JOIN table_factor ON conditional_expr

| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition

| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

1

2

3

4

5

table_reference[INNER|CROSS]JOINtable_factor[join_condition]

|table_referenceSTRAIGHT_JOINtable_factor

|table_referenceSTRAIGHT_JOINtable_factorONconditional_expr

|table_reference{LEFT|RIGHT}[OUTER]JOINtable_referencejoin_condition

|table_referenceNATURAL[{LEFT|RIGHT}[OUTER]]JOINtable_factor

JOIN、CROSS JOIN、INNER JOIN

MySQL 官网说：In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). JOIN, CROSS JOIN, 和 INNER JOIN 是等价的，可以相互替换。下面的例子不再出现 CROSS JOIN 和 INNER JOIN。

直接使用 JOIN 连接两个表查询得到结果为：

可以看到，两表结果进行了笛卡尔积，最终查出 12 行记录。这是没带任何条件的查询，这条语句等价于：

Default

SELECT * FROM user, user_info

1

SELECT *FROMuser,user_info

MySQL 官网如是说：

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

说的是，在没有条件的情况下，INNER JOIN 和 ,(逗号)是等价的，都返回两张表的笛卡尔积。

我们来试试带条件的 JOIN 查询：

这个结果很清晰，连接的条件是 user.id=user_info.user_id。结果返回两行记录。

USING

我们来看看 USING 的用法：

USING(name) 等价于 ON user.name=user_info.name，但是大家请注意，这里有一点点区别，使用 USING 得到的结果只有一个 name 字段，且 name 字段在结果最前面，而使用 ON 得到的结果有两个 name 字段。

我们再试试使用 USING 去查并不是两张表都有的字段：

提示错误，因为 user_info 表里并没有 id 字段。

JOIN 与 LEFT JOIN 和 RIGHT JOIN

在 LEFT JOIN 里面，如果右边的表里没有匹配左边表的记录，则右边表所有字段为 NULL；同理，在 RIGHT JOIN 里面，如果左边的表里没有匹配右边表的记录，则左边表所有字段为 NULL；而 JOIN 只查询出两张表都存在的数据。

利用 LEFT JOIN 可以查出左边表存在而右边表不存在的记录，如下图：

STRAIGHT_JOIN

STRAIGHT_JOIN 和 JOIN 一样，除了前者可以保证左边的表先查。STRAIGHT_JOIN 可以用在防止 JOIN 优化器将表的顺序搞错。

NATURAL

NATURAL JOIN 等价于使用 USING，它会 USING 所有两张表里都包含的字段：

OUTER

至于这货，笔者也没搞明白是干什么的。官网给出的实例讲的是用在 ODBC 里，笔者没看明白。

上面所有的示例都只演示了两张表 JOIN，可不要以为只能两张表进行 JOIN 查询。一下是一些 SQL 示例：

Default

SELECT * FROM t1, t2, t3;

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)

ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c);

1

2

3

SELECT *FROMt1,t2,t3;

SELECT *FROMt1LEFTJOIN(t2CROSSJOINt3CROSSJOINt4)

ON(t2.a=t1.aANDt3.b=t1.bANDt4.c=t1.c);

ps:大家有没有发现 wordpress 写作时，编辑器用得很不爽啊，比如，插入代码后想回到正常编辑必须切换到文本模式下，在  标签后先打几个字符然后在回可视化模式正常写作。还有，每次发文章都要跑到后台来，没有直接用 markdown 舒服啊，直接本地写好，提交上去，多方便。