MySQL的Join使用

最新推荐文章于 2024-03-10 11:25:43 发布
最新推荐文章于 2024-03-10 11:25:43 发布
阅读量689 收藏
点赞数
分类专栏： Mysql基础知识 文章标签： mysql join

在MySQL(以5.1为例)中，表连接的语法可以参见MySQL官方手册：MySQL官方手册-JOIN

在查询中，连接的语法类似

[sql]  view plain copy
  1. SELECT select_expr FROM table_references  

table_references（对表的引用） 的定义如下（也可以看成 连接表达式 ）：（晕晕晕哈）

[sql]  view plain copy
  1. table_references:  
  2.     table_reference [, table_reference] ...  
  3.   
  4. table_reference:  
  5.     table_factor  
  6.   | join_table  
  7.   
  8. table_factor:  
  9.     tbl_name [[AS] alias] [index_hint_list]  
  10.   | table_subquery [AS] alias  
  11.   | ( table_references )  
  12.   | { OJ table_reference LEFT OUTER JOIN table_reference  
  13.         ON conditional_expr }  
  14.   
  15. join_table:  
  16.     table_reference [INNER | CROSS] JOIN table_factor [join_condition]  
  17.   | table_reference STRAIGHT_JOIN table_factor  
  18.   | table_reference STRAIGHT_JOIN table_factor ON conditional_expr  
  19.   | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition  
  20.   | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor  
  21.   
  22. join_condition:  
  23.     ON conditional_expr  
  24.   | USING (column_list)  
  25.   
  26. index_hint_list:  
  27.     index_hint [, index_hint] ...  
  28.   
  29. index_hint:  
  30.     USE {INDEX|KEY}  
  31.       [{FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])  
  32.   | IGNORE {INDEX|KEY}  
  33.       [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)  
  34.   | FORCE {INDEX|KEY}  
  35.       [{FOR {JOIN|ORDER BY|GROUP BY}] (index_list)  
  36.   
  37. index_list:  
  38.     index_name [, index_name] ...  

其中，table_factor是基本的表选择，而join_table是基于表的一些扩展。
下面，通过实验介绍一下表连接。
首先，假设有以下几个表

table1
id book
1java
2c++
3php

table2
id author
2zhang
3wang
4li

table3
author year
zhang2003
ma2006
liu2011

Inner Join 内连接
将两个表中存在连接关系的字段，组成的记录集，叫做内连接。
内连接等价于

[sql]  view plain copy
  1. mysql> select table1.id as id,book,author from table1, table2 where table1.id=table2.id;  
  2. +------+------+--------+  
  3. | id   | book | author |  
  4. +------+------+--------+  
  5. |    2 | c++  | zhang  |  
  6. |    3 | php  | wang   |  
  7. +------+------+--------+  
  8. 2 rows in set (0.00 sec)  
  9. mysql> select * from table1 inner join table2 using (id);  
  10. +------+------+--------+  
  11. | id   | book | author |  
  12. +------+------+--------+  
  13. |    2 | c++  | zhang  |  
  14. |    3 | php  | wang   |  
  15. +------+------+--------+  
  16. 2 rows in set (0.00 sec)  

可以看出，两者是等价的。没有Using子句的Inner Join相当于是求两个table的笛卡尔积。
Cross Join 交叉连接
在Mysql中，Cross Join可以用逗号表达式表示，例如(table1, table 2)。在Mysql中，Cross Join 和 Inner Join 是等价的，但是在标准SQL中，它们并不等价，Inner Join 用于带有on表达式的连接，反之用Cross Join。以下两个SQL语句是等价的。
Cross Join 指的是两个table的笛卡尔积。以下三句SQL是等价的。

[sql]  view plain copy
  1. mysql> select * from table1 inner join table2;  
  2. mysql> select * from table1 cross join table2;  
  3. mysql> select * from (table1, table2);  
  4. mysql> select * from table1 nature join table2;  
  5. 结果集：  
  6. +------+------+------+--------+  
  7. | id   | book | id   | author |  
  8. +------+------+------+--------+  
  9. |    1 | java |    2 | zhang  |  
  10. |    2 | c++  |    2 | zhang  |  
  11. |    3 | php  |    2 | zhang  |  
  12. |    1 | java |    3 | wang   |  
  13. |    2 | c++  |    3 | wang   |  
  14. |    3 | php  |    3 | wang   |  
  15. |    1 | java |    4 | li     |  
  16. |    2 | c++  |    4 | li     |  
  17. |    3 | php  |    4 | li     |  
  18. +------+------+------+--------+  

不难理解，下面两句SQL也是等价的。

[sql]  view plain copy
  1. mysql> select * from table1 left join (table2, table3) on (table2.id = table1.id and table2.author = table3.author);  
  2. mysql> select * from table1 left join (table2 cross join table3) on (table2.id = table1.id and table2.author = table3.author);  
  3. 结果集：  
  4. +------+------+------+--------+--------+------+  
  5. | id   | book | id   | author | author | year |  
  6. +------+------+------+--------+--------+------+  
  7. |    1 | java | NULL | NULL   | NULL   | NULL |  
  8. |    2 | c++  |    2 | zhang  | zhang  | 2003 |  
  9. |    3 | php  | NULL | NULL   | NULL   | NULL |  
  10. +------+------+------+--------+--------+------+  

Natural Join 自然连接
NATURAL [LEFT] JOIN：这个句子的作用相当于INNER JOIN，或者是在USING子句中包含了联结的表中所有公共字段的Left JOIN（左联结）。
也就是说：下面两个SQL是等价的。

[sql]  view plain copy
  1. mysql> select * from table1 natural join table2;  
  2. mysql> select * from table1 inner join table2 using (id);  
  3.   
  4. 结果集：  
  5. +------+------+--------+  
  6. | id   | book | author |  
  7. +------+------+--------+  
  8. |    2 | c++  | zhang  |  
  9. |    3 | php  | wang   |  
  10. +------+------+--------+  

同时，下面两个SQL也是等价的。

[sql]  view plain copy
  1. mysql> select * from table1 natural left join table2;  
  2. mysql> select * from table1 left join table2 using(id);  
  3. 结果集：  
  4. +------+------+--------+  
  5. | id   | book | author |  
  6. +------+------+--------+  
  7. |    1 | java | NULL   |  
  8. |    2 | c++  | zhang  |  
  9. |    3 | php  | wang   |  
  10. +------+------+--------+  

Left Join 左外连接
左外连接A、B表的意思就是将表A中的全部记录和表B中字段连接形成的记录集，这里注意的是最后出来的记录集会包括表A的全部记录。
左连接表1，表二等价于右连接表二，表一。如下两个SQL是等价的：
[sql]  view plain copy
  1. mysql> select * from table1 left join table2 using (id);  
  2. mysql> select * from table2 right join table1 using (id);  
  3. 结果集：  
  4. +------+------+--------+  
  5. | id   | book | author |  
  6. +------+------+--------+  
  7. |    1 | java | NULL   |  
  8. |    2 | c++  | zhang  |  
  9. |    3 | php  | wang   |  
  10. +------+------+--------+  

Right Join 右外连接

右外连接和左外连接是类似的。为了方便数据库便于访问，推荐使用左外连接代替右外连接。


最后，讲一下Mysql表连接的一些注意事项。

1、两个表求差集的方法
如果求 左表 - 右表 的差集，使用类似下面的SQL：
[sql]  view plain copy
  1. SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL;  
  2. 例如  
  3. mysql> select table1.* from table1 left join table2 using(id) where table2.id is null;  
  4. +------+------+  
  5. | id   | book |  
  6. +------+------+  
  7. |    1 | java |  
  8. +------+------+  
  9. 1 row in set (0.00 sec)  

2、Using子句
Using子句可以使用On子句重写。但是使用Select *　查询出的结果有差别。以下两句话是等价的：
[sql]  view plain copy
  1. mysql> select id, book, author from table1 join table2 using (id);  
  2. mysql> select table1.id, book, author from table1 join table2 on table1.id=table2.id;  
  3. 结果集：  
  4. +------+------+--------+  
  5. | id   | book | author |  
  6. +------+------+--------+  
  7. |    2 | c++  | zhang  |  
  8. |    3 | php  | wang   |  
  9. +------+------+--------+  

但是下面两个有些许不同，使用on时候，重复的部分会被输出两次。
[sql]  view plain copy
  1. mysql> select * from table1 join table2 using (id);  
  2. +------+------+--------+  
  3. | id   | book | author |  
  4. +------+------+--------+  
  5. |    2 | c++  | zhang  |  
  6. |    3 | php  | wang   |  
  7. +------+------+--------+  
  8. 2 rows in set (0.00 sec)  
  9. mysql> select * from table1 join table2 on table1.id=table2.id;  
  10. +------+------+------+--------+  
  11. | id   | book | id   | author |  
  12. +------+------+------+--------+  
  13. |    2 | c++  |    2 | zhang  |  
  14. |    3 | php  |    3 | wang   |  
  15. +------+------+------+--------+  
  16. 2 rows in set (0.00 sec)  

3、Straight Join的使用
STRAIGHT_JOIN 和 JOIN相似，除了大部分情况下，在使用STRAIGHT_JOIN时候，先读右表后读左表。而在大部分情况下是先读左表的。STRAIGHT_JOIN仅用于少数情况下的表连接性能优化，比如右表记录数目明显少于左表。

4、Mysql表连接的运算顺序
在MySQL 5.1版本中，INNER JOIN, CROSS JOIN, LEFT JOIN, 和RIGHT JOIN 比逗号表达式具有更高的优先级。
因此SQL1被解析成SQL3，而不是SQL2。
[sql]  view plain copy
  1. SQL1 :　SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);  
  2. SQL2 :　SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);  
  3. SQL3 :　SELECT * FROM t1, (t2 JOIN t3 ON (t1.i1 = t3.i3));  

