full join 和full outer join_跟飞哥学编程：SQL入门-：JOIN和UNION

最新推荐文章于 2024-08-13 23:06:11 发布
最新推荐文章于 2024-08-13 23:06:11 发布
阅读量803 收藏
点赞数
文章标签： full join 和full outer join full join和full outer join full outer join 与full join的区别 hive full outer join inner join on 加条件和where加条件 inner join 和join的区别

fce34d03cb190083abd2250035134536.png

因为要遵守三大范式的要求，实际的开发中，数据库中会存在大量的零碎的“小”表。对于这些小表，我们常常使用

JOIN

将多个表 水平 联接起来，以获取单表无法提供的信息。比如我们现在有两张表Student和City：

3b0c416089a36af0f82268ea8569aca9.png
为了简化，我们只用了很少的列

执行如下SQL语句：

SELECT

建议：总是使用表别名。如果不使用别名，相同的列名就需要使用表的全名前缀加以区分

得到的结果就是：

b6e46240fdabac53f35c2841541a1829.png

从FromCity和Id列，可以清楚的看到两表连接的方式或规则。同一行内，FromCityId的值总是等于（右边City的）Id的值。这是由SQL中：ON s.FromCityId = c.Id 决定的。意思就是：把Student的FromCityId和City的Id相等的行连接起来。

查看执行计划，可以看到SQL Server先进行了两张表的扫描，然后再进行了JOIN操作：

a013d1d725e67240db430d95bbe9b77b.png

我们可以在SELECT子句中指定列，进一步整理显示结果，比如：

SELECT

结果就会变成：

1daadb89b5dd0e1d8eaddf507fabac36.png

这就是SQL中默认的连接方式：内连接，关键字INNRE JOIN，其中INNER可以省略。除此以外，还有：

外连接，关键字：OUTER，也可以省略。因为它又还细分为：

  • 左外连接（可简称为左连接），关键字：LEFT：左边所有行被保留
  • 右外连接（可简称为右连接），关键字：RIGHT：右边所有行被保留
  • 全外连接，关键字：FULL：左右两边都被保留

所谓“左”和“右”，是指在书写SQL语句时，在JOIN左边或右边。

为了展示左连接，我们修改一下上表数据，把“陈晓斌”的FromCityId改成NULL值。使用左连接：

SELECT

因为要保证左表全部显示，“陈晓斌”就一定会被保留。但保留之后，City里没值怎么办呢？用NULL填充：

5c17fe95e4be275bdaad0b63581f1c62.png

如果是内连接的话，因为NULL值不会和City.Id里的1,2,3,4相等，所以结果就会没有“陈晓斌”这一行：

5e792e6a65a724a5bcd7ba273c72a0f9.png
注意：没有“陈晓斌”这一行了

同理，为了展示右连接，我们可以在City表中添加一行：5/四川/成都，这个城市在Student表中没有任何对应。使用右连接：

SELECT

因为要保证由表全部显示，“5/四川/成都”就一定会被保留（但FromCityId=NULL的“陈晓斌”也不会保留）。保留之后，Student里没值还是用NULL填充：

1227a1a0e3d684d768ac2716df986236.png

如果是内连接的话，Student表中没有任何一行FromCityId=5，所以结果就不会有“5/四川/成都”这一行：

2901c26a4ad27eca314588c0d40c9dec.png
注意：没有“5/四川/成都”这一行了

想一想：Student LEFT JOIN City是不是等于 City LEFT JOIN Student？

那么最后，全连接就非常好理解了：

SELECT

结果为：

e38c9f3c6f39e0356ba4bdd4a9e603b8.png

最后，我们来了解一下交叉连接：

SELECT

这中连接会不进行任何过滤，依次：

  1. 把左边的表的第一行和右边全表连接
  2. 把左边的表的第二行和右边全表连接
  3. 把左边的表的第三行和右边全表连接
  4. ……
  5. 把左边的表的最后一行和右边全表连接

这种运算方式又被称之为笛卡尔乘积。其计算结果的行数非常大（mxn），同学们了解即可，一般不需要使用。

实际上，在SELECT查询语句中，使用JOIN连接的表，可以像单表一样使用WHERE/ORDER/GROUP等操作。

但是，注意不要混用WHERE和ON。在INNER JOIN的时候，可能没有什么问题，比如这两句SQL是等效的：

SELECT

但是，在外连接的时候，使用WHERE子句结果仍然正常：

