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

博主： dafenqi
发布时间：2023 年 11 月 29 日
132 次浏览
暂无评论
10741字数
分类： mysql

## 1. 自然连接（natural join）（内连接）
 学生表
```bash
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);
```

成绩表

```bash
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语句，它默认比较两张表里相同的列。** 
```sql
SELECT * FROM student NATURAL JOIN score;
```

显示结果如下：
```bash
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，形成的中间表为两个表的笛卡尔积。
```sql
SELECT student.name,score.code FROM student,score WHERE score.code=student.code;
```
  
### 2）显示的内连接，一般称为内连接，有INNER JOIN，形成的中间表为两个表经过ON条件过滤后的笛卡尔积。
```sql
SELECT student.name,score.code FROM student INNER JOIN score ON score.code=student.code;
```

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

```bash
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 score inner join student;
+----+--------+----------+----+-------+----------+
| 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](https://baike.baidu.com/item/%E8%87%AA%E8%BF%9E%E6%8E%A5/2556770) 
新的学生表
```bash
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);
```

**问题：查询显示成绩小于小明的学生和成绩？** 
当表中的某一个字段与这个表中另外字段的相关时，我们可能用到 **自连接** 。
```bash
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）：返回指定左表的全部行+右表对应的行，如果左表中数据在右表中没有与其相匹配的行，则在查询结果集中显示为空值。 
 例：
 ```sql
 SELECT student.name,score.code FROM student LEFT JOIN score ON score.code=student.code; 
``` 
 查询结果如下： 
 
```bash
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）：与左外连接类似，是左外连接的反向连接。
```sql
SELECT student.name,score.codeFROM student RIGHT JOIN score ON score.code=student.code;
```

```bash
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中，可通过求左外连接与右外连接的合集来实现全外连接。 
 例：
```sql
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;
``` 
```bash
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）：相当与笛卡尔积，左表和右表组合。 （内连接）
```sql
SELECT student.name,score.code FROM student CROSS JOIN score ON score.code=student.code; 
```

```bash
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)

```

## 参考链接：
 [自然连接、内连接、外连接（左外连接、右外连接、全外连接）、交叉连接](https://javaforall.cn/178803.html) 
 [百科自连接](https://baike.baidu.com/item/%E8%87%AA%E8%BF%9E%E6%8E%A5/2556770) 
 [数据库中自然连接与内连接的区别](https://edu.iask.sina.com.cn/jy/20zbg7XLTb.html) 
  [MySQL数据库的46种基本语法](https://zhuanlan.zhihu.com/p/501367084) 
  [MySQL 自连接讲解](https://blog.csdn.net/weixin_62332711/article/details/128602423)

最后修改：2023 年 12 月 05 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

jprfffedhg
你的才华让人瞩目，期待你的更多文章。 https://w...
pkrqcpalxw
你的文章让我感受到了无尽的欢乐，谢谢分享。 http:/...
urjitfqbzy
你的文章让我感受到了生活的美好，谢谢！ https://...
bfgsfrpbas
你的文章让我心情愉悦，每天都要来看一看。 https:/...
xvyzshabcu
《冰山女神赖上我合集版》短片剧高清在线免费观看：https:/...

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

dafenqi • 2023 年 11 月 29 日

INSERT INTO student (name, code) VALUES
    ('张三', 20181601),
    ('尔四', 20181602),
    ('小红', 20181603),
    ('小明', 20181604),
    ('小青', 20181605);
```

成绩表

INSERT INTO score (id, grade, code) VALUES
(1, 55, 20181601),
(2, 88, 20181602),
(3, 99, 20181605),
(4, 33, 20181611);
```

**自然连接不用指定连接列，也不能使用ON语句，它默认比较两张表里相同的列。** 
```sql
SELECT * FROM student NATURAL JOIN score;
```

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

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

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

```

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

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

PHP 数据类型

当你在浏览器扣下域名时。。。

PHP switch 替代品 match

变量在内存中的表现形式

简易发邮件

通俗的PHP正则表达式手册

PHP中pack()函数说明和例子

PHP制作一个简单的日历

编程书籍收藏集

npm下载速度慢的处理方法

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

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

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

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款