Mysql Join总结

最新推荐文章于 2025-04-09 17:16:26 发布
最新推荐文章于 2025-04-09 17:16:26 发布
阅读量796 收藏 10
点赞数 3
分类专栏： mysql 文章标签： Mysql Join
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43430036/article/details/89922437
版权
本文介绍了MySQL连接，包括交叉连接、内连接、左连接和右连接，阐述了各连接类型的特点和使用方法。同时，详细分析了ON与Where在左右连接查询中的差异，指出标准SQL解析和执行顺序，强调ON与Where使用时要注意筛选条件针对的表。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

Mysql Join总结

首先，一张最完美的图来展示我们可能遇到的各种连接场景，当然这幅图不适合Mysql，因为Mysql不支持Full Join，但是我们依然可以来理解可能遇到的各种join场景。
sql joins

Mysql 连接

MySQL连接是一种基于表之间的公共列的值来链接来自一个(自连接)或更多表的数据的方法。
MySQL支持以下类型的连接：

  • 交叉连接(Cross join)
  • 内连接(Inner join)
  • 左连接(Left join)
  • 右连接(Right join)

要连接表，可以对相应类型的连接使用CROSS JOIN，INNER JOIN，LEFT JOIN或RIGHT JOIN子句。 在SELECT语句中的FROM子句之后使用了连接子句。

请注意，MySQL不支持完全外部连接。

数据准备

新建测试数据库

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */;

新建测试数据表

use test;
#新建产品品牌分类表
CREATE TABLE `s_brand_category` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into s_brand_category(`name`) values ('手机'),('电脑');
# 新建产品品牌表
CREATE TABLE `s_product_brand` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` int(11) UNSIGNED NOT NULL COMMENT '品牌分类Id' ,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `#idx_category_id` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into s_product_brand(`category_id`,`name`) values(1,'小米'),(1,'华为'),(2,'联想');

MySQL交叉连接(CROSS JOIN)

CROSS JOIN生成来自多个表的行的笛卡尔乘积。假设您使用CROSS JOIN来连接t1和t2表，结果集将包括t1表中的行与t2表中的行的组合笛卡尔乘积。 Mysql默认的连接方式即CROSS JOIN。

mysql> select * from s_brand_category ,s_product_brand;
+----+--------+----+-------------+--------+
| id | name   | id | category_id | name   |
+----+--------+----+-------------+--------+
|  1 | 手机   |  1 |           1 | 小米   |
|  2 | 电脑   |  1 |           1 | 小米   |
|  1 | 手机   |  2 |           1 | 华为   |
|  2 | 电脑   |  2 |           1 | 华为   |
|  1 | 手机   |  3 |           2 | 联想   |
|  2 | 电脑   |  3 |           2 | 联想   |
+----+--------+----+-------------+--------+
6 rows in set (0.01 sec)

mysql> select * from s_brand_category cross join s_product_brand;
+----+--------+----+-------------+--------+
| id | name   | id | category_id | name   |
+----+--------+----+-------------+--------+
|  1 | 手机   |  1 |           1 | 小米   |
|  2 | 电脑   |  1 |           1 | 小米   |
|  1 | 手机   |  2 |           1 | 华为   |
|  2 | 电脑   |  2 |           1 | 华为   |
|  1 | 手机   |  3 |           2 | 联想   |
|  2 | 电脑   |  3 |           2 | 联想   |
+----+--------+----+-------------+--------+
6 rows in set (0.00 sec)

MySQL内连接(INNER JOIN)

要形成一个INNER JOIN连接子句，需要一个称为连接谓词的条件。 INNER JOIN需要两个连接的表中的行具有匹配的列值。 INNER JOIN通过组合基于连接谓词的两个连接表的列值来创建结果集。
要连接两个表，INNER JOIN将第一个表中的每一行与第二个表中的每一行进行比较，以找到满足连接谓词的行对。每当通过匹配非NULL值来满足连接谓词时，两个表中每个匹配的行对的列值将包含在结果集中(可以简单地理解为两个表的交集).

