full join 和full outer join_理解 SQL 中的各类 JOIN

最新推荐文章于 2020-11-30 13:58:59 发布
最新推荐文章于 2020-11-30 13:58:59 发布
阅读量808 收藏 1
点赞数
文章标签： full join 和full outer join full outer join 与full join的区别 inner join 和join的区别 inner join 重复数据 join inner join的区别 left join on 多条件

4c35d2ba334e9a7d63b28f3b33eb7dec.png

最近比较头疼 JOIN 的用法和结果，写这篇文章是为了总结归纳阅读的资料。在本文中，将以 MySQL 语法为例。

例子中使用的表

我们有两张表，分别为 product 与 price，结构如下所示 （price 表中的 id 是外键）:

mysql> desc product;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | smallint(6) | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc price;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | smallint(6)  | YES  |     | NULL    |       |
| pprice | decimal(5,2) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

我们在表里插入了一些数据：

mysql> select * from product;
+----+--------+
| id | name   |
+----+--------+
|  0 | milk   |
|  1 | coffee |
|  2 | water  |
+----+--------+
3 rows in set (0.00 sec)

mysql> select * from price;
+------+--------+
| id   | pprice |
+------+--------+
|    0 |  10.90 |
|    1 |  28.00 |
|    2 |   1.99 |
+------+--------+
3 rows in set (0.00 sec)

MySQL 中的 JOIN 类型

下面是 MySQL 8.0 中的 JOIN 语法[1]：

joined_table: {
    table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
}

根据语法，我们可以有多少种组合呢（按 JOIN 关键词算）？

=> 答案是 4+4+6+1=15（算错了？请留言）

但是这些 JOIN 都大同小异，主要有以下几类：

  • NATURAL JOIN
  • LEFT(OUTER)/RIGHT(OUTER) JOIN
  • JOIN/INNER/CORSS JOIN

我们下来按顺序讨论，从 natural join 开始 。

NATURAL JOIN

先来看两个例子：

mysql> select * from product natural join  price;
+----+--------+--------+
| id | name   | pprice |
+----+--------+--------+
|  0 | milk   |  10.90 |
|  1 | coffee |  28.00 |
|  2 | water  |   1.99 |
+----+--------+--------+
3 rows in set (0.00 sec)

-- 给 produc 添加一行，但是在 price 没有添加对应的行
mysql> insert into product value(3, 'juice');
Query OK, 1 row affected (0.01 sec)

mysql> select * from product natural join price;
+----+--------+--------+
| id | name   | pprice |
+----+--------+--------+
|  0 | milk   |  10.90 |
|  1 | coffee |  28.00 |
|  2 | water  |   1.99 |
+----+--------+--------+
3 rows in set (0.00 sec)

在这两条查询的结果中，有以下特征：

  • 只保留一列 id
  • price 不包含的 id 没有出现

这些特征就是 NATURAL JOIN 本身的含义：

  • 名称相同的列为 JOIN 使用的列
  • 某行所对应该该列的值相等为 JOIN 的条件

NATURAL JOIN 可以理解为以下的集合操作[2]：

  • 结果中的列：进行 JOIN 两张表列的并集
  • 结果中的行：两张表中满足值相等的行的并集

LEFT/RIGHT JOIN

在 MySQL 中，LEFT JOIN = LEFT OUTER JOIN；RIGHT JOIN = RIGHT OUTER JOIN。

首先要说明的是，这两个 JOIN 不是对称的（结果不相同）：

mysql> select * from product right join price on product.id = price.id;
+------+--------+------+--------+
| id   | name   | id   | pprice |
+------+--------+------+--------+
|    0 | milk   |    0 |  10.90 |
|    1 | coffee |    1 |  28.00 |
|    2 | water  |    2 |   1.99 |
+------+--------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from product left join price on product.id = price.id;
+----+--------+------+--------+
| id | name   | id   | pprice |
+----+--------+------+--------+
|  0 | milk   |    0 |  10.90 |
|  1 | coffee |    1 |  28.00 |
|  2 | water  |    2 |   1.99 |
|  3 | juice  | NULL |   NULL |
+----+--------+------+--------+
4 rows in set (0.00 sec)

