mysql+join的方式_MYSQL 的几种join连接方式

一、关于JOIN语法【内容来自于mysql手册】

MySQL支持以下JOIN语法。这些语法用于SELECT语句的 table_references 部分和多表DELETE和UPDATE语句：

table_references:

table_reference [, table_reference] …

table_reference:

table_factor

| join_table

table_factor:

tbl_name [[AS] alias]

[{USE|IGNORE|FORCE} INDEX (key_list)]

| ( table_references )

| { OJ table_reference LEFT OUTER JOIN table_reference

ON conditional_expr }

join_table:

table_reference [INNER | CROSS] JOIN table_factor [join_condition]

| table_reference STRAIGHT_JOIN table_factor

| table_reference STRAIGHT_JOIN table_factor ON condition

| table_reference LEFT [OUTER] JOIN table_reference join_condition

| table_reference NATURAL [LEFT [OUTER]] JOIN table_factor

| table_reference RIGHT [OUTER] JOIN table_reference join_condition

| table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

join_condition:

ON conditional_expr

| USING (column_list)

一个表引用还被称为一个联合表达式。

与SQL标准相比， table_factor 的语法被扩展了。SQL标准只接受 table_reference ，而不是圆括号内的一系列条

目。

在MySQL中，CROSS JOIN从语法上说与INNER JOIN等同(两者可以互相替换。在标准SQL中，两者是不等同的。

INNER JOIN与ON子句同时使用，CROSS JOIN以其它方式使用。

USING( column_list )子句用于为一系列的列进行命名。这些列必须同时在两个表中存在。如果表a和表b都包含列

c1, c2和c3，则以下联合会对比来自两个表的对应的列：

a LEFT JOIN b USING (c1,c2,c3)

两个表的NATURAL [LEFT] JOIN被定义为与INNER JOIN语义相同，或与使用USING子句的LEFT JOIN语义相同。

USING子句用于为同时存在于两个表中的所有列进行命名。

INNER JOIN和，(逗号)在无联合条件下是语义相同的：两者都可以对指定的表计算出笛卡儿乘积也就是说，第

一个表中的每一行被联合到第二个表中的每一行)。

RIGHT JOIN的作用与LEFT JOIN的作用类似。要使代码可以在数据库内移植，建议您使用LEFT JOIN代替RIGHT JOIN。

STRAIGHT_JOIN与JOIN相同。除了有一点不一样，左表会在右表之前被读取。STRAIGH_JOIN可以被用于这样的

情况，即联合优化符以错误的顺序排列表。MySQL优化的奇技淫巧之STRAIGHT_JOIN

您可以提供提示，当从一个表中恢复信息时，MySQL应使用哪个索引。通过指定USE INDEX(key_list)，您可以告知

MySQL只使用一个索引来查找表中的行。另一种语法IGNORE INDEX(key_list)可以被用于告知MySQL不要使用某些

特定的索引。如果EXPLAIN显示MySQL正在使用来自索引清单中的错误索引时，这些提示会有用处。

您也可以使用FORCE INDEX，其作用接近USE INDEX(key_list)，不过增加了一项作用，一次表扫描被假设为代价

很高。换句话说，只有当无法使用一个给定的索引来查找表中的行时，才使用表扫描。

USE KEY、IGNORE KEY和FORCE KEY是USE INDEX、IGNORE INDEX和FORCE INDEX的同义词。

注释：当MySQL决定如何在表中查找行并决定如何进行联合时，使用USE INDEX、IGNORE INDEX和FORCEINDEX

只会影响使用哪些索引。当分解一个ORDER BY或GROUP BY时，这些语句不会影响某个索引是否被使用。

以上，我们从内连接、外连接、自然连接 三个方向来具体学习join的格式和特点。

二、内连接

在第一部分，我们了解到在mysql中，inner join...on , join...on , 逗号...where ，cross join...on是一样的含义。但是在

标准SQL中，它们并不等价。

三、外链接

外链接包括left [outer] join 和right [outer] join ，左连接和右连接。

左连接从左表产生一套完整的记录,与匹配的记录(右表) .如果没有匹配,右侧将包含null。右连接反之。

在手册中提到“RIGHT JOIN的作用与LEFT JOIN的作用类似。要使代码可以在数据库内移植，建议您使用LEFT JOIN

代替RIGHT JOIN”

四、自然连接

NATURAL JOIN

NATURAL [LEFT] JOIN：这个句子的作用相当于INNER JOIN，或者是在USING子句中包含了联结的表中所有公共字段