当没有连接谓词的存在的时候INNER JOIN 得到的结果即是CROSS JOIN。

mysql> select * from s_brand_category inner join s_product_brand;
+----+--------+----+-------------+--------+
| id | name   | id | category_id | name   |
+----+--------+----+-------------+--------+
|  1 | 手机   |  1 |           1 | 小米   |
|  2 | 电脑   |  1 |           1 | 小米   |
|  1 | 手机   |  2 |           1 | 华为   |
|  2 | 电脑   |  2 |           1 | 华为   |
|  1 | 手机   |  3 |           2 | 联想   |
|  2 | 电脑   |  3 |           2 | 联想   |
+----+--------+----+-------------+--------+
6 rows in set (0.00 sec)

mysql> select * from s_brand_category as c inner join s_product_brand  as b on c.id = b.category_id;
+----+--------+----+-------------+--------+
| id | name   | id | category_id | name   |
+----+--------+----+-------------+--------+
|  1 | 手机   |  1 |           1 | 小米   |
|  1 | 手机   |  2 |           1 | 华为   |
|  2 | 电脑   |  3 |           2 | 联想   |
+----+--------+----+-------------+--------+
3 rows in set (0.00 sec)

MySQL左连接(LEFT JOIN)

类似于INNER JOIN，LEFT JOIN也需要连接谓词。当使用LEFT JOIN连接两个表时，介绍了左表和右表的概念。

与INNER JOIN不同，LEFT JOIN返回左表中的所有行，包括满足连接谓词的行。 对于不匹配连接谓词的行，右表中的列将使用NULL值显示在结果集中。
不同于Inner Join,Left Join 必须有连接谓词ON的存在。

# 先插入一条种类不存在的产品记录
insert into s_product_brand(`category_id`,`name`) values(3,'特步');

mysql> select * from s_product_brand as b left join s_brand_category as c  on c.id = b.category_id;
+----+-------------+--------+------+--------+
| id | category_id | name   | id   | name   |
+----+-------------+--------+------+--------+
|  1 |           1 | 小米   |    1 | 手机   |
|  2 |           1 | 华为   |    1 | 手机   |
|  3 |           2 | 联想   |    2 | 电脑   |
|  4 |           3 | 特步   | NULL | NULL   |
+----+-------------+--------+------+--------+
4 rows in set (0.00 sec)

mysql> select * from s_brand_category as c left join s_product_brand  as b on c.id = b.category_id;
+----+--------+------+-------------+--------+
| id | name   | id   | category_id | name   |
+----+--------+------+-------------+--------+
|  1 | 手机   |    1 |           1 | 小米   |
|  1 | 手机   |    2 |           1 | 华为   |
|  2 | 电脑   |    3 |           2 | 联想   |
+----+--------+------+-------------+--------+
3 rows in set (0.00 sec)

MySQL右连接(RIGHT JOIN)

右连接(RIGHT JOIN)类似于左连接(LEFT JOIN)，除了表的处理是相反的。使用RIGHT JOIN，右表格(t2)中的每一行将显示在结果集中。 对于右表中没有左表(t1)中的匹配行的行，左表(t1)中的列会显示NULL。

不同于Inner Join,Right Join 必须有连接谓词ON的存在。

mysql> select * from s_product_brand as b right join s_brand_category as c  on c.id = b.category_id;
+------+-------------+--------+----+--------+
| id   | category_id | name   | id | name   |
+------+-------------+--------+----+--------+
|    1 |           1 | 小米   |  1 | 手机   |
|    2 |           1 | 华为   |  1 | 手机   |
|    3 |           2 | 联想   |  2 | 电脑   |
+------+-------------+--------+----+--------+
3 rows in set (0.00 sec)

mysql> select * from s_brand_category as c right join s_product_brand  as b on c.id = b.category_id;
+------+--------+----+-------------+--------+
| id   | name   | id | category_id | name   |
+------+--------+----+-------------+--------+
|    1 | 手机   |  1 |           1 | 小米   |
|    1 | 手机   |  2 |           1 | 华为   |
|    2 | 电脑   |  3 |           2 | 联想   |
| NULL | NULL   |  4 |           3 | 特步   |
+------+--------+----+-------------+--------+