SELECT

但使用AND的结果就变得“诡异”起来：

SELECT

db40d598364ad383d08cf14bb09dcacb.png

整张表，包括姓名不是“幸龙泰”的同学都被查询出来了，而且我们还注意到除了“幸龙泰”，关联的City表中的值全部为NULL！这是怎么回事？

从逻辑上来讲，在ON中定义的条件是用于“连接”的，不是用于对查询结果进行过滤的。所以，根据左外连接的规则：

  1. 左边的所有数据都必须保留，无论连接条件是否满足；
  2. 只是当连接条件不能满足的时候，右表字段值为NULL.

从而形成了上述查询结果。

另外，对比两条语句的执行计划：

9a6a40bd1c461361f2f049554fb88548.png

看起来都差不多。但是，把鼠标停在Student的Clustered Index Scan上，你就会发现区别：

fe7cae31c600b28176788a6509b3cfcc.png
使用的是AND

8dfa81e4f7bcb95d569e807d97361975.png
使用的WHERE子句

可以看出，在Student上设置的WHERE子句其实在JOIN之前都执行了！(^o^)/

为了代码的可读性，我们推荐“两个总是”：

  1. 总是使用ON设立联接条件，
  2. 总是使用WHERE对联接之后的结果进行筛选。

联表删/改

直接上语法，背下来就行。记忆规律就是在单表的删改语句中加了一行FROM...JOIN...ON。

比如，删除来自重庆的同学：

DELETE

将来自重庆的同学的成绩加5分：

UPDATE

就像外键不仅仅用于多张表的连接一样，JOIN也不仅仅用于连接其他表，我们还可以使用JOIN的：

自联接

让表自己和自己联接。

和多表连接唯一的不同：连接时必须使用表别名。

比如有这么一个需求：

找到姓名重复的学生。

除了之前的子查询，我们还可以使用自连接：

SELECT

UNION

可以将多个查询结构纵向联接，只要这些查询结果列的数量、顺序相同，且每一列的数据类型能够兼容（可隐式转换）。

比如我们可以把学生的综合成绩和各科成绩用一个查询结果显示出来，并按学生姓名有序显示：

SELECT

54362ad8dfff4c3c585fe921c4dfab34.png

注意以下几个语法点：

  1. UNION后可以接ALL，也可以不接。不接ALL（默认），会清除重复行（所有列/字段都重复才算重复）；接ALL，不会清除重复行
  2. 列名以第一个查询子句为准，所以也只能使用第一个SELECT语句的列名

每日单词：

c2b236ac35a7db9086d0c92506e98f68.png

作业：

  1. 联表查出求助的标题和作者用户名
  2. 查找并删除从未发布过求助的用户
  3. 用一句SELECT显示出用户和他的邀请人用户名
  4. 17bang的关键字有“一级”“二级”和其他“普通（三）级”的区别：
    1. 请在表Keyword中添加一个字段，记录这种关系
    2. 然后用一个SELECT语句查出所有普通关键字的上一级、以及上上一级的关键字名称，比如：

2a96005543d756febd12a8fddaa47885.png
  1. 17bang中除了求助（Problem），还有意见建议（Suggest）和文章（Article），他们都包含Title、Content、PublishTime和Auhthor四个字段，但是：
    1. 建议和文章没有悬赏（Reward）
    2. 建议多一个类型：Kind NVARCHAR(20)）
    3. 文章多一个分类：Category INT）
  2. 请按上述描述建表。然后，用一个SQL语句显示某用户发表的求助、建议和文章的Title、Content，并按PublishTime降序排列

感谢童鞋们的阅读！^_^

我就是：黑律师/包工头/创业狗/老码农……现在还是教书匠的大飞哥。

再次重申这个系列的目标是：

1）通俗易懂。2）实战为主。3）面向就业。

系列内容的完善需要你的反馈！

欢迎点赞和评论，以及加入我们的QQ交流群：326801052。

