MYSQL 的几种join连接方式

一、关于JOIN语法【mysql 手册  ||  中文】

MySQL支持以下JOIN语法。

这些语法用于SELECT语句的 table_references 部分和多表DELETE和UPDATE语句。

table_references:
    escaped_table_reference [, escaped_table_reference] ...

escaped_table_reference: {
    table_reference
  | { OJ table_reference }
}

table_reference: {
    table_factor
  | joined_table
}

table_factor: {
    tbl_name [PARTITION (partition_names)]
        [[AS] alias] [index_hint_list]
  | table_subquery [AS] alias
  | ( table_references )
}

joined_table: {
    table_reference [INNER | CROSS] JOIN table_factor [join_specification]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON search_condition
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
}

join_specification: {
    ON search_condition
  | USING (join_column_list)
}

join_column_list:
    column_name [, column_name] ...

index_hint_list:
    index_hint [, index_hint] ...

index_hint: {
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
}

index_list:
    index_name [, index_name] ...

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

与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_JOINhttp://huoding.com/2013/06/04/261
 

您可以提供提示，当从一个表中恢复信息时，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 和 FORCE INDEX只会影响使用哪些索引。当分解一个 ORDER BY 或 GROUP BY 时，这些语句不会影响某个索引是否被使用。

二、内连接

在第一部分，我们了解到在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 [LEFT] JOIN：这个句子的作用相当于 INNER JOIN，或者是在USING子句中包含了联结的表中所有公共字段的 Left JOIN（左联结）。

五、全连接

因为在mysql中并不支持full join ，所以我们用union实现。MySQL Full Join的实现http://blog.csdn.net/jklfjsdj79hiofo/article/details/40399265

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’会报错。

强烈推荐阅读：

MySQL STRAIGHT_JOIN 与 NATURAL JOIN

mysql的join使用

Mysql Join语法解析与性能分析