ON 与Where

这里，我们先注意在左右连接查询中 on和where未知顺序不同导致的查询结果的不同。

mysql> select * from s_product_brand as b left join s_brand_category as c  on  b.category_id = c.id  and  c.id = 1;
+----+-------------+--------+------+--------+
| id | category_id | name   | id   | name   |
+----+-------------+--------+------+--------+
|  1 |           1 | 小米   |    1 | 手机   |
|  2 |           1 | 华为   |    1 | 手机   |
|  3 |           2 | 联想   | NULL | NULL   |
|  4 |           3 | 特步   | NULL | NULL   |
+----+-------------+--------+------+--------+
4 rows in set (0.00 sec)

mysql> select * from s_product_brand as b left join s_brand_category as c  on  b.category_id = c.id  where  c.id = 1;
+----+-------------+--------+------+--------+
| id | category_id | name   | id   | name   |
+----+-------------+--------+------+--------+
|  1 |           1 | 小米   |    1 | 手机   |
|  2 |           1 | 华为   |    1 | 手机   |
+----+-------------+--------+------+--------+
2 rows in set (0.00 sec)

我们会发现两个查询存在差异。

为什么会存在差异，其实这和on与where查询顺序有关。

首先，我们需要知道：
标准的 SQL 解析顺序为:

  1. FROM 子句 组装来自不同数据源的数据
  2. WHERE 子句 基于指定的条件对记录进行筛选
  3. GROUP BY 子句 将数据划分为多个分组
  4. 使用聚合函数进行计算
  5. 使用HAVING子句筛选分组
  6. 计算所有的表达式
  7. 使用ORDER BY对结果集进行排序

标准的 SQL 执行顺序为:

  1. FROM：对FROM子句中前两个表执行笛卡尔积生成虚拟表vt1
  2. ON: 对vt1表应用ON筛选器只有满足 join_condition 为真的行才被插入vt2
  3. OUTER(join)：如果指定了 OUTER JOIN保留表(preserved table)中未找到的行将行作为外部行添加到vt2，生成t3，如果from包含两个以上表，则对上一个联结生成的结果表和下一个表重复执行步骤和步骤直接结束。
  4. WHERE：对vt3应用 WHERE 筛选器只有使 where_condition 为true的行才被插入vt4
  5. GROUP BY：按GROUP BY子句中的列列表对vt4中的行分组生成vt5
  6. CUBE|ROLLUP：把超组(supergroups)插入vt6，生成vt6
  7. HAVING：对vt6应用HAVING筛选器只有使 having_condition 为true的组才插入vt7
  8. SELECT：处理select列表产生vt8
  9. DISTINCT：将重复的行从vt8中去除产生vt9
  10. ORDER BY：将vt9的行按order by子句中的列列表排序生成一个游标vc10

所以这里的根本原因在于
先on条件筛选表行再join

而对于where是对 join之后结果做再次筛选。

ON与where的使用一定要注意场所：

  • ON后面的筛选条件主要是针对的是关联表【而对于主表刷选条件不适用】。
  • 对于主表的筛选条件应放在where后面，不应该放在ON后面
  • 对于关联表我们要区分对待。如果是要条件查询后才连接应该把查询件放置于ON后。如果是想再连接完毕后才筛选就应把条件放置于where后面
  • 对于关联表我们其实可以先做子查询再做join
mysql> select * from s_product_brand as b left join (select * from s_brand_category where id = 1) as c  on  b.category_id = c.id  ;
+----+-------------+--------+------+--------+
| id | category_id | name   | id   | name   |
+----+-------------+--------+------+--------+
|  1 |           1 | 小米   |    1 | 手机   |
|  2 |           1 | 华为   |    1 | 手机   |
|  3 |           2 | 联想   | NULL | NULL   |
|  4 |           3 | 特步   | NULL | NULL   |
+----+-------------+--------+------+--------+
4 rows in set (0.00 sec)

