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_JOINicon-default.png?t=M276http://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的实现icon-default.png?t=M276http://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语法解析与性能分析

评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值