对于 LEFT JOIN（RIGHT JOIN 类似），结果保留左表 product 中 id 的值 {0,1,2,3,}，因为右表 price 没有 id=3 的行，所以当 id=3 时，结果中右边的值为 NULL。

特征如下：

  • 结果中的列：保留左表和右表中的所有列。
  • 结果中的行：保留左表所有行，根据给定的 JOIN 条件，取两表中满足该条件行的并集；如果右表行不满足条件，值为 NULL

练习题：以下两条查询的结果是什么？

select * from product natural left join price;
select * from product natural right join price;

JOIN/INNER/CROSS JOIN

在 MySQL 中，这里的三个关键词是等价的[1]：

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent.

我们也可以从实际查询中看出等价性；

-- 用 JOIN
mysql> select * from product join price on product.id = price.id;
+----+--------+------+--------+
| id | name   | id   | pprice |
+----+--------+------+--------+
|  0 | milk   |    0 |  10.90 |
|  1 | coffee |    1 |  28.00 |
|  2 | water  |    2 |   1.99 |
+----+--------+------+--------+
3 rows in set (0.00 sec)

-- 用 INNER JOIN
mysql> select * from product inner join price on product.id = price.id;
+----+--------+------+--------+
| id | name   | id   | pprice |
+----+--------+------+--------+
|  0 | milk   |    0 |  10.90 |
|  1 | coffee |    1 |  28.00 |
|  2 | water  |    2 |   1.99 |
+----+--------+------+--------+
3 rows in set (0.00 sec)

-- 用 CROSS JOIN
mysql> select * from product cross join price on product.id = price.id;
+----+--------+------+--------+
| id | name   | id   | pprice |
+----+--------+------+--------+
|  0 | milk   |    0 |  10.90 |
|  1 | coffee |    1 |  28.00 |
|  2 | water  |    2 |   1.99 |
+----+--------+------+--------+
3 rows in set (0.00 sec)

不加 JOIN 条件的运行结果，以 JOIN 关键词为例：

mysql> select * from product join price;
+----+--------+------+--------+
| id | name   | id   | pprice |
+----+--------+------+--------+
|  0 | milk   |    2 |   1.99 |
|  0 | milk   |    1 |  28.00 |
|  0 | milk   |    0 |  10.90 |
|  1 | coffee |    2 |   1.99 |
|  1 | coffee |    1 |  28.00 |
|  1 | coffee |    0 |  10.90 |
|  2 | water  |    2 |   1.99 |
|  2 | water  |    1 |  28.00 |
|  2 | water  |    0 |  10.90 |
|  3 | juice  |    2 |   1.99 |
|  3 | juice  |    1 |  28.00 |
|  3 | juice  |    0 |  10.90 |
+----+--------+------+--------+
12 rows in set (0.01 sec)

此处可以理解为 JOIN 条件默认是 True。

特征：

  • 结果的列：左表和右表的所有列
  • 结果的行：结果中的一行是左表的一行和右表一行的并集

特殊情况

当我们不给定关键词，使用逗号分隔时，MySQL 默认会转换为这里的三个 JOIN：

This is a conservative extension if each comma in a list of table_reference items is considered as equivalent to an inner join.

练习题：以下查询的结果是什么？

select * from product inner join price on 1;
select * from product inner join price on 0;
select * from product natural inner join price;

总结

我们在本文中以 MySQL 为例，介绍了不同的 JOIN ，同时使用集合概念表述了不同 JOIN 结果的区别。个人能力有限，可能会有错误，欢迎指出和探讨。（封面图片[3]）

更多阅读资料

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?​stackoverflow.com
3b5a4c0abf740581bbc4b4ffc02df363.png
What is the difference between "INNER JOIN" and "OUTER JOIN"?​stackoverflow.com
3b5a4c0abf740581bbc4b4ffc02df363.png

参考

  1. ^abMySQl JOIN Clause https://dev.mysql.com/doc/refman/8.0/en/join.html
  2. ^SQL and Relational Theory: How to Write Accurate SQL Code. P.185 https://www.amazon.com/SQL-Relational-Theory-Write-Accurate/dp/1491941170
  3. ^封面图片 https://data-flair.training/blogs/sql-join/
