有关JOIN的那些事儿 -- SQL

最新推荐文章于 2022-03-17 14:34:02 发布
最新推荐文章于 2022-03-17 14:34:02 发布
阅读量214 收藏
点赞数
分类专栏： 笔记 文章标签： sql mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44708233/article/details/106037329
版权

有关JOIN的那些事儿

SQL

目录

前言

虽然跟着书本学到了一些基础的sql语法，可是一旦要运用到实践当中就会发现和自己想的完全不一样。
就在进行多表查询时，由于每个表可能具有的某些特性或者查询时会有一些要求，这使得简单的连接查询不能派上用场或者效率太低
因此，学习一下表的连接是很有必要的

概述

www.runoob.com
以上包括：
LEFT JOIN
RIGHT JOIN
INNER JOIN
OUTER JOIN
FULL OUTER JOIN
还有一种
NATURAL JOIN

详解

1 . LEFT JOIN

  • ON 子句与 WHERE 子句的不同
  • 一种更好地理解带有 WHERE … IS NULL 子句的复杂匹配条件的简单方法
ON 与 WHERE的使用（重点）

（1）ON
ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。
如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据
（2）WHERE
在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。
（3）区别
（阶段一）它们处理数据时有先后之分，运算系统先会对on的条件进行匹配，（阶段二）然后才用where的条件对匹配后的数据进行筛选
（4）例子（来自 彭博）
例：
现存在两张表product_details和product

mysql> SELECT * FROM product;
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  2 |    200 |
|  3 |    300 |
|  4 |    400 |
+----+--------+
4 rows in set (0.00 sec)
 
mysql> SELECT * FROM product_details;
+----+--------+-------+
| id | weight | exist |
+----+--------+-------+
|  2 |     22 |     0 |
|  4 |     44 |     1 |
|  5 |     55 |     0 |
|  6 |     66 |     1 |
+----+--------+-------+
4 rows in set (0.00 sec)

对其用左外连接的方式查询id = 2 的所有信息

mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       AND product_details.id=2;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)
 
mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       WHERE product_details.id=2;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
|  2 |    200 |  2 |     22 |     0 |
+----+--------+----+--------+-------+
1 row in set (0.01 sec)

可以明显看出查询结果的区别

首先，两张表会被拼接起来，其中左表的数据全部保留

然后，id = 2的条件放在AND后时 - 满足product.id=product_details.id所有项都会被列出来，不过只有满足id = 2 的行才有右表的相关信息

而将id = 2的条件放在WHERE后时 - 直接对表进行连接
如下：
阶段一：符合条件的都列出所有信息

mysql> SELECT * FROM product LEFT JOIN product_details
    ->          ON (product.id = product_details.id);
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 |    4 |     44 |     1 |
+----+--------+------+--------+-------+
4 rows in set (0.01 sec)

阶段二：对上表进行筛选 id = 2
得到

+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
|  2 |    200 |  2 |     22 |     0 |
+----+--------+----+--------+-------+

这就是上面结果的由来

WHERE … IS NULL 与 LEFT JOIN

如果ON语句中使用多个筛选条件并与 where…is null配合使用时会出现问题

例子

筛选条件为
(a.id=b.id) AND (b.weight!=44) AND (b.exist=0)

无＊WHERE b.id IS NULL＊条件时
mysql> select * from product a left join product_details b
    -> on a.id=b.id and b.weight != 44 and b.exist=0;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)


mysql> SELECT * FROM product a LEFT JOIN product_details b  
       ON a.id=b.id AND b.weight!=44 AND b.exist=0  
       WHERE b.id IS NULL;  
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+
3 rows in set (0.00 sec)

对比原表product_details和product会发现ＯＮ后面*(b.weight!=44) AND (b.exist=0)*的条件并没有使用

这是因为其判断机制和Ｃ语言类似，与C语言中的逻辑 AND 和 逻辑 OR表达式一样，其操作数是从左到右求值的。如果第一个参数做够判断操作结果，那么第二个参数便不会被计算求值（短路效果）

