数据库 | 辨析 cross join、inner join和outer join

易水卷长空

已于 2022-10-31 10:13:24 修改

阅读量8.6k

点赞数 18

文章标签：数据库 mysql sql

于 2022-03-20 21:07:29 首次发布

本文链接：https://blog.csdn.net/a26013/article/details/123615320

版权

一、笛卡尔积(交叉连接) CROSS JOIN

以下面两个表chinese，english为例

其中chinese表共5条记录，english表共4条记录

一、笛卡尔积(交叉连接) CROSS JOIN

交叉连接不带WHERE子句，它返回被连接的两个表所有数据行的笛卡尔积，返回结果集合中的数据行数等于第一个表中的数据行数乘以第二个表中的数据行数

SELECT *
FROM chinese c,english e;

#或者

SELECT *
FROM chinese c CROSS JOIN english e;

可以看到，表chinese和表english的笛卡尔积共5*4=20条记录

[ 三种形式的笛卡尔积 ]

下面三条语句是等价的：

#语句1
SELECT *
FROM chinese c,english e;

#语句2
SELECT *
FROM chinese c CROSS JOIN english e;

#语句3
SELECT *
FROM chinese c INNER JOIN english e;

二、内连接 INNER JOIN

主要指带ON的内连接，内连接按照ON条件合并两个表，返回满足条件的记录

SELECT *
FROM chinese c INNER JOIN english e 
ON c.ID = e.ID;

等值连接 WHERE

使用 where 和 = 将表连接起来的查询，其查询结果中列出被连接表中的所有列，包括其中重复的列

SELECT *
FROM chinese c,english e
WHERE c.ID = e.ID;

[ 辨析 | 等值连接和内连接 ]

下面三条语句的结果相同，其中INNER JOIN中的INNRE可以省略：
#语句1
SELECT *
FROM chinese c,english e
WHERE c.ID = e.ID;

#语句2
SELECT *
FROM chinese c INNER JOIN english e 
ON c.ID = e.ID;

#语句3
SELECT *
FROM chinese c JOIN english e 
ON c.ID = e.ID;
从逻辑上来说，等值连接与内连接没什么不同；但实际上等值连接和内连接的执行计划并不相同，当参与连接的两个表比较大时，使用内连接(inner join)的效率更高

注意，有时优化器会自动把where转换成inner join，所以这时候它们的运行时间可能相同

自然连接 NATURAL JOIN

自然连接是一种特殊的内连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉

SELECT *
FROM chinese c NATURAL JOIN english e;

[ 辨析 | 自然连接与内连接 ]

自然连接与内连接的区别在于，自然连接要求两个表具有相同的属性（比如表chinese和表english中的ID），而内连接则没有此要求；并且自然连接会去除重复属性（只保留一列），而内连接则不会

三、外连接 OUTER JOIN

外连接包括左外连接、右外连接和全外连接

[ 辨析 | 内连接和外连接 ]

内连接（等值连接、自然连接）只会筛选出满足ON（WHERE）条件的结果；

而外连接不仅包含内连接的结果，还会保留部分或全部不满足ON（WHERE）条件的记录，比如left join除了两表中满足ON的记录外会多保留左表中不满足条件的数据，right join除了两表中满足ON的记录外会多保留右表中不满足条件的数据，full join除了两表中满足ON的记录外会分别保留左表和右表中不满足条件的数据

左外连接 LEFT JOIN

左外连接会保留左表的全部记录，相当于在左表的基础上加上右表中满足ON条件的数据，剩余的空位以NULL填充

SELECT *
FROM chinese c LEFT JOIN english e
ON c.ID = e.ID;

右外连接 RIGHT JOIN

右外连接会保留右表的全部记录，相当于在右表的基础上加上左表中满足ON条件的数据，剩余的空位以NULL填充

SELECT *
FROM chinese c RIGHT JOIN english e
ON c.ID = e.ID;

全外连接 FULL JOIN

全外连接是左外连接和右外连接结果的并集。也可以把全外连接理解为在内连接基础上，把左表和右表中未被匹配的记录分别添加进来

SELECT *
FROM chinese c FULL JOIN english e
ON c.ID = e.ID

注意，MySQL是没有FULL JOIN的，但是可以用UNION左外连右外连实现全外连接

(SELECT *
FROM chinese c LEFT JOIN english e
ON c.ID = e.ID)
UNION
(SELECT *
FROM chinese c RIGHT JOIN english e
ON c.ID = e.ID);