weixin_39942572
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
hive 子查询特别分析
秋韩
07-30 2万+
Hive只支持在FROM子句中使用子查询，子查询必须有名字，并且列必须唯一：SELECT ... FROM(subquery) name ... 确认下是否一定要求列必须唯一？
full join 和full outer join_跟飞哥学编程：SQL入门-：JOIN和UNION
weixin_39796116的博客
11-28 651
因为要遵守三大范式的要求，实际的开发中，数据库中会存在大量的零碎的“小”表。对于这些小表，我们常常使用JOIN将多个表 水平 联接起来，以获取单表无法提供的信息。比如我们现在有两张表Student和City：为了简化，我们只用了很少的列执行如下SQL语句：SELECT 建议：总是使用表别名。如果不使用别名，相同的列名就需要使用表的全名前缀加以区分得到的结果就是：从FromCity和Id列，可以清楚...
图解SQL各种join和union
qianshanding0708的博客
03-26 746
Join语法有很多inner和outer，还有left、right，有的时候会真有点分不清楚。假设我们有两张表。Table A 是左边的表。Table B 是右边的表。其...
hive left outer join的问题
yukangkk的技术博客
07-26 7610
最近BA用户反馈有两句看似很像的语句返回的结果数不一样，比较奇怪，怀疑是不是Hive的Bug Query 1 返回结果数6071 select count(distinct reviewid) as dis_reviewcnt from (select a.reviewid from bi.dpods_dp_reviewreport a left outer join bi.dpods_dp
full join 和full outer join_多表关联：公式展开、join、过滤条件的顺序
weixin_39942451的博客
11-28 754
这是在实现多表关联时想到的。我们现在这套体系，实现多表关联比较复杂。如果Superset能官方支持多表关联，不知道会是什么样的方案，复杂度如何。在公式这个层面，没有关联条件，只有两个列、或者多个列，相互之间该如何计算（+-*/）。->由此可知，在展开公式之前，结果集要先进行join，以保证列计算时，每一行被正确的对齐了。->查找基础KPI、分组、查询的操作不需要改变。在获取df之后，m...
SQL联合查询inner join、outer join和cross join的区别详解
12-16
这三者的区别很多人都应该不是很清楚，包括我自己，下面我们一起来看看，如果你使用join连表，缺陷的情况下是inner join，另外，开发中使用的left join和right join属于outer join，而outer join还包括full join....
inner join、 left join 、right join、 outer join之间的区别
01-13
`FULL OUTER JOIN`也称为`FULL JOIN`，它会返回两个表中的所有记录。如果某个表中没有匹配的记录，则另一侧的结果将是`NULL`。 **示例**： 继续使用之前的表`A`和表`B`，执行`FULL OUTER JOIN`查询： ```sql ...
SQL FULL OUTER JOIN
冷月醉雪的博客
12-20 1426
    FULL OUTER JOIN关键字只要左表(table1)和右表(table2)其中一个表中存在匹配，则返回行。    FULL OUTER JOIN关键字结合了LEFT JOIN和RIGHT JOIN的结果。   SQL FULL OUTER JOIN 语法   SELECT column_name(s) FROM table1 FULL OUTER JOIN table2...
SQL中的left outer join,inner join,right outer join用法详解
12-17
* FULL OUTER JOIN：LEFT OUTER 和 RIGHT OUTER 中所有行的超集。 内连接（Inner Join） 内连接是最常见的一种连接，它也被称为普通连接，而 E.FCodd 最早称之为自然连接。例如： SELECT * FROM t_institution i ...
带你了解数据库中JOIN的用法
weixin_33859231的博客
04-07 561
前言 欢迎关注公众号：Coder编程 获取最新原创技术文章和相关免费学习资料，随时随地学习技术知识！ 本章主要介绍数据库中Join的的用法，也是我们在使用数据库时非常基础的一个知识点。本次会介绍数据库中的inner join、left join、right join 的用法以及它们之间的区别。 文章如有错误还请大家及时指出~ 以下都是采用mysql数据库 Join 相信大家在学习数据库的使...
left join 和 left outer join的区别
weixin_33827590的博客
11-02 841
left join 是left outer join的简写，left join默认是outer属性的。 Inner Join Inner Join 逻辑运算符返回满足第一个（顶端）输入与第二个（底端）输入联接的每一行。这个和用select查询多表是一样的效果，所以很少用到； outer join则会返回每个满足第一个（顶端）输入与第二个（底端）输入的联接的行。它还返回任何在第二个输入中没...
MySQL 索引优化 btree hash rtree
weixin_30304375的博客
07-07 106
一、MySQL索引类型mysql里目前只支持4种索引分别是：full-text，b-tree，hash，r-treeb-tree索引应该是mysql里最广泛的索引的了，除了archive基本所有的存储引擎都支持它.1. full-text索引full-text在mysql里仅有myisam支持它，而且支持full-text的字段只有char、varchar、text数据类型。full-t...
full outer join 与full join的区别_Apache Flink 漫谈系列 双流JOIN
weixin_39827315的博客
11-30 6013
什么是JOINJOIN的本质是分别从N(N>=1)张表中获取不同的字段，进而得到最完整的记录行。比如我们有一个查询需求：在学生表(学号，姓名，性别)，课程表(课程号，课程名，学分)和成绩表(学号，课程号，分数)中查询所有学生的姓名，课程名和考试分数。如下：为啥需要JOINJOIN的本质是数据拼接，那么如果我们将所有数据列存储在一张大表中，是不是就不需要JOIN了呢？如果真的能将所需...
一张图轻松搞懂Hive中的join（内连接、左外连接、右外连接、满外连接）
weixin_43230682的博客
08-19 8798
join语句 1 等值 join Hive支持通常的SQL JOIN语句，但是只支持等值连接，==不支持非等值连接==。 案例实操 select * from stu left join score on stu.id = score.s_id; 根据学生和成绩表，查询学生姓名对应的成绩 2 表的别名 好处 使用别名可以简化查询。 使用表名前缀可以提高执行效率。 案例实操：合并老师与课程表 -- hive当中创建course表并加载数据 create table ..
full outer join 与full join的区别_Join有哪几种方式？内外连接有什么区别？
weixin_39533742的博客
11-29 7135
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 ...
hive 子查询、union 、left join
u010299467的专栏
02-18 979
建表语句： create table  tb_in_base (    id  bigint,    devid bigint,     devname string  ) partitioned by (job_time bigint) row format delimited fields terminated by ','; create table  tb_i
full join 和full outer join_带你了解数据库中JOIN的用法
热门推荐
weixin_39559895的博客
11-29 1万+
前言欢迎关注公众号：Coder编程 获取最新原创技术文章和相关免费学习资料，随时随地学习技术知识！本章主要介绍数据库中Join的的用法，也是我们在使用数据库时非常基础的一个知识点。本次会介绍数据库中的 inner join、 left join、 right join 的用法以及它们之间的区别。 文章如有错误还请大家及时指出~以下都是采用mysql数据库Join相信大家在学习数据库的使用时，都有使...
Hive的left join、left outer join和left semi join三者的区别
蚂蚁学Python
06-30 2185
Hive的Join的文档说明地址： https://cwiki.apache.org/confluence/display/Hive/LanguageManual%2BJoins 以下为两个测试数据表建表语句： MySQL use test; DROP TABLE IF EXISTS table1; create table t...
pandas入门: 数据合并之--join
jp_666的博客
02-23 904
pandas join知识点总结。 创建两个dataframe df1 = pd.DataFrame({'A': [3, 4, 8, 9], 'B': [1.2, 2.4, 4.5, 7.3], 'C': ["aa", "bb", "cc", "dd"]}) df2 = pd.DataFrame({'D': [1, 2]}) 结果如下： A B C 0 3 1.2 ...
full outer join 和 full join
最新发布
09-17
Full outer join 和 Full join 是SQL中常用的连接操作。它们的作用是将两个表中的数据按照指定的连接条件进行合并，并包括两个表中所有的行。 Full join 是将两个表中满足连接条件的行进行合并，并且包括两个表中不...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值