因此会报错，找不到i1列。因此以后在写这样的查询的时候，最好写明白，不要省略括号，这样能避免很多错误。

5、循环的自然连接
在MySQL 5.1版本中，SQL1等价于SQL3， 而在MySQL以前版本中，SQL1等价于SQL2。
[sql]  view plain copy
  1. SQL1 : SELECT ... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;  
  2. SQL2 : SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c;  
  3. SQL3 : SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;  




小藤椅
关注
专栏目录
Java面试基础篇
m0_67392010的博客
07-29 3791
先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。Java的强引用、弱引用、软引用、虚引用。Java语言基础常识。Java12新特性。JavaIO详解。...
MYSQL七种关系图
weixin_44747858的博客
07-31 1340
MYSQL七种查询关系。 CREATE TABLE t_blog( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(50), typeId INT ); SELECT * FROM t_blog; ±—±------±-------+ | id | title | typeId | ±—±------±-------+ | 1 | aaa | 1 | | 2 | bbb | 2 | | 3 | ccc |
MYSQL中JOIN的用法
weixin_30652879的博客
08-08 110
近期用phpcms v9做项目，初期没有问题，后期随着数据量的增大，phpcms v9后台出现的栏目更新不动的情况，初期我以为是程序的问题，进行了程序排查，没有发现任何问题，登录上centos服务器后free命令发现内存也没有满。瞬间感觉很蛋疼，不知如何处理，后来登录阿里云账号，发现更新栏目时,cpu使用率直接到达100%，xhell远程登录进入服务器后用top检测cpu使用率的情况，发...
MySQL联接查询算法（NLJ、BNL、BKA、HashJoin）
weixin_34326179的博客
02-20 1715
一、联接过程介绍 为了后面一些测试案例，我们事先创建了两张表，表数据如下： 1 2 3 4 CREATE TABLE t1 (m1 int, n1 char(1)); CREATE TABLE t2 (m2 int, n2 char(1)); INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, ...
mysql Join使用以及优化
04-14
MySQL数据库中Join操作的使用及优化是一项重要的技能，它涉及到数据库中表与表之间的关联查询。在执行Join操作时，数据库管理系统需要按照某种算法将多个表中的数据记录联合起来，并返回查询结果。Join操作的主要...
MySQL Join使用与优化策略探讨
MySQL Join使用及优化详解 在处理大型数据库系统时，JOIN操作是数据关联的关键，尤其是在关系型数据库如MySQL中。然而，JOIN的使用并非总是那么简单，特别是当涉及到性能优化时。本文将深入探讨以下两个主要问题： ...
MySQL中的JOIN操作
最新发布
Meteor的笔记
03-10 1179
Inner Join 返回了员工和部门有匹配关系的记录。Left Join 返回了所有员工的信息，其中赵六没有部门信息，所以department_name字段为NULL。Right Join 返回了所有部门的信息，其中技术部没有员工，所以name字段为NULL。Left Join、Right Join和Inner Join是MySQL中非常重要的查询操作，它们允许我们根据两个或多个表之间的关系来检索数据。在选择使用哪种JOIN操作时，我们应根据具体的业务需求和数据关系来决定。
Mysql中Join的使用实例详解
09-09
本章节我们将向大家介绍如何使用MySQL 的 JOIN 在两个或多个表中查询数据。对mysql中的join感兴趣的朋友一起学习吧
MySQL中JOIN的用法
qq_46416934的博客
07-30 9836
JOIN用于连表查询，主要有5种用法。下面分别演示这5种用法随便建2张表，结构如下字段col1用来使两张表有一个同名字段的（但其实没什么用，因为查询条件都需要用ON来指定，这里只是说明一下如果有相同的字段名也没什么影响）...
MySQL中JOIN的用法（简化明确版）
编程资料大全
12-01 244
MySQL中JOIN的用法（简化明确版）MySQL中涉及到多表连接的话，可以使用如下几种JOIN1. ','逗号运算符 例如SELECT * FROM table1,table2 WHERE where_condition2. [CROSS | INNER] JOIN (交叉连接和内连接，结果都是产生笛卡尔积，但在标准SQL中两者 <wbr></wbr>是不同的，可...
MySQL的join用法
热门推荐
huatian5的博客
06-29 1万+
MySQL的join用法主要有三种： inner jon 内连接 left join 左连接 right join 右连接 table a: table b: 1.inner join 内连接又叫等值连接，此时的inner可以省略。 获取两个表中有匹配关系的记录，即两表取交集 2.left join 以左表为基础，获取匹配关系的记录，如果右表中没有匹配...
Mysql Join语法解析与性能分析
a620002129的博客
05-27 1365
一．Join语法概述 join 用于多表中字段之间的联系，语法如下： ... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona table1:左表；table2:右表。 JOIN 按照功能大致分为如下三类： INNER JOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。 LEFT JOIN（左连接）：取得左表（...
mysql中join的用法
I_am_a_Beginner的博客
07-22 685
1、MySQL的join七种用法 2、Mysql中Join语法解析与性能分析
MySQL的JOIN用法
leadai的博客
10-26 496
全文共925个字，10张图，预计阅读时间6分钟。数据库中的JOIN称为连接，连接的主要作用是根据两个或多个表中的列之间的关系，获取存在于不同表中的数据。连接分为三类：内连...
SQL 中JOIN的用法
10-14 2585
JOIN连接组合两个表中的字段记录，包括三种：  INNER JOIN运算式：连接组合两个表中的字段记录。 LEFT JOIN运算式：连接组合两个表中的字段记录，并将包含了LEFT JOIN左边表中的全部记录。 RIGHT JOIN运算式：连接组合两个表中的字段记录，并将包含了RIGHT JOIN右边表中的全部记录。 INNER JOIN设定两个表相关连的运算式，以连接组合两个表中的字段记录。 I
mysql中的JOIN用法总结
weixin_30410999的博客
03-06 248
join是mysql中一个基础的关键词，一般在多表连接查询中使用，这里做一下总结 1、JOIN的语法格式 table_references: table_reference [, table_reference] ... table_reference: table_factor | join_table table_factor: tbl...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值