SQL中 JOIN 的两种连接类型：内连接（自然连接、自连接、交叉连接）、外连接（左外连接、右外连接、全外连接）

2023-11-30 05:36
文章标签 sql 类型 连接 两种 join 自然 database 交叉 左外 右外 全外

本文主要是介绍SQL中 JOIN 的两种连接类型：内连接（自然连接、自连接、交叉连接）、外连接（左外连接、右外连接、全外连接），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

SQL中 JOIN 的两种连接类型：内连接（自然连接、自连接、交叉连接）、外连接（左外连接、右外连接、全外连接）

1. 自然连接（natural join）（内连接）

学生表

mysql> select * from student;
+----+--------+----------+
| id | name   | code     |
+----+--------+----------+
|  1 | 张三   | 20181601 |
|  2 | 尔四   | 20181602 |
|  3 | 小红   | 20181603 |
|  4 | 小明   | 20181604 |
|  5 | 小青   | 20181605 |
+----+--------+----------+
5 rows in set (0.00 sec)CREATE TABLE `student` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,`code` int DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;INSERT INTO student (name, code) VALUES('张三', 20181601),('尔四', 20181602),('小红', 20181603),('小明', 20181604),('小青', 20181605);

成绩表

mysql> select * from score;
+----+-------+----------+
| id | grade | code     |
+----+-------+----------+
|  1 |    55 | 20181601 |
|  2 |    88 | 20181602 |
|  3 |    99 | 20181605 |
|  4 |    33 | 20181611 |
+----+-------+----------+
4 rows in set (0.00 sec)CREATE TABLE `score` (`id` int NOT NULL AUTO_INCREMENT,`grade` int DEFAULT NULL,`code` int DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;INSERT INTO score (id, grade, code) VALUES
(1, 55, 20181601),
(2, 88, 20181602),
(3, 99, 20181605),
(4, 33, 20181611);

自然连接不用指定连接列，也不能使用ON语句，它默认比较两张表里相同的列。

SELECT * FROM student NATURAL JOIN score;

显示结果如下：

mysql> select * from student natural join score;
+----+----------+--------+-------+
| id | code     | name   | grade |
+----+----------+--------+-------+
|  1 | 20181601 | 张三   |    55 |
|  2 | 20181602 | 尔四   |    88 |
+----+----------+--------+-------+
2 rows in set (0.00 sec)

–自然连接 natural join
自动判断连接条件完成连接.
–自然内连接 natural inner join
select *|字段列表 from 左表 natural [inner] join 右表;
自然内连接其实就是内连接,这里的匹配条件是由系统自动指定.

–自然外连接 natural outer join
自然外连接分为自然左外连接和自然右外连接.匹配条件也是由系统自动指定.

–自然左外连接 natural left join
select *|字段列表 from 左表 natural left [outer] join 右表;

–自然右外连接 natural right join
select *|字段列表 from 右表 natural right [outer] join 左表;

2. 内连接（inner join）

和自然连接区别之处在于内连接可以自定义两张表的不同列字段。
内连接有两种形式：显式和隐式。

1）隐式的内连接，没有INNER JOIN，形成的中间表为两个表的笛卡尔积。

SELECT student.name,score.code FROM student,score WHERE score.code=student.code;

2）显示的内连接，一般称为内连接，有INNER JOIN，形成的中间表为两个表经过ON条件过滤后的笛卡尔积。

SELECT student.name,score.code FROM student INNER JOIN score ON score.code=student.code;

例：以下1）、2）语句执行结果相同。

mysql> SELECT student.name,score.code FROM student,score WHERE score.code=student.code;
mysql> SELECT student.name,score.code FROM student INNER JOIN score ON score.code=student.code;
+--------+----------+
| name   | code     |
+--------+----------+
| 张三   | 20181601 |
| 尔四   | 20181602 |
| 小青   | 20181605 |
+--------+----------+
3 rows in set (0.00 sec)mysql> select student.*,score.* from student inner join score;
mysql> select student.*,score.* from student inner join score;
+----+--------+----------+----+-------+----------+
| id | name   | code     | id | grade | code     |
+----+--------+----------+----+-------+----------+
|  1 | 张三   | 20181601 |  4 |    33 | 20181611 |
|  1 | 张三   | 20181601 |  3 |    99 | 20181605 |
|  1 | 张三   | 20181601 |  2 |    88 | 20181602 |
|  1 | 张三   | 20181601 |  1 |    55 | 20181601 |
|  2 | 尔四   | 20181602 |  4 |    33 | 20181611 |
|  2 | 尔四   | 20181602 |  3 |    99 | 20181605 |
|  2 | 尔四   | 20181602 |  2 |    88 | 20181602 |
|  2 | 尔四   | 20181602 |  1 |    55 | 20181601 |
|  3 | 小红   | 20181603 |  4 |    33 | 20181611 |
|  3 | 小红   | 20181603 |  3 |    99 | 20181605 |
|  3 | 小红   | 20181603 |  2 |    88 | 20181602 |
|  3 | 小红   | 20181603 |  1 |    55 | 20181601 |
|  4 | 小明   | 20181604 |  4 |    33 | 20181611 |
|  4 | 小明   | 20181604 |  3 |    99 | 20181605 |
|  4 | 小明   | 20181604 |  2 |    88 | 20181602 |
|  4 | 小明   | 20181604 |  1 |    55 | 20181601 |
|  5 | 小青   | 20181605 |  4 |    33 | 20181611 |
|  5 | 小青   | 20181605 |  3 |    99 | 20181605 |
|  5 | 小青   | 20181605 |  2 |    88 | 20181602 |
|  5 | 小青   | 20181605 |  1 |    55 | 20181601 |
+----+--------+----------+----+-------+----------+
20 rows in set (0.00 sec)

拓展

自连接（内连接）

https://baike.baidu.com/item/%E8%87%AA%E8%BF%9E%E6%8E%A5/2556770
新的学生表

CREATE TABLE `new_student` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,`code` int DEFAULT NULL,`grade` int DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;INSERT INTO `test`.`new_student`(`id`, `name`, `code`, `grade`) VALUES (1, '张三', 20181601, 55);
INSERT INTO `test`.`new_student`(`id`, `name`, `code`, `grade`) VALUES (2, '尔四', 20181602, 88);
INSERT INTO `test`.`new_student`(`id`, `name`, `code`, `grade`) VALUES (3, '小红', 20181603, 77);
INSERT INTO `test`.`new_student`(`id`, `name`, `code`, `grade`) VALUES (4, '小明', 20181604, 66);
INSERT INTO `test`.`new_student`(`id`, `name`, `code`, `grade`) VALUES (5, '小青', 20181605, 99);

问题：查询显示成绩小于小明的学生和成绩？
当表中的某一个字段与这个表中另外字段的相关时，我们可能用到 自连接 。

mysql> select * from new_student;
+----+--------+----------+-------+
| id | name   | code     | grade |
+----+--------+----------+-------+
|  1 | 张三   | 20181601 |    55 |
|  2 | 尔四   | 20181602 |    88 |
|  3 | 小红   | 20181603 |    77 |
|  4 | 小明   | 20181604 |    66 |
|  5 | 小青   | 20181605 |    99 |
+----+--------+----------+-------+
5 rows in set (0.00 sec)mysql> select st2.name, st2.grade from new_student st1, new_student st2 where st1.name='小明' and st1.grade < st2.grade;
+--------+-------+
| name   | grade |
+--------+-------+
| 尔四   |    88 |
| 小红   |    77 |
| 小青   |    99 |
+--------+-------+
3 rows in set (0.00 sec)
数据库中自然连接与内连接的区别：

1、自然连接一定是内连接，内连接不一定是自然连接；
2、内连接不把重复的属性除去，自然连接要把重复的属性除去；
3、内连接要求相等的分量，不一定是公共属性，自然连接要求相等的分量必须是公共属性；
4、内连接不把重复的属性除去，自然连接要把重复的属性除去。

3.外连接（outer join）

1）左外连接（left outer join）：返回指定左表的全部行+右表对应的行，如果左表中数据在右表中没有与其相匹配的行，则在查询结果集中显示为空值。

例：

SELECT student.name,score.code FROM student LEFT JOIN score ON score.code=student.code;

查询结果如下：

mysql> select student.name,score.code from student left join score on score.code=student.code;
+--------+----------+
| name   | code     |
+--------+----------+
| 张三   | 20181601 |
| 尔四   | 20181602 |
| 小红   |     NULL |
| 小明   |     NULL |
| 小青   | 20181605 |
+--------+----------+
5 rows in set (0.00 sec)

2）右外连接（right outer join）：与左外连接类似，是左外连接的反向连接。

SELECT student.name,score.codeFROM student RIGHT JOIN score ON score.code=student.code;

mysql> select student.name,score.code from student right join score on score.code=student.code;
+--------+----------+
| name   | code     |
+--------+----------+
| 张三   | 20181601 |
| 尔四   | 20181602 |
| 小青   | 20181605 |
| NULL   | 20181611 |
+--------+----------+
4 rows in set (0.00 sec)

3）全外连接（full outer join）：把左右两表进行自然连接，左表在右表没有的显示NULL，右表在左表没有的显示NULL。（MYSQL不支持全外连接，适用于Oracle和DB2。）

在MySQL中，可通过求左外连接与右外连接的合集来实现全外连接。
例：

SELECT student.name,score.code FROM student LEFT JOIN score ON
score.code=student.code UNION SELECT student.name,score.code
FROM student RIGHT JOIN score ON score.code=student.code;

mysql> select student.name,score.code from student left join score on score.code=student.code union select student.name,score.code from student right join score on score.code=student.code;
+--------+----------+
| name   | code     |
+--------+----------+
| 张三   | 20181601 |
| 尔四   | 20181602 |
| 小红   |     NULL |
| 小明   |     NULL |
| 小青   | 20181605 |
| NULL   | 20181611 |
+--------+----------+
6 rows in set (0.00 sec)

4.交叉连接（cross join）：相当与笛卡尔积，左表和右表组合。 （内连接）

SELECT student.name,score.code FROM student CROSS JOIN score ON score.code=student.code; 

mysql> select student.name,score.code from student cross join score on score.code=student.code;
+--------+----------+
| name   | code     |
+--------+----------+
| 张三   | 20181601 |
| 尔四   | 20181602 |
| 小青   | 20181605 |
+--------+----------+
3 rows in set (0.00 sec)mysql> select student.*,score.* from student cross join score on score.code=student.code;
+----+--------+----------+----+-------+----------+
| id | name   | code     | id | grade | code     |
+----+--------+----------+----+-------+----------+
|  1 | 张三   | 20181601 |  1 |    55 | 20181601 |
|  2 | 尔四   | 20181602 |  2 |    88 | 20181602 |
|  5 | 小青   | 20181605 |  3 |    99 | 20181605 |
+----+--------+----------+----+-------+----------+
3 rows in set (0.00 sec)mysql> select student.*,score.* from student cross join score;
+----+--------+----------+----+-------+----------+
| id | name   | code     | id | grade | code     |
+----+--------+----------+----+-------+----------+
|  1 | 张三   | 20181601 |  4 |    33 | 20181611 |
|  1 | 张三   | 20181601 |  3 |    99 | 20181605 |
|  1 | 张三   | 20181601 |  2 |    88 | 20181602 |
|  1 | 张三   | 20181601 |  1 |    55 | 20181601 |
|  2 | 尔四   | 20181602 |  4 |    33 | 20181611 |
|  2 | 尔四   | 20181602 |  3 |    99 | 20181605 |
|  2 | 尔四   | 20181602 |  2 |    88 | 20181602 |
|  2 | 尔四   | 20181602 |  1 |    55 | 20181601 |
|  3 | 小红   | 20181603 |  4 |    33 | 20181611 |
|  3 | 小红   | 20181603 |  3 |    99 | 20181605 |
|  3 | 小红   | 20181603 |  2 |    88 | 20181602 |
|  3 | 小红   | 20181603 |  1 |    55 | 20181601 |
|  4 | 小明   | 20181604 |  4 |    33 | 20181611 |
|  4 | 小明   | 20181604 |  3 |    99 | 20181605 |
|  4 | 小明   | 20181604 |  2 |    88 | 20181602 |
|  4 | 小明   | 20181604 |  1 |    55 | 20181601 |
|  5 | 小青   | 20181605 |  4 |    33 | 20181611 |
|  5 | 小青   | 20181605 |  3 |    99 | 20181605 |
|  5 | 小青   | 20181605 |  2 |    88 | 20181602 |
|  5 | 小青   | 20181605 |  1 |    55 | 20181601 |
+----+--------+----------+----+-------+----------+
20 rows in set (0.00 sec)

参考链接：

自然连接、内连接、外连接（左外连接、右外连接、全外连接）、交叉连接
百科自连接
数据库中自然连接与内连接的区别
MySQL数据库的46种基本语法
MySQL 自连接讲解

这篇关于SQL中 JOIN 的两种连接类型：内连接（自然连接、自连接、交叉连接）、外连接（左外连接、右外连接、全外连接）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/435823。

相关文章

SQL中redo log 刷⼊磁盘的常见方法

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例（伪代码）在数据库系统中，r
阅读更多...
mysql中的group by高级用法

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使
阅读更多...
Mysql用户授权(GRANT)语法及示例解读

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》：本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权（GRANT）语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT
阅读更多...
Mysql如何解决死锁问题

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》：本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1
阅读更多...
SQL BETWEEN 的常见用法小结

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中，BETWEEN是一个操作符，用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用
阅读更多...
MySQL索引的优化之LIKE模糊查询功能实现

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》：本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分
阅读更多...
MySql match against工具详细用法

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引（Full-Textindex）的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七
阅读更多...
数据库面试必备之MySQL中的乐观锁与悲观锁

数据库面试必备之MySQL中的乐观锁与悲观锁

《数据库面试必备之MySQL中的乐观锁与悲观锁》：本文主要介绍数据库面试必备之MySQL中乐观锁与悲观锁的相关资料,乐观锁适用于读多写少的场景,通过版本号检查避免冲突,而悲观锁适用于写多读少且对数... 目录一、引言二、乐观锁（一）原理（二）应用场景（三）示例代码三、悲观锁（一）原理（二）应用场景（三）示例
阅读更多...
SQL表间关联查询实例详解

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括：左连接（leftjoin）、右连接（rightjoin）、全连接（fulljoin）、内连接（innerjoin）、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表
阅读更多...
SQL server配置管理器找不到如何打开它

SQL server配置管理器找不到如何打开它

《SQLserver配置管理器找不到如何打开它》最近遇到了SQLserver配置管理器打不开的问题,尝试在开始菜单栏搜SQLServerManager无果,于是将自己找到的方法总结分享给大家,对SQ... 目录方法一：桌面图标进入方法二：运行窗口进入方法三：查找文件路径方法四：检查 SQL Server 安
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2