因此就出现了一些变式，其查询结果在某些时候会是一样

SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id
WHERE b.id is null OR b.weight=44 OR b.exist=0;

SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id AND b.weight!=44 AND b.exist!=0
WHERE b.id IS NULL;

注：结果会并不完全一样，参考以上ON 与 WHERE的使用

2 . RIGHT JOIN

总结来讲与 LEFT JOIN一样，只是一个保留了左边的所有数据，一个保留了右边的所有数据
以上案例结果如下

mysql> SELECT * FROM product right JOIN product_details
    ->        ON (product.id = product_details.id);
+------+--------+----+--------+-------+
| id   | amount | id | weight | exist |
+------+--------+----+--------+-------+
|    2 |    200 |  2 |     22 |     0 |
|    4 |    400 |  4 |     44 |     1 |
| NULL |   NULL |  5 |     55 |     0 |
| NULL |   NULL |  6 |     66 |     1 |
+------+--------+----+--------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM product right JOIN product_details
    ->        ON (product.id = product_details.id)
    ->        AND product_details.id=2;
+------+--------+----+--------+-------+
| id   | amount | id | weight | exist |
+------+--------+----+--------+-------+
|    2 |    200 |  2 |     22 |     0 |
| NULL |   NULL |  4 |     44 |     1 |
| NULL |   NULL |  5 |     55 |     0 |
| NULL |   NULL |  6 |     66 |     1 |
+------+--------+----+--------+-------+
4 rows in set (0.00 sec)
与上面的结果对比可以看到，多一个and条件会多进行一次筛选，不满足条件的部分置为null

mysql> SELECT * FROM product right JOIN product_details
    ->        ON (product.id = product_details.id)
    ->        WHERE product_details.id=2;
+------+--------+----+--------+-------+
| id   | amount | id | weight | exist |
+------+--------+----+--------+-------+
|    2 |    200 |  2 |     22 |     0 |
+------+--------+----+--------+-------+
1 row in set (0.00 sec)

3 . INNER JOIN

emmm，其实看完上面的LEFT JOIN和RIGHT JOIN以后，就会发现其他的几种都非常简单了

还是拿上文的案例来说
inner join可以理解为在左右两张表中都查找满足ON后的条件
(product.id = product_details.id)
的部分然后将其拼接起来

简单来说就是取交集

左表：
+------+--------+----+
| id   | amount | id |
+------+--------+----+
|    2 |    200 |  2 |
+------+--------+----+
右表：
+----+--------+-------+
| id | weight | exist |
+----+--------+-------+
|  2 |     22 |     0 |
+----+--------+-------+
然后连接
+------+--------+----+--------+-------+
| id   | amount | id | weight | exist |
+------+--------+----+--------+-------+
|    2 |    200 |  2 |     22 |     0 |
+------+--------+----+--------+-------+

OVER！

4 .FULL （OUTER） JOIN

从概述的图中可以看出full join其实就是取并集
而在实际运用中是比较少的（参照一下普通的连接）

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full join...'

本来想测试一下full join的效果，可是MySQL 5.5 好像不支持
因此可以参考的是另外一种变式
就是左连接并上右连接

SELECT * FROM product left JOIN product_details ON (product.id = product_details.id)
unoin all
SELECT * FROM product right JOIN product_details ON (product.id = product_details.id)

最后的结果就是上面的左连接与右连接的并
关于UNION　ALL 请自行参考资料

最后

至此：有关JOIN的一些基础用法就差不多结束了，有兴趣的可以刷一些数据库的编程题，能在实际中运用才是真正学到了

查找当前薪水详情以及部门编号dept_no

查找所有已经分配部门的员工的last_name和first_name以及dept_no

查找所有员工的last_name和first_name以及对应部门编号dept_no

查找所有员工入职时候的薪水情况

网络上的资源很多，修行看个人

共勉！

参考资料
1 . LEFTJOIN
2 . 菜鸟教程

