SQL-JOIN全解析

article/2025/9/15 2:12:27

SQL-JOIN全解析

  • 一、SQL JOIN的作用是什么？
  • 二、四种JOIN的区别
  • 三、如何使用各种join
    • （一）准备测试数据
    • （二）左连接
    • （三）右连接
    • （四）内连接
    • （五）外连接
  • 四、总结

一、SQL JOIN的作用是什么？

SQL JOIN的作用就是把来自多个表的数据行，根据一定的规则连接起来，形成一张大的数据表。

例如下面这张用烂了的图，可以帮你快速理解每个join用法的效果：

这张图描述了left join（左连接）、right join（右连接） 、inner join（内连接）、outer join（外连接）相关的7种用法。

在这里插入图片描述

我改了一版：

感觉更方便理解了

在这里插入图片描述

可以关注我公众号，回复“mysql”，可以拿到高清大图

二、四种JOIN的区别

  • 1、INNER JOIN：如果表中有至少一个匹配，则返回行；
  • 2、LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行；
  • 3、RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行；
  • 4、FULL JOIN：只要其中一个表中存在匹配，则返回行
    。

三、如何使用各种join

（一）准备测试数据

测试的数据很简单，依旧拿来在课堂上，书本上用到的老一套的数据表，学生表和成绩表来实现。

1、学生表：

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',`sno` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '学号',`sname` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '学生姓名',PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '202001', '张三');
INSERT INTO `student` VALUES (2, '202002', '李四');
INSERT INTO `student` VALUES (3, '202003', '王五');
INSERT INTO `student` VALUES (4, '202004', '赵六');
INSERT INTO `student` VALUES (5, '202005', '小明');
INSERT INTO `student` VALUES (6, '202006', '小红');
INSERT INTO `student` VALUES (7, '202007', '小刚');
INSERT INTO `student` VALUES (8, '202008', '小李');SET FOREIGN_KEY_CHECKS = 1;

2、成绩表：