weixin_39796116
关注
SQL联合查询inner join、outer join和cross join的区别详解
12-16
这三者的区别很多人都应该不是很清楚，包括我自己，下面我们一起来看看，如果你使用join连表，缺陷的情况下是inner join，另外，开发中使用的left join和right join属于outer join，而outer join还包括full join....
full join 和full outer join_从零开始学习大数据系列(六十四)Hive的关联查询JOIN
weixin_40004960的博客
11-28 1561
[本文2400字左右，预计阅读需要15-20分钟]在之前的文章《从零开始学习大数据系列(五十九)Hive的SELECT查询》中已经介绍了下Hive中的Select查询的基本操作。那么当我们查询的数据存放在不同的表中，关联查询就派上用场了。所谓关联查询，一般就是通过使用相同的字段值来组合多张表的字段类似于SQL中的JOIN，同时查询多张表的记录，下面我们来了解下Hive中的关联查询JOIN...
hive之full outer join（全连接）使用方法
热门推荐
ImezZ
04-07 3万+
目录 介绍 语法 例子 创建顾客表：customers 创建订单表：orders full outer join语句 left join + union + right join语句 介绍 full outer join结合了 LEFT JOIN 和 RIGHT JOIN 的结果，并使用NULL值作为两侧缺失匹配结果。 语法 SELECT table1.column_name(s),table2.column_name(s) FROM table1 ..
sql中join使用和full join
最新发布
weixin_44823875的博客
08-13 507
【注意】：Oracle数据库支持full join，mysql是不支持full join的，但仍然可以同过左外连接+ union+右外连接实现。我们可以使用union来达到目的。两表关联，把左表的列和右表的列通过笛卡尔积的形式表达出来。两表关联，左表全部保留，右表关联不上用null表示。右表全部保留，左表关联不上的用null表示。两表关联，保留两表中交集的记录。两表关联，查询左表独有的数据。两表关联，查询右表独有的数据。两表关联，查询它们的所有记录。两表关联，取并集然后去交集。
内连接 外连接 交叉连接总结
edifiergl的专栏
04-08 1977
本篇文章总结了一下SQL中的五种连接-内连接、左外连接、右外连接、全连接、交叉连接 一、内连接 (full join/full outer join) 内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种： 1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
full join 和full outer join_带你了解数据库中JOIN的用法
weixin_39559895的博客
11-29 1万+
前言欢迎关注公众号：Coder编程 获取最新原创技术文章和相关免费学习资料，随时随地学习技术知识！本章主要介绍数据库中Join的的用法，也是我们在使用数据库时非常基础的一个知识点。本次会介绍数据库中的 inner join、 left join、 right join 的用法以及它们之间的区别。 文章如有错误还请大家及时指出~以下都是采用mysql数据库Join相信大家在学习数据库的使用时，都有使...
hive之full outer join（全连接）使用
weixin_42326851的博客
04-01 2165
hive之full outer join（全连接）使用
SQL外部联合:right outer join、left outer join、full outer join
一起加油吧~
02-17 5617
SQL将外部联合分为了右外部联合(right outer join)、左外部联合(left outer join)、完全外部联合(full outer join)3个类型。 左外部联合：LEFT OUTER JOIN 基本语法：SELECT column_list ...
SQL中JOIN和UNION区别、用法及示例介绍
12-15
在SQL中，JOIN和UNION是两种非常重要的操作，它们用于处理多个表的数据结合，但它们的作用和原理有所不同。 JOIN操作主要用于将两个或更多表的数据根据特定条件进行合并。JOIN有四种基本类型： 1. INNER JOIN（内...
非常详细的SQL--JOIN之完全用法
09-11
Microsoft SQL Server 2000 支持 SQL-92 关键字，包括 LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN 等。这些关键字可以在 FROM 子句中使用，以指定外联接的类型。 旧式 Transact-SQL 外联接语法 SQL ...
full outer join 与full join的区别_基础小白的SQL的JOIN语法解析
weixin_39564527的博客
11-30 1万+
总的来说，四种JOIN的使用/区别可以描述为:left join 会从左表(shop)那里返回所有的记录，即使在右表(sale_detail)中没有匹配的行。right outer join 右连接，返回右表中的所有记录，即使在左表中没有记录与它匹配full outer join 全连接，返回左右表中的所有记录在表中存在至少一个匹配时，inner join 返回行。 关键字inner可省略。Joi...
带你了解数据库中JOIN的用法
weixin_33859231的博客
04-07 581
前言 欢迎关注公众号：Coder编程 获取最新原创技术文章和相关免费学习资料，随时随地学习技术知识！ 本章主要介绍数据库中Join的的用法，也是我们在使用数据库时非常基础的一个知识点。本次会介绍数据库中的inner join、left join、right join 的用法以及它们之间的区别。 文章如有错误还请大家及时指出~ 以下都是采用mysql数据库 Join 相信大家在学习数据库的使...
SQL全外连接（Full Outer Join），与左连接、右连接
Zhan_chi_的博客
07-18 2025
返回左表和右表中的所有记录，无论是否存在匹配的记录。如果左表和右表中的记录可以匹配，则返回匹配的结果；如果左表或右表中的记录没有匹配的记录，则对应的结果列将被填充为NULL值。与左连接相反，返回右表中的所有记录，以及与左表中符合连接条件的记录匹配的结果。如果左表中没有匹配的记录，对应的结果列将被填充为NULL值。返回左表中的所有记录，以及与右表中符合连接条件的记录匹配的结果。如果右表中没有匹配的记录，对应的结果列将被填充为NULL值。
SQL FULL OUTER JOIN 关键字（完整外部连接）｜｜SQL自连接 Self JOIN
爱编程的鱼的博客
11-06 695
FULL OUTER JOIN关键字返回左表（Customers）中的所有行，以及右表（Orders）中的所有行。如果 "Customers"中的行中没有"Orders"中的匹配项，或者"Orders"中的行中没有 "Customers"中的匹配项，那么这些行也会列出。当左（表1）或右（表2）表记录匹配时，FULL OUTER JOIN关键字将返回所有记录。在本教程中，我们将使用著名的Northwind示例数据库。在本教程中，我们将使用着名的Northwind示例数据库。
full outer join 与full join的区别_Join有哪几种方式？内外连接有什么区别？
weixin_39533742的博客
11-29 7178
join有5中连接方式：内连接(inner join…on…)全外连接（full join…on…)左连接（left join…on…)右连接（right join…on…)交叉连接(cross join …on…)内连接（inner join… on…)select通过id将table1和table2连接起来产生一个新表，新表是由这个表的公开部分组成；如下图：内连接左外连接（left join ...
MySQL 的 FULL OUTER JOIN如何实现
m0_50736744的博客
08-09 1034
在 MySQL 中，不支持 FULL OUTER JOIN 的语法，但我们可以通过其他方式来实现 FULL OUTER JOIN的效果。FULL OUTER JOIN 是一种联结方式，它返回两个表的所有匹配行和非匹配行。此篇博客介绍一种使用 UNION 和 LEFT JOIN、RIGHT JOIN 来实现 FULL OUTER JOIN 的方法。
四种连接类型：inner（内连接），left[outer]（左外连接），right[outer]（右外连接），full[outer]（完全外连接）
03-01 1万+
在from子句中表示连接操作有四种：inner（内连接），left[outer]（左外连接），right[outer]（右外连接），full[outer]（完全外连接）。 一、理论讲解 1、内连接join 结果集中只保留了符合连接条件的元组，而排除了两个表中没有对应的或匹配的元组的操作称为内连接。 2、左外连接left 左(外)连接，左表(...
Oracle 数据库中 FULL OUTER JOIN 的作用 ？
林夕
03-24 1247
FULL OUTER JOIN 返回左表（Table A）和右表（Table B）的所有行，并且如果左表或右表中没有匹配的行，则使用 NULL 值填充缺失的部分。FULL OUTER JOIN 关联之后的结果：（a表多了一条：2021 年的数据，b表没有） ，（b表多了一条：2028年的数据 ，a表没有）。当需要同时显示两个表中所有记录时，FULL OUTER JOIN 就非常有用。
full outer join 与full join的区别_Apache Flink 漫谈系列 双流JOIN
weixin_39827315的博客
11-30 6101
什么是JOINJOIN的本质是分别从N(N>=1)张表中获取不同的字段，进而得到最完整的记录行。比如我们有一个查询需求：在学生表(学号，姓名，性别)，课程表(课程号，课程名，学分)和成绩表(学号，课程号，分数)中查询所有学生的姓名，课程名和考试分数。如下：为啥需要JOINJOIN的本质是数据拼接，那么如果我们将所有数据列存储在一张大表中，是不是就不需要JOIN了呢？如果真的能将所需...
SQL JOIN详解：LEFT, RIGHT, INNER, SELF与FULL JOIN的区别与用法
5. FULL JOIN: 全连接（FULL JOIN）也称为FULL OUTER JOIN，返回两个表中所有可能的记录，如果在其中一个表中没有匹配，则相应字段的值为NULL。`SELECT a.cust_id, b.tot_amt FROM customer a FULL JOIN sales b ON ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值