的Left JOIN(左联结)。

五、全连接

full join

因为在mysql中并不支持full join ，所以我们用union实现。MySQL Full Join的实现

CREATE TABLE `pa` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(100) NOT NULL DEFAULT '',

`price` decimal(10,2) NOT NULL DEFAULT 0.00,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='产品基础信息';

insert into pa(id,name,price) value(1,'产品A1',12.00),(2,'产品A2',66.55),(3,'产品A3',100);

CREATE TABLE `pb` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`paid` int(11) NOT NULL ,

`color` varchar(100) NOT NULL DEFAULT '',

`size` float(10,2) NOT NULL DEFAULT 0.00,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='产品扩展信息';

insert into pb(id,paid,color,size) value(1,1,'red',2),(2,1,'green',3),(3,1,'blue',4),(4,2,'red',2),(5,2,'green',3),(6,2,'blue',4);

内连接：

select a.id,a.name,a.price,b.color,b.size from pa a inner join pb b on a.id=b.paid;

select a.id,a.name,a.price,b.color,b.size from pa a cross join pb b on a.id=b.paid;

select a.id,a.name,a.price,b.color,b.size from pa a join pb b on a.id=b.paid;

select a.id,a.name,a.price,b.color,b.size from pa a,pb b where a.id=b.paid;

查询结果

+----+----------+-------+-------+------+

| id | name     | price | color | size |

+----+----------+-------+-------+------+

|  1 | 产品A1   | 12.00 | red   | 2.00 |

|  1 | 产品A1   | 12.00 | green | 3.00 |

|  1 | 产品A1   | 12.00 | blue  | 4.00 |

|  2 | 产品A2   | 66.55 | red   | 2.00 |

|  2 | 产品A2   | 66.55 | green | 3.00 |

|  2 | 产品A2   | 66.55 | blue  | 4.00 |

+----+----------+-------+-------+------+

左连接：

select a.id,a.name,a.price,b.color,b.size from pa a left join pb b on a.id=b.paid;

+----+----------+--------+-------+------+

| id | name     | price  | color | size |

+----+----------+--------+-------+------+

|  1 | 产品A1   |  12.00 | red   | 2.00 |

|  1 | 产品A1   |  12.00 | green | 3.00 |

|  1 | 产品A1   |  12.00 | blue  | 4.00 |

|  2 | 产品A2   |  66.55 | red   | 2.00 |

|  2 | 产品A2   |  66.55 | green | 3.00 |

|  2 | 产品A2   |  66.55 | blue  | 4.00 |

|  3 | 产品A3   | 100.00 | NULL  | NULL |

+----+----------+--------+-------+------+

右连接：

select a.id,a.name,a.price,b.color,b.size from pa a right join pb b on a.id=b.paid;

+------+----------+-------+-------+------+

| id   | name     | price | color | size |

+------+----------+-------+-------+------+

|    1 | 产品A1   | 12.00 | red   | 2.00 |

|    1 | 产品A1   | 12.00 | green | 3.00 |

|    1 | 产品A1   | 12.00 | blue  | 4.00 |

|    2 | 产品A2   | 66.55 | red   | 2.00 |

|    2 | 产品A2   | 66.55 | green | 3.00 |

|    2 | 产品A2   | 66.55 | blue  | 4.00 |

+------+----------+-------+-------+------+

自然连接：

select a.id,a.name,a.price,b.color,b.size from pa a natural join pb b;

+----+----------+--------+-------+------+

| id | name     | price  | color | size |

+----+----------+--------+-------+------+

|  1 | 产品A1   |  12.00 | red   | 2.00 |

|  2 | 产品A2   |  66.55 | green | 3.00 |

|  3 | 产品A3   | 100.00 | blue  | 4.00 |

+----+----------+--------+-------+------+

以上几个例子，我们会有两个疑问：

1.为什么做链接会有null项？

2.自然连接怎么和其他几个出来的行数不一致？

第一个疑问：因为左连接的含义是以左表为基准，根据左表中的信息去查右表，右表没有，记为null，有则写入，当右表在左表的条件下有多个数据时，分别列出。

第二个疑问：使用 NATURAL JOIN 时，MySQL 将表中具有相同名称的字段自动进行记录匹配，而这些同名字段类型可以不同。因此，NATURAL JOIN 不用指定匹配条件，不能指定匹配条件，加‘on’会报错。

强烈推荐阅读：