-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`  (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',`sno` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '学号',`courseName` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '课程名',`grade` double(3, 0) NULL DEFAULT NULL COMMENT '成绩',PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES (1, '202001', '高数一', 90);
INSERT INTO `grade` VALUES (2, '202003', '高数二', 88);
INSERT INTO `grade` VALUES (3, '202003', '英语一', 77);
INSERT INTO `grade` VALUES (4, '202004', '英语二', 79);
INSERT INTO `grade` VALUES (5, '202002', 'C++语言设计', 87);
INSERT INTO `grade` VALUES (6, '202005', 'Java面向对象基础', 98);
INSERT INTO `grade` VALUES (7, '202006', '算法分析与实现', 76);
INSERT INTO `grade` VALUES (8, '202007', '软件工程A', 65);
INSERT INTO `grade` VALUES (9, '202007', '计算机应用与基础', 59);SET FOREIGN_KEY_CHECKS = 1;

现在的数据如下：

mysql> select * from grade;
+----+--------+------------------+-------+
| id | sno    | courseName       | grade |
+----+--------+------------------+-------+
|  1 | 202001 | 高数一           |    90 |
|  2 | 202003 | 高数二           |    88 |
|  3 | 202003 | 英语一           |    77 |
|  4 | 202004 | 英语二           |    79 |
|  5 | 202002 | C++语言设计      |    87 |
|  6 | 202005 | Java面向对象基础 |    98 |
|  7 | 202006 | 算法分析与实现   |    76 |
|  8 | 202007 | 软件工程A        |    65 |
|  9 | 202007 | 计算机应用与基础 |    59 |
+----+--------+------------------+-------+
9 rows in set (0.12 sec)mysql> 
mysql> select * from student;
+----+--------+-------+
| id | sno    | sname |
+----+--------+-------+
|  1 | 202001 | 张三  |
|  2 | 202002 | 李四  |
|  3 | 202003 | 王五  |
|  4 | 202004 | 赵六  |
|  5 | 202005 | 小明  |
|  6 | 202006 | 小红  |
|  7 | 202007 | 小刚  |
|  8 | 202008 | 小李  |
+----+--------+-------+
8 rows in set (0.12 sec)mysql>

数据结构如下：

mysql> 
mysql> desc grade;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| sno        | varchar(20) | YES  |     | NULL    |                |
| courseName | varchar(20) | YES  |     | NULL    |                |
| grade      | double(3,0) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.09 sec)mysql> 
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| sno   | varchar(20) | YES  |     | NULL    |                |
| sname | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.10 sec)mysql>

（二）左连接

在7种join的用法中，左连接的用法有两种，如下图所示：
在这里插入图片描述

第一种：

mysql> select * from student t1 -> left join grade t2 -> on t1.sno=t2.sno;
+----+--------+-------+------+--------+------------------+-------+
| id | sno    | sname | id   | sno    | courseName       | grade |
+----+--------+-------+------+--------+------------------+-------+
|  1 | 202001 | 张三  |    1 | 202001 | 高数一           |    90 |
|  3 | 202003 | 王五  |    2 | 202003 | 高数二           |    88 |
|  3 | 202003 | 王五  |    3 | 202003 | 英语一           |    77 |
|  4 | 202004 | 赵六  |    4 | 202004 | 英语二           |    79 |
|  2 | 202002 | 李四  |    5 | 202002 | C++语言设计      |    87 |
|  5 | 202005 | 小明  |    6 | 202005 | Java面向对象基础 |    98 |
|  6 | 202006 | 小红  |    7 | 202006 | 算法分析与实现   |    76 |
|  7 | 202007 | 小刚  |    8 | 202007 | 软件工程A        |    65 |
|  7 | 202007 | 小刚  |    9 | 202007 | 计算机应用与基础 |    59 |
|  8 | 202008 | 小李  | NULL | NULL   | NULL             | NULL  |
+----+--------+-------+------+--------+------------------+-------+
10 rows in set (0.10 sec)mysql>

从上面结果中可以看到我们学生中有小李，但是成绩表中并没有小李的成绩。所以会出现null的情况。


这也验证了我们前面所述的一句话：
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行；

即使成绩表中没有匹配的数据，也从左表返回所有的行。

那么在大多数情况下，我们是不让显示null的数据的，那该怎么办？
很简单，可以调换一下t1和t2的位置即可，如下实验效果：

mysql> 
mysql> select * from grade t1 -> left join student t2 -> on t1.sno=t2.sno;
+----+--------+------------------+-------+----+--------+-------+
| id | sno    | courseName       | grade | id | sno    | sname |
+----+--------+------------------+-------+----+--------+-------+
|  1 | 202001 | 高数一           |    90 |  1 | 202001 | 张三  |
|  5 | 202002 | C++语言设计      |    87 |  2 | 202002 | 李四  |
|  2 | 202003 | 高数二           |    88 |  3 | 202003 | 王五  |
|  3 | 202003 | 英语一           |    77 |  3 | 202003 | 王五  |
|  4 | 202004 | 英语二           |    79 |  4 | 202004 | 赵六  |
|  6 | 202005 | Java面向对象基础 |    98 |  5 | 202005 | 小明  |
|  7 | 202006 | 算法分析与实现   |    76 |  6 | 202006 | 小红  |
|  8 | 202007 | 软件工程A        |    65 |  7 | 202007 | 小刚  |
|  9 | 202007 | 计算机应用与基础 |    59 |  7 | 202007 | 小刚  |
+----+--------+------------------+-------+----+--------+-------+
9 rows in set (0.16 sec)mysql>

此处是重点：在Mysql5.7的官方手册中也提及到，这个优化的方式：

At the parser stage, queries with right outer join operations are converted to equivalent queries containing only left join operations. In the general case, the conversion is performed such that this right join:

在解析器阶段，具有右外部连接的查询会被转换为仅包含左连接操作的相等查询。
在一般情况下，左连接会转换成右连接

(T1, ...) RIGHT JOIN (T2, ...) ON P(T1, ..., T2, ...)

Becomes this equivalent left join:
变成下面这个等价的左连接：

(T2, ...) LEFT JOIN (T1, ...) ON P(T1, ..., T2, ...)

可以看到在转换的时候，会把t1变成t2，把t2的位置换成t1的位置。

第二种：

mysql> 
mysql> select * from student t1 -> left join grade t2 -> on t1.sno=t2.sno-> where t2.sno is null;
+----+--------+-------+------+------+------------+-------+
| id | sno    | sname | id   | sno  | courseName | grade |
+----+--------+-------+------+------+------------+-------+
|  8 | 202008 | 小李  | NULL | NULL | NULL       | NULL  |
+----+--------+-------+------+------+------------+-------+
1 row in set (19.59 sec)mysql>

从结果上看，很清楚，只查出来了sno为null的数据。
如果not null呢？

mysql> 
mysql> select * from student t1 left join grade t2 on t1.sno=t2.snowhere t2.sno is NOT null;
+----+--------+-------+----+--------+------------------+-------+
| id | sno    | sname | id | sno    | courseName       | grade |
+----+--------+-------+----+--------+------------------+-------+
|  1 | 202001 | 张三  |  1 | 202001 | 高数一           |    90 |
|  3 | 202003 | 王五  |  2 | 202003 | 高数二           |    88 |
|  3 | 202003 | 王五  |  3 | 202003 | 英语一           |    77 |
|  4 | 202004 | 赵六  |  4 | 202004 | 英语二           |    79 |
|  2 | 202002 | 李四  |  5 | 202002 | C++语言设计      |    87 |
|  5 | 202005 | 小明  |  6 | 202005 | Java面向对象基础 |    98 |
|  6 | 202006 | 小红  |  7 | 202006 | 算法分析与实现   |    76 |
|  7 | 202007 | 小刚  |  8 | 202007 | 软件工程A        |    65 |
|  7 | 202007 | 小刚  |  9 | 202007 | 计算机应用与基础 |    59 |
+----+--------+-------+----+--------+------------------+-------+
9 rows in set (19.46 sec)mysql>

这就很神奇了，居然和我们上一种想要的最终结果一样。那么这个sql就是舍弃掉了为null的数据。

（三）右连接

在这里插入图片描述

这个的用法和左连接正好相反，可以在脑子中想想一下。

不罗嗦了，直接看效果吧。

mysql> 
mysql> select * from student t1 right join grade t2 on t1.sno=t2.sno;
+----+--------+-------+----+--------+------------------+-------+
| id | sno    | sname | id | sno    | courseName       | grade |
+----+--------+-------+----+--------+------------------+-------+
|  1 | 202001 | 张三  |  1 | 202001 | 高数一           |    90 |
|  2 | 202002 | 李四  |  5 | 202002 | C++语言设计      |    87 |
|  3 | 202003 | 王五  |  2 | 202003 | 高数二           |    88 |
|  3 | 202003 | 王五  |  3 | 202003 | 英语一           |    77 |
|  4 | 202004 | 赵六  |  4 | 202004 | 英语二           |    79 |
|  5 | 202005 | 小明  |  6 | 202005 | Java面向对象基础 |    98 |
|  6 | 202006 | 小红  |  7 | 202006 | 算法分析与实现   |    76 |
|  7 | 202007 | 小刚  |  8 | 202007 | 软件工程A        |    65 |
|  7 | 202007 | 小刚  |  9 | 202007 | 计算机应用与基础 |    59 |
+----+--------+-------+----+--------+------------------+-------+
9 rows in set (19.49 sec)mysql>

从上面效果上可以看到只匹配到了成绩表中有的数据，小李就没有显示。
也验证了：
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行；

（四）内连接

在这里插入图片描述

INNER JOIN：如果表中有至少一个匹配，则返回行；

mysql> select * from student t1 inner join grade t2 on t1.sno=t2.sno;
+----+--------+-------+----+--------+------------------+-------+
| id | sno    | sname | id | sno    | courseName       | grade |
+----+--------+-------+----+--------+------------------+-------+
|  1 | 202001 | 张三  |  1 | 202001 | 高数一           |    90 |
|  3 | 202003 | 王五  |  2 | 202003 | 高数二           |    88 |
|  3 | 202003 | 王五  |  3 | 202003 | 英语一           |    77 |
|  4 | 202004 | 赵六  |  4 | 202004 | 英语二           |    79 |
|  2 | 202002 | 李四  |  5 | 202002 | C++语言设计      |    87 |
|  5 | 202005 | 小明  |  6 | 202005 | Java面向对象基础 |    98 |
|  6 | 202006 | 小红  |  7 | 202006 | 算法分析与实现   |    76 |
|  7 | 202007 | 小刚  |  8 | 202007 | 软件工程A        |    65 |
|  7 | 202007 | 小刚  |  9 | 202007 | 计算机应用与基础 |    59 |
+----+--------+-------+----+--------+------------------+-------+
9 rows in set (0.12 sec)mysql>

（五）外连接

这一种在Mysql中是不支持的，可以在SQL Server上测试。这里就不测试了。

四、总结

  • 1、INNER JOIN：如果表中有至少一个匹配，则返回行；
  • 2、LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行；
  • 3、RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行；
  • 4、FULL JOIN：只要其中一个表中存在匹配，则返回行
    。

欢迎一起学习，一起交流，一起进步。

关注我微信公众号第一时间推送给你精彩内容哦：

回复菜单，更有好礼，惊喜在等着你。

在这里插入图片描述

快来我粉丝群：每天欢快的玩耍（微信扫描二维码即可加入，群马上满，抓紧啦！！！）
在这里插入图片描述


http://chatgpt.dhexx.cn/article/IJd5B5nb.shtml

相关文章

SQL中join语句详解

SQL中join语句详解

1.inner join(内连接) 只返回匹配的行。 select * from table_a a inner join table_b b on a.name b.name 2.left join(左外连接) 返回左表的全部数据，和右表中满足on条件的行，如果左表的行在右表中没有匹配的数据，那么这一行中右表对应…
阅读更多...
SQL Server 数据库常用操作：多表联查（JOIN...ON语句的使用）

SQL Server 数据库常用操作：多表联查（JOIN...ON语句的使用）

1.使用传统连接方式查询 (1). 有两张表Book(BookID,BookName,TypeID,AuthorID,…)，BookType(TypeID,TypeName)，查询每本书的书名和图书类型。 SELECT BookName, TypeName FROM Book, BookType WHERE Book.TypeID BookType.TypeID(2). 有三张表Book(Boo…
阅读更多...
Oracle SQL中join方式总结

Oracle SQL中join方式总结

在ORACLE数据库中，表与表之间的SQL JOIN方式有多种（不仅表与表，还可以表与视图、物化视图等联结）。SQL JOIN其实是一个逻辑概念，像NEST LOOP JOIN、 HASH JOIN等是表连接的物理实现方式。 为了更直观的了解以上join方式…
阅读更多...
SQL语句中的join用法

SQL语句中的join用法

SQL中join的各种用法 1.自然连接（natural join） 自然连接将表中具有相同名称的列自动进行匹配，自然连接不必指定任何同等连接条件也不能认为指定哪些列需要被匹配，自然连接得到的结果表中，两表中名称相同的列只出现一次…
阅读更多...
sql中join的各种用法

sql中join的各种用法

sql中join的用法 sql中join的含义可以理解为单词“join”，用来连接两张表，join所有连接方式可以分为： 内连接，外连接，右连接，左连接，自然连接 上面这张图已经很清晰的表明了各种连接方式的语法…
阅读更多...
SQL Server中JOIN的使用方法总结

SQL Server中JOIN的使用方法总结

JOIN 分为：内连接（INNER JOIN）、外连接（OUTER JOIN）。 其中，外连接分为：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、全外连接&…
阅读更多...
sql 语法中 join 的所有用法总结（简单例子）

sql 语法中 join 的所有用法总结（简单例子）

join 常见的用法有： 目录 left join (left outer join) right join (right outer join) join (inner join) full join（full outer join 、outer join） cross join 说明：left join 就是 left outer join、 right join 就是 r…
阅读更多...
SQL语句各种join用法（图文）

SQL语句各种join用法（图文）

1、INNER JOIN(内连接) select * from table A A inner join table B B on A.key B.key //内连接 2、LEFT JOIN(左连接) select * from table A A left join table B B on A.key B.key //左连接 3、RIGHT JOIN(右连接) select * from table A A right join table B B on A…
阅读更多...
sql join中on条件后接and和where

sql join中on条件后接and和where

目录 场景1：left join on a.xx b.xx and a.xx2 aa 场景2：left join on a.xx b.xx and b.xx2 aa 场景3：left join on a.xx b.xx where b.xx2 aa 场景4：inner join on a.xx b.xx where a.xx2 aa 场景5：…
阅读更多...
详解SQL中的各种连接(JOIN)方法

详解SQL中的各种连接(JOIN)方法

详解SQL中的各种连接(JOIN)方法 简介 有时候为了得到完整的结果，我们需要从两个或更多的表中获取结果，而 SQL 就提供了 JOIN 子句，用来把来自两个或者多个表的行结合起来（基于这些表之间的共同字段） 数据库中的表可以…
阅读更多...
SQL语句中JOIN的用法

SQL语句中JOIN的用法

记录：257 写SQL最高境界：SELECT * FROM 表名。当然这是一句自嘲。探究一下SQL语句中JOIN的用法，直到经历这个场景，变得想验证一下究竟。 一、场景 把关系型数据库A中表TEST_TB01和TEST_TB02迁移到大数据平台M（MaxCom…
阅读更多...
SQL中连接（JOIN）子句介绍

SQL中连接（JOIN）子句介绍

本文主要介绍 SQL（Structured Query Language）中连接（JOIN）子句的相关知识，同时通过用法示例介绍连接的常见用法。 说明：本文的用法示例是面向 MySQL 数据库的。 1 概述 SQL 中 JOIN 子句用于把来自两个…
阅读更多...
SQL 中 JOIN 的用法

SQL 中 JOIN 的用法

一、概要 JOIN对于接触过数据库的人，这个词都不陌生，而且很多人很清楚各种JOIN，还有很多人对这个理解也不是很透彻，这次就说说JOIN操作。 图片是很容易被接受和理解，所以尝试使用图片来说明一下。 二、JOIN分类 客…
阅读更多...
思科三层交换机IPv6静态和默认路由配置

思科三层交换机IPv6静态和默认路由配置

基础配置： SWA： Switch>ena Switch#conf t Switch(config)#host SWA SWA(config)#vlan 10 SWA(config-vlan)#vlan 100 SWA(config-vlan)#int vlan 10 SWA(config-if)#ipv6 add 2001:10::1/64 SWA(config-if)#int vlan 100 SWA(config-if)#i…
阅读更多...
Cisco 三层交换机与路由器ospf协议配置多区域area

Cisco 三层交换机与路由器ospf协议配置多区域area

分布操作： 1.划分vlan 2.ospf、area 3.测试ping 三层交换机2 路由器2 PC机4 路由器使用WIC-2T模块 使用DCE串口线连接 其余设备间均用直通线连接 各种预配置信息： 设备从左往右，从上往下依次为 S1，R2，R3&#…
阅读更多...
三层交换机配置的步骤

三层交换机配置的步骤

网管不会配置三层交换机怎么办？ 学啊！ 交换机是企业组网的重要设备，掌握交换机配置是作为网管的必备技能。这里以三层交换机的配置为例子，说一说配置的步骤。 配置管理地址 每个交换机需要配置一个管理地址，方便后期的…
阅读更多...
思科利用三层交换机实现 VLAN 间路由

思科利用三层交换机实现 VLAN 间路由

一、实验目的 掌握三层交换机基本配置方法 掌握三层交换机VLAN路由的配置方法 通过三层交换机实现VLAN间相互通信 二、实验原理 三层交换机具备网络层的功能，实现VLAN相互访问的原理是：利用三层交换机的路由功能，通过识别数据包的IP地址…
阅读更多...
Cisco Packet Tracer中配置三层交换机

Cisco Packet Tracer中配置三层交换机

三层交换机介绍： 三层交换机就是具有部分路由器功能的交换机，三层交换机的最重要目的是加快大型局域网内部的数据交换，所具有的路由功能也是为这目的服务的，能够做到一次路由，多次转发。对于数据包转发等规律性的过程…
阅读更多...
思科三层交换机配置SVI 实现VLAN 间路由

思科三层交换机配置SVI 实现VLAN 间路由

本次SVI实验参数如图： 依次三层交换我们命名为SW1，二层交换命名为SW2 下面我们开始先对二层交换机SW2进行配置 Switch>en Switch#conf t Switch(config)#host SW2 SW2(config)#vlan 20 SW2(config-vlan)#vlan 30 SW2(config-vlan)#vlan 40 SW2(config…
阅读更多...
关于Cisco Packet Tracer三层交换机路由配置

关于Cisco Packet Tracer三层交换机路由配置

步骤1：交换机trunk配置 交换机sw1配置 Switch>en Switch#conf t Switch(config)#ho sw1 sw1(config)#int f0/2 sw1(config-if)#sw mo tr sw1(config-if)#exit 交换机sw2配置 Switch>en Switch#conf t Switch(config)#ho sw2 sw2(config)#int f0/2 sw2(config-…
阅读更多...
推荐文章

Copyright @ 2022~2023