0
0

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 inset(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 inset(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 NATURALJOIN score;

显示结果如下：

mysql>select * from student natural join score;
+----+----------+--------+-------+
|id| code     | name   | grade |
+----+----------+--------+-------+
|1|20181601| 张三   |55||2|20181602| 尔四   |88|
+----+----------+--------+-------+
2 rows inset(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 INNERJOIN 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 inset(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 inset(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 inset(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 inset(0.00 sec)
数据库中自然连接与内连接的区别：

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

3.外连接（outer join）

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

例：

SELECT student.name,score.code FROM student LEFTJOIN 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 inset(0.00 sec)

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

SELECT student.name,score.codeFROM student RIGHTJOIN 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 inset(0.00 sec)

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

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

SELECT student.name,score.code FROM student LEFTJOIN score ON
score.code=student.code UNIONSELECT student.name,score.code
FROM student RIGHTJOIN 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 inset(0.00 sec)

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

SELECT student.name,score.code FROM student CROSSJOIN 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 inset(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 inset(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 inset(0.00 sec)

参考链接：

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

标签： 数据库
本文转载自: https://blog.csdn.net/heshihu2019/article/details/134694435
版权归原作者 程序员达芬奇 所有， 如有侵权，请联系我们删除。
登录后发布评论

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

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

机器人技术在安全领域的重要作用

安全学习记录——网络篇（一）

图腾柱PFC：HP1010为您的电动两轮车之旅提供绿色，高效，安全的动力

单元测试数据库回滚问题

这才是单元测试，之前我们都错了

【C语言基础】：字符函数和字符串函数

ChatGPT-Next-Web SSRF漏洞+XSS漏洞复现(CVE-2023-49785)

文章导航