mysql> select * from s_product_brand as b left join s_brand_category as c  on  b.category_id = c.id  and  c.id = 1;
+----+-------------+--------+------+--------+
| id | category_id | name   | id   | name   |
+----+-------------+--------+------+--------+
|  1 |           1 | 小米   |    1 | 手机   |
|  2 |           1 | 华为   |    1 | 手机   |
|  3 |           2 | 联想   | NULL | NULL   |
|  4 |           3 | 特步   | NULL | NULL   |
+----+-------------+--------+------+--------+
4 rows in set (0.00 sec)
MySQL 多表连接（JOIN）
yimeixiaolangzai的博客
08-19 3324
在关系型数据库中，数据通常会被存储在不同的表中。为了从这些分散的表中获取有用的信息，我们需要使用 JOIN 操作来将这些表连接在一起。JOIN 操作的本质是通过某种条件将多张表的数据结合在一起，形成一个新的结果集。MySQL 的多表连接是数据库查询中不可或缺的工具，通过 JOIN 操作，我们可以轻松地在多个表中提取、组合数据。熟练掌握不同类型的 JOIN 并理解其适用场景，可以帮助我们在复杂的业务需求中快速构建高效的查询。
MySQL大总结
耀的博客
01-23 5万+
MySQL是程序员必须要掌握的，其实语法结构相比于其他的数据结构、Java、C++来说应该算是简单的，只要多多练习，拿下它应该不成问题。本文是将我学过的面试笔试重点的知识进行归纳总结，如果你认为写的不错的话，求点赞，求收藏，感谢！！！ 一、MySQL数据库基础 1，数据库的操作 1.1、显示当前数据库 show databases; 1.2 、创建数据库 语法： CREATE DATABASE [IF NOT EXISTS] db_name [create_specif..
MySQL JOIN查询
weixin_30301449的博客
11-04 233
一般而言，如果要设计一个小型数据库（指代码量少），但又要适应海量数据及访问的性能需求，最有效的方法莫过于针对主要应用场景选择一个或几个性能优异的核心算法作为引擎，然后努力将一些非主要应用场景作为该算法的特例或变种植入到引擎当中。 MySQL、PostgreSQL 等就是这么做的。在 MySQL 的 SELECT 查询当中，其核心算法就是 JOIN 查询算法。其他的查询语句都相应向...
MySQL JOIN详解：INNER JOIN与LEFT JOIN的选择与应用
最新发布
星途码客的博客
04-09 3749
JOIN操作用于将两个或多个表中的数据基于相关字段进行组合。通过JOIN，我们可以实现：关联查询分散在不同表中的数据减少数据冗余建立复杂的数据关系模型优先考虑INNER JOIN：除非明确需要保留不匹配的行LEFT JOIN用于保留性查询：如报表、统计分析始终验证JOIN条件：确保关联字段正确且高效注意NULL处理：特别是LEFT JOIN后的WHERE条件性能监控：EXPLAIN是你的好朋友保持一致性：团队应建立统一的JOIN使用规范正确使用JOIN操作可以大幅提高SQL查询的效率和准确性。
mysql join w3c_Mysql Join的使用
weixin_29446109的博客
01-19 153
Mysql Join的使用在前几章节中，我们已经学会了如果在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。本章节我们将向大家介绍如何使用MySQL 的 JOIN 在两个或多个表中查询数据。你可以在SELECT, UPDATE 和 DELETE 语句中使用Mysql 的 join 来联合多表查询。以下我们将演示MySQL LEFT JOIN 和 JOIN 的使用...
Mysql Join 总结
qq_25911515的博客
04-07 397
Mysql 的连接 分为 ： 内连接 、外连接 、左连接、右连接、自连接 数据准备： 1.创建博客表 CREATE TABLE t_blog( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(50), typeId INT ); insert into t_blo...
MySQL联接查询算法（NLJ、BNL、BKA、HashJoin）
weixin_34326179的博客
02-20 1875
一、联接过程介绍 为了后面一些测试案例，我们事先创建了两张表，表数据如下： 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所有方法总结
08-08
在MySQL中，主要的Join方法包括INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN、SELF JOIN以及CROSS JOIN等。 INNER JOIN（内连接）：是所有Join中最常见的类型。使用INNER JOIN后，只有两个表中能够相互匹配的记录...
mysql使用from与join两表查询的区别总结
09-09
总结来说，在MySQL中，使用`FROM`和`JOIN`（不包括`LEFT JOIN`和`RIGHT JOIN`）进行两表查询时，无论是查询结果还是查询效率，它们基本上是等效的。这主要是因为MySQL的解析器在处理查询语句时，会将`FROM`和`JOIN`...
Mysql join联表及id自增实例解析
09-14
总结来说，理解和熟练运用JOIN操作、理解优化器如何选择驱动表以及掌握自增ID的限制和行为，对于优化MySQL查询性能和确保数据的正确性至关重要。在编写SQL查询时，应尽可能遵循最佳实践，以确保查询效率和数据的一致...
mysql join连接总结
w592376568的博客
10-19 116
MySQL中的JOIN
面向学姐编程
11-07 314
MySQL的官方文档上面是这么写的“MySQL executes joins between tables using a nested-loop algorithm or variations on it.”但是似乎又有其他算法，这篇文章的题目就是《MySQL Isn’t Limited to Nested-Loop Joins》，作者还在文中抱怨说“I think the MySQL docu...
MySQL的Join使用
热门推荐
慢慢来比较快
12-13 6万+
在MySQL(以5.1为例)中，表连接的语法可以参见MySQL官方手册：MySQL官方手册-JOIN 在查询中，连接的语法类似 SELECT select_expr FROM table_references table_references（对表的引用）的定义如下（也可以看成连接表达式）：（晕晕晕哈） table_references: table_referen
[Mysql]SQL 中JOIN 的总结与使用
Zemo 学习笔记
03-10 611
SQL四种JOIN的使用/区别可以描述为: left join 会从左表(shop)那里返回所有的记录，即使在右表(sale_detail)中没有匹配的行。 right join 右连接，返回右表中的所有记录，即使在左表中没有记录与它匹配 full join 全连接：返回左右表中的所有记录 inner join 内连接：在表中存在至少一个匹配
Mysql 连接join
qq_34812578的博客
07-23 943
1.inner join / join 由于mysql默认是内连接，所以，join 等同于 inner join 内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集，从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。有INNER JOIN，WHERE（等值连接），STRAIGHT_JOIN,JOIN(省略INNER)四种写法。 2.全外连接（full join）：mysql 暂不支持，可以用union模拟实现。 SELECT * FROM t_blog..
mysql join
daicooper的博客
12-02 295
MySQL使用JOIN来连接多个表查询数据，主要使用的JOIN算法只有一种，那就是nested-loop join。 nested-loop join算法实现的机制很简单，就是从驱动表中选取数据作为循环基础数据，然后以这些数据作为查询条件到下一个表中进行查询，如此往复。这个实现机制类似于foreach函数的遍历。因此带来的问题就是连接的表越多，函数嵌套的层数就越多，算法复杂度呈指数级增长。 ...
mysql join
青蛙的博客
05-09 216
SELECT * FROM  table1 t1, table2 t2WHERE t1.id=t2.id update table1 t1, table2 t2set t1.dist_filepath=t2.dist_filepath,t1.dist_filename=t2.dist_filenameWHERE t1.id=t2.id 
简单几句概括join
Cheris_0805109的博客
09-21 206
JOIN: 如果表中有至少一个匹配，则返回行 LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行 RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行 FULL JOIN: 只要其中一个表中存在匹配，就返回行 INNER JOIN：两个表交叉 转载于:https://www.cnblogs.com/tangdacheng/p/3331055.ht...
MySQL Join查询详解：内连接与外连接实例
"mysql中各种常见join连表查询实例总结" 在数据库管理中，尤其是使用MySQL进行数据查询时，JOIN操作是不可或缺的一部分，它允许我们从多个表中联接数据以获得所需的结果集。本篇文章主要关注MySQL中的JOIN类型及其...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值