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

已于 2023-11-29 21:29:00 修改
阅读量3.1k 收藏 12
点赞数 1
文章标签： 数据库
于 2023-11-29 17:00:46 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/heshihu2019/article/details/134694435
版权

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 自连接讲解

程序员达芬奇
关注
  • 1
    点赞
  • 踩
  • 12
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL连接表(内连接、左连接、右连接、交叉连接、全外连接)
m0_67393295的博客
06-12 2015
分享一些自己的连表总结在关系数据库中，数据分布在多个逻辑表中。 要获得完整有意义的数据集，需要使用连接来查询这些表 中的数据。 SQL Server支持多种每种连接类型指定SQL Server如何使用一个表中的数据来选择另一个表中的行下图说明了两个结果集的内联接的结果： 使用两个 INNER JOIN 子句来查询三个表中的数据： 使用两个 INNER JOIN 子句来查询三个表中的数据： INNER JOIN 关键字在表中存在至少一个匹配时返回行。 B. SQL Server左连接 LEFT JOIN 子
Oracle数据库：自然连接natural join，using语句，注意避免写交叉连接
weixin_46838716的博客
10-28 2206
Oracle数据库：自然连接natural join，using语句，注意避免写交叉连接
MySQL系列之Natural Join用法
Nicky's blog
11-10 2万+
Natural join即自然连接，natural join等同于inner join或inner using，其作用是将两个表中具有相同名称的列进行匹配
SQL JOIN (数据库的关联查询)
最新发布
2301_81034686的博客
05-31 1305
在数据库查询数据过程中往往需要将不同表的某个字段组合成一个新的表作为查询结果返回，通过JOIN能够同时看到我们想看到的有关联的信息。
超级通俗易懂的自然连接
LinXiaoTaoZi的博客
09-10 4万+
突然遇到了就记录下来 原题：求两个关系RA和RB的自然连接，书本上的原题是这样的（有点歪，将就一下） 解题思路： 1、首先找到RA 和RB 中相同的列，是不是只有A和C两列是他们都有的； 2、我们就只看两个关系式中的A列和C列所在的行； 3、从RA开始看，RA的A,C两列所在第一行值是 ac。于是我们去RB中找到A,C两列同样值为ac的行； 4、我们发现RB中的第一行A,C列的值和RA中第一行ac的值一样。所以需要将他们连接起来就是： a b c a c d。但是，需要做去重...
9.3.2 自然连接（NATURAL JOIN）
我的博客
06-01 5034
9.3.2  自然连接（NATURAL JOIN） 自然连接（NATURAL JOIN）是一种特殊的等价连接，它将表中具有相同名称的列自动进行记录匹配。自然连接不必指定任何同等连接条件。图9.9给出了典型的自然连接示意图。    图9.9  自然连接 自然连接自动判断相同名称的列，而后形成匹配。缺点是，虽然可以指定查询结果
数据库关系运算：自然连接，投影，交，除，并
热门推荐
现从事游戏开发领域 | 团队项目be_with开发进行中 web方向感兴趣者私 ——We can do all things.
04-23 4万+
1.自然连接 自然连接（natural join） 自然连接是一种特殊的等值连接，他要求两个关系表中进行比较的必须是相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。 sql语句：Select …… from 表1 natural join 表2 结果： 2.投影 投影是选取关系中列的子集。设模式R上关系r，X是R上属性的子集（x就是列），r到 X上的投影r`表示为： 投影操作是...
SQL连接的理解和使用（内连接：自然连接&等值连接，外连接：左连接&右连接&全外连接）
海蒂
04-03 9004
连接（Join）是指将两个或多个表中的数据按照一定的条件关联起来，形成一个新的虚拟表。也可以理解为两个表的笛卡尔积中筛选符合条件的行。什么是笛卡尔积？
数据库:笛卡尔积、内连接、左连接、右连接
12-14
本篇文章将深入探讨四种基本的连接类型：笛卡尔积、内连接、左连接和右连接，以及它们在实际数据库操作中的应用。 首先，我们来理解笛卡尔积（Cartesian Product）。笛卡尔积源自数学概念，它表示两个集合的所有...
SQL的几种连接：内连接、左联接、右连接、全连接、交叉连接
06-09
根据连接的方式和结果，SQL连接可以分为内连接、左连接、右连接、全连接和交叉连接等多种类型。 内连接 内连接是最基本的连接类型，它将两个表格中的数据根据某个条件连接起来。内连接可以分为等值连接、不等值...
sql内连接和外连接
12-14
外连接分为三种类型：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。 **语法**： - **左外连接**： ```sql SELECT column_name(s) FROM table1 LEFT OUTER JOIN ...
SQL语句中不同的连接JOIN及join的用法
09-09
自然连接是一种特殊的内连接，它基于两个表中具有相同名称的列进行匹配，无需指定等价连接条件。但是，由于它可能会导致意外的结果，因为它会自动匹配所有同名列，因此在实际应用中并不常见。 6. **CROSS JOIN** ...
Java学习日志(三十一): 多表查询，内连接，外连接
12-14
内连接分为隐式和显式两种形式。隐式内连接通过在`WHERE`子句中设置条件来实现，如：`SELECT * FROM 表A, 表B WHERE 表A.主键 = 表B.外键;` 显式内连接使用`JOIN`关键字，如：`SELECT * FROM 表A INNER JOIN 表B ON ...
SQL自然联接（NATURAL JOIN）
在路上
08-31 1万+
SQL> create table lm_t10(id number,name number); Table created. SQL> create table lm_t11(id number,name number); Table created. 1.纯自然联接
数据库的连接（自然连接、内连接、左外连接、右外连接）
qq_52947919的博客
12-06 3489
自然连接和内连接的两个表都有可能丢数据行，而左外连接跟他们的区别就是保留左表的所有数据，没有任何重复的行连接后他会将右表的列的数据置为NULL。跟自然连接不同，自然连接将重复的列合并成一个公共列，而内连接则相反，将其保留。也可以理解成如果左表跟右表有重复部分，则将左表拼接到右表上去。对比公共列，将公共列重复的数据行保留，将两个表拼接起来。右外连接则和左外连接相反，选择保留右表所有数据。公共列中的重复部分用绿色标注。
第四章 SQL查询 之连接{自然连接，内连接，外连接（左外连接，右外连接，完全连接）}
qq_39962271的博客
04-20 4200
4.4 连接 自然连接 自然连接实际指定了搜寻条件。这里包括两部分的内容：首先，自然连接列必须同名，另外，所有同名列都将作为搜索条件。 自然连接所使用的关键字为natural join 。其连接原则为，两个数据源的共有列，并且具有相同列值。 结合与笛卡尔积的区别进行理解 自然连接与笛卡尔积的区别在于，笛卡尔积无论何时，都将获得两个数据表中所有记录的两两结合。而当两个数据表中存在同名列时，Oracle将同名列作为搜寻条件。相当于强制添加条件——where t1.column = t2.column
mysql 内连接、自然连接、外连接的区别
ratel的博客
12-02 4万+
内连接、自然连接、外连接的区别
自然连接（NATURAL JOIN）
zhuzi51的专栏
09-04 1091
https://blog.csdn.net/holly2008/article/details/25501343
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值