路瞳
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
非常详细的SQL--JOIN之完全用法
09-11
SQL Server 也支持旧式 Transact-SQL 外联接语法，使用 *= 和 =* 运算符指定外联接。但是，这种语法可能会产生歧义，因此建议使用 SQL-92 语法。 实践应用 JOIN 操作在实际应用中有非常广泛的应用场景，例如： * ...
SQL：SQL-92，SQL-99和SQL-2003的BNF语法
01-30
SQL-92是SQL的第一个正式标准，它为SQL提供了更严谨的定义，引入了新的关键字和语句，如`JOIN`、`INNER JOIN`、`LEFT JOIN`、`RIGHT JOIN`等，以及`DECLARE`、`BEGIN`、`END`等用于创建存储过程的控制结构。...
MySQL LEFT JOIN (where is null 否定 on里面条件)
万里船的专栏
09-05 7242
即使你认为自己已对 MySQL 的 LEFT JOIN 理解深刻，但我敢打赌，这篇文章肯定能让你学会点东西！ ON 子句与 WHERE 子句的不同 一种更好地理解带有 WHERE ... IS NULL 子句的复杂匹配条件的简单方法 Matching-Conditions 与 Where-conditions 的不同 关于 “A LEFT JOIN B ON 条件表达式” 的一点提醒
[419]sql语法—left join on 多条件
周小董
10-21 2万+
问题：如果有A表和B表，A表有a1,a2,a3…an字段，B表有b1，b2,b3…bn字段，想查出同时满足条件a1=b1,a2=b2,a3=b3这三个条件的所内容？ 用内连接： select a.*, b.* from a left join b on a1=b1 and a2=b2 and a3=b3 和楼上那个相比，楼上是在联接的时候就过滤了，我的是联接后过滤，两个结果是不一样的 sel...
关于left join ... is null 的奇怪思路
laozaoxiaowanzi的博客
04-13 2151
虽然题目有点拗口难懂，不知我到底在解决什么问题。我通过例子的方法来说明，就会一目了然了。 最近在做的一个需求，其中一部分要求是：查询出有过什么什么的客户且过滤掉什么的客户，其实就是各种表的联结再加上一些过滤条件。这都不重要，重要的是，这两个过滤条件我写的很有问题。 如下： 假如有一个表tb_ftc（不是真实业务场景的表） c_id ch_no c_st ... 01023 1 -1 02345 3 2 13132 2 ..
hive中join on和where一起使用时的执行顺序及关联字段为null的情况
fuyun6363的博客
02-17 7941
查看很多博客，都说join on和where一起使用时，先进行join产生临时表，再进行where条件过滤得到结果表，在面试中问到也有面试官是这么解释的，但使用explain查看执行计划，你会发现执行顺序并不是这样。 在两表join时如果关联字段为null，有些博客上写会产生数据倾斜或笛卡儿积，其实不会，因为在join之前就会对join的表中关联字段进行非空过滤。 > explain sel...
内连接，左连接和右连接作用
llthh的博客
03-17 1440
在数据库查询中，经常会遇到多表连接来查询数据，下面来说一下他们的连接方法和作用 内连接：内连接是很常用的一个连接方法，用来查询两个表或者多个表之间相同的数据，例如： 查询的是他们之间相同的数据 内连接用到的方法是innerf join ： select *from 主表名 别名 inner join 表名 别名 on …………… （拥有的相同数据） 左连接：左连接也是多表查询的，主要是查询左表的全部数据，右表查询不到的用null来表示，例如： 查询的都是左表以及右表有关的相同数据 左连接用到的方法是le
x-pack-sql-jdbc-7.4.2
02-22
这款组件的出现，极大地简化了对Elasticsearch数据的查询、分析和管理过程，同时也让那些熟悉SQL语法的开发者能够更快地融入Elasticsearch的生态环境。 一、SQL支持与兼容性 X-Pack SQL JDBC的核心功能在于它对SQL...
parent-join-client-6.2.3-API文档-中文版.zip
04-07
赠送jar包：parent-join-client-6.2.3.jar 赠送原API文档：parent-join-client-6.2.3-javadoc.jar 赠送源代码：parent-join-client-6.2.3-sources.jar 包含翻译后的API文档：parent-join-client-6.2.3-javadoc-...
x-pack-sql-jdbc.rar
09-01
这个驱动的两个版本——x-pack-sql-jdbc-7.7.1.jar和x-pack-sql-jdbc-7.5.1.jar，分别对应Elasticsearch的7.7.1和7.5.1版本，确保与不同版本的Elasticsearch系统兼容。 1. **JDBC接口介绍** JDBC是Java中用于访问...
关于Left join，你可能不知道这些......
hitchenghengchao的博客
10-10 1万+
点击上方“超哥的杂货铺”，轻松关注join 是 SQL查询中很常见的一种操作，具体来讲有join，left join, right join，full join等很多形式...
MySQL LEFT JOIN (where is null 否定 on里面条件)
m0_58028961的博客
02-24 666
即使你认为自己已对MySQL的 LEFT JOIN 理解深刻，但我敢打赌，这篇文章肯定能让你学会点东西！ ON 子句与 WHERE 子句的不同 一种更好地理解带有 WHERE ... IS NULL 子句的复杂匹配条件的简单方法 Matching-Conditions 与 Where-conditions 的不同 关于 “A LEFT JOIN B ON 条件表达式” 的一点提醒 ON条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从B表中检索数据行。 如果B...
SQL语法——left join on 多条件
热门推荐
minixuezhen的博客
03-30 35万+
left join on +多条件与where区别 重点 先匹配，再筛选where条件。 本文将通过几个例子说明两者的差别。 表1：product id amount 1 100 2 200 3 300 4 400 表2：product_details id weight exist ...
left join on 和where条件的放置
一起玩编程
05-30 31万+
select * from td left join ( select case_id as sup_case_id , count(*) supervise_number from td_kcdc_case_sup_info group by case_id ) sup on sup.sup_case_id = td.case_id where
MySQL left join on 左表连接字段为null的返回结果
花郎徒结的博客
07-17 5万+
写个博客备忘用... MySQL 左连接(右连接类似)查询，例 select * from A left join B on A.id = B.id where... A表是会返回所有符合where条件的记录，B表不满足A.id=B.id的记录则不会返回(同时B也要满足where条件) 如果B的连接字段id本身为null，那么视为不满足on的条件，不返回该条记录。 那么如果
使用left join连接带NULL值数据表案例
OYJJ的专栏
05-01 1万+
insert into t_user values(1,'use1','123','user1',1,0,'test@qq.com',0,now()); insert into t_user values(2,'use2','123','user2',1,0,'test@qq.com',0,now());insert into t_message values(1,'msg11','content1
多表left join where is null 的优化_一文搞懂spark sql中的CBO（基于代价的优化）
weixin_39574869的博客
12-09 536
Spark CBO 背景。。。。。。。。。。。我们在是时候学习真正的spark技术了这篇文章中介绍了很多基于规则的优化方式，也就是RBO，实现简单有效。它属于 LogicalPlan 的优化，所有优化均基于 LogicalPlan 本身的特点，未考虑数据本身的特点，也未考虑算子本身的代价。本文将介绍 CBO，它充分考虑了数据本身的特点(如大小、分布)以及操作算子的特点(中间结果集的分...
mysql 之left join
aybaylyn的博客
07-24 408
[size=medium] 转自：[url]http://www.oschina.net/question/89964_65912[/url] left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的. 换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例如: A.aID = B.bID) （A....
mybatis-plus-join-boot-starter
最新发布
07-15
MyBatis-Plus-Join-Boot-Starter 是一个用于简化 MyBatis-Plus 在 Spring Boot 项目中进行关联查询的扩展库。它提供了一些便捷的注解和方法，使得在使用 MyBatis-Plus 进行关联查询时更加方便和高效。你可以通过添加...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值