最详细的 join 功能详解

于 2023-09-06 15:22:45 发布
阅读量334 收藏
点赞数 1
文章标签： 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Heoric/article/details/132716324
版权

从 join 语法到 join 算法

前言

网上各种讲 join 的，讲的我迷糊。各种 cross join 、natural join、left join、semi join 等等。

今天就来一次性把他们讲明白了。

构造数据

我们先构造数据，通过例子把它们都讲明白。

 create table person(userid int,name char(30));
 insert into person values(1001,'张三');
 insert into person values(1002,'李四');
 insert into person values(1003,'王二麻子');
 insert into person values(1004 ,'刘五');
 insert into person values(1005, '毛八');
 insert into person values(1006, '孙西瓜');
 insert into person values(1007, '陈小坏');
 insert into person values(1009, '刘妞妞');
 ​
 create table scorep(userid int,score int);
 insert into scorep values(1001,100);
 insert into scorep values(1002,98);
 insert into scorep values(1004,99);
 insert into scorep values(1005,89);
 insert into scorep values(1007,88);
 insert into scorep values(1008,92);
 ​

语法分类

首先，我们看语法层面的 join 类型。就是 可以直接在 sql 层面写出来的。

cross join

cross join 是做笛卡尔积。输出全部数据。

 笛卡尔积
 select * from person , scorep;
 select * from person cross join scorep;
   uid  |   name   | uid  | score
 -------+----------+------+--------
   1001 | 张三     | 1001 |   100
   1001 | 张三     | 1002 |    98
   1001 | 张三     | 1004 |    99
   1001 | 张三     | 1005 |    89
   1001 | 张三     | 1007 |    88
   1001 | 张三     | 1008 |    92
   1002 | 李四     | 1001 |   100
   1002 | 李四     | 1002 |    98
   1002 | 李四     | 1004 |    99
   1002 | 李四     | 1005 |    89
   1002 | 李四     | 1007 |    88
   1002 | 李四     | 1008 |    92
   1003 | 王二麻子 | 1001 |   100
   1003 | 王二麻子 | 1002 |    98
   1003 | 王二麻子 | 1004 |    99
   1003 | 王二麻子 | 1005 |    89
   1003 | 王二麻子 | 1007 |    88
   1003 | 王二麻子 | 1008 |    92
   1004 | 刘五     | 1001 |   100
   1004 | 刘五     | 1002 |    98
   1004 | 刘五     | 1004 |    99
   1004 | 刘五     | 1005 |    89
   1004 | 刘五     | 1007 |    88
   1004 | 刘五     | 1008 |    92
   1005 | 毛八     | 1001 |   100
   1005 | 毛八     | 1002 |    98
   1005 | 毛八     | 1004 |    99
   1005 | 毛八     | 1005 |    89
   1005 | 毛八     | 1007 |    88
   1005 | 毛八     | 1008 |    92
   1006 | 孙西瓜   | 1001 |   100
   1006 | 孙西瓜   | 1002 |    98
   1006 | 孙西瓜   | 1004 |    99
   1006 | 孙西瓜   | 1005 |    89
   1006 | 孙西瓜   | 1007 |    88
   1006 | 孙西瓜   | 1008 |    92
   1007 | 陈小坏   | 1001 |   100
   1007 | 陈小坏   | 1002 |    98
   1007 | 陈小坏   | 1004 |    99
   1007 | 陈小坏   | 1005 |    89
   1007 | 陈小坏   | 1007 |    88
   1007 | 陈小坏   | 1008 |    92
   1009 | 刘妞妞   | 1001 |   100
   1009 | 刘妞妞   | 1002 |    98
   1009 | 刘妞妞   | 1004 |    99
   1009 | 刘妞妞   | 1005 |    89
   1009 | 刘妞妞   | 1007 |    88
   1009 | 刘妞妞   | 1008 |    92
 (48 rows)
 ​

Natural join

natural join是对两张表中字段名和数据类型都相同的字段进行等值连接，并返回符合条件的结果 。 natural join是自然连接,自动对两个表按照同名的列进行连接

natural join 可以跟 left 、right 、full、inner 。不写默认是 inner join。

语法如下 使用自然连接要注意，两个表同名的列不能超过1个。

  select * from person natural join scorep;
   uid  |  name  | score
 -------+--------+--------
   1001 | 张三   |   100
   1002 | 李四   |    98
   1004 | 刘五   |    99
   1005 | 毛八   |    89
   1007 | 陈小坏 |    88
 (5 rows)
 ​

inner join

 root@localhost:26257/defaultdb> select * from person t1 join scorep t2 on t1.uid = t2.uid;
   uid  |  name  | uid  | score
 -------+--------+------+--------
   1001 | 张三   | 1001 |   100
   1002 | 李四   | 1002 |    98
   1004 | 刘五   | 1004 |    99
   1005 | 毛八   | 1005 |    89
   1007 | 陈小坏 | 1007 |    88
 (5 rows)

outer join

Left join

 root@localhost:26257/defaultdb> select * from person t1 left join scorep t2 on t1.uid = t2.uid;
   uid  |   name   | uid  | score
 -------+----------+------+--------
   1001 | 张三     | 1001 |   100
   1002 | 李四     | 1002 |    98
   1003 | 王二麻子 | NULL |  NULL
   1004 | 刘五     | 1004 |    99
   1005 | 毛八     | 1005 |    89
   1006 | 孙西瓜   | NULL |  NULL
   1007 | 陈小坏   | 1007 |    88
   1009 | 刘妞妞   | NULL |  NULL
 (8 rows)
 ​

Right join
 root@localhost:26257/defaultdb> select * from person t1 right join scorep t2 on t1.uid = t2.uid;
   uid  |  name  | uid  | score
 -------+--------+------+--------
   1001 | 张三   | 1001 |   100
   1002 | 李四   | 1002 |    98
   1004 | 刘五   | 1004 |    99
   1005 | 毛八   | 1005 |    89
   1007 | 陈小坏 | 1007 |    88
   NULL | NULL   | 1008 |    92
 (6 rows)
 ​

Full join
 root@localhost:26257/defaultdb> select * from person t1 full join scorep t2 on t1.uid = t2.uid;
   uid  |   name   | uid  | score
 -------+----------+------+--------
   1001 | 张三     | 1001 |   100
   1002 | 李四     | 1002 |    98
   1003 | 王二麻子 | NULL |  NULL
   1004 | 刘五     | 1004 |    99
   1005 | 毛八     | 1005 |    89
   1006 | 孙西瓜   | NULL |  NULL
   1007 | 陈小坏   | 1007 |    88
   1009 | 刘妞妞   | NULL |  NULL
   NULL | NULL     | 1008 |    92
 (9 rows)
 ​

从实现层面来讲讲这几种 join。 首先看看如何解析 join。 然后我们需要一个数据结构来存储这几种join。

这里关键的信息是 jion 条件和 jion类型。

  • 条件分为三种 。 on、using 和 nature。

  • 类型分为 不写，inner、cross 这三个会转化为 inner 。left、right、full。 四种。

 ​
 joined_table:
 | table_ref CROSS opt_join_hint JOIN table_ref
   {
     $$.val = &tree.JoinTableExpr{JoinType: tree.AstCross, Left: $1.tblExpr(), Right: $5.tblExpr(), Hint: $3}
   }
 | table_ref join_type opt_join_hint JOIN table_ref join_qual
   {
     $$.val = &tree.JoinTableExpr{JoinType: $2, Left: $1.tblExpr(), Right: $5.tblExpr(), Cond: $6.joinCond(), Hint: $3}
   }
 | table_ref JOIN table_ref join_qual
   {
     $$.val = &tree.JoinTableExpr{Left: $1.tblExpr(), Right: $3.tblExpr(), Cond: $4.joinCond()}
   }
 | table_ref NATURAL join_type opt_join_hint JOIN table_ref
   {
     $$.val = &tree.JoinTableExpr{JoinType: $3, Left: $1.tblExpr(), Right: $6.tblExpr(), Cond: tree.NaturalJoinCond{}, Hint: $4}
   }
 | table_ref NATURAL JOIN table_ref
   {
     $$.val = &tree.JoinTableExpr{Left: $1.tblExpr(), Right: $4.tblExpr(), Cond: tree.NaturalJoinCond{}}
   }

 type JoinTableExpr struct {
   JoinType string
   Left     TableExpr
   Right    TableExpr
   Cond     JoinCond
   Hint     string
 }
 ​
 // JoinTableExpr.Join
 const (
   AstFull  = "FULL"
   AstLeft  = "LEFT"
   AstRight = "RIGHT"
   AstCross = "CROSS"
   AstInner = "INNER"
 )
 ​
 // JoinTableExpr.Hint
 const (
   AstHash     = "HASH"
   AstLookup   = "LOOKUP"
   AstMerge    = "MERGE"
   AstInverted = "INVERTED"
 )

Left anti join

 select * from person t1 left anti join scorep t2 on t1.uid = t2.uid
 ​
 -- not in
 ​
 root@localhost:26257/defaultdb> select * from person where uid not in (select uid from scorep);
   uid  |   name
 -------+-----------
   1003 | 王二麻子
   1006 | 孙西瓜
   1009 | 刘妞妞
 (3 rows)
 ​
 -- not exists
 ​
 root@localhost:26257/defaultdb> select * from person t1 where not exists (select * from scorep t2 where t2.uid = t1.uid);
   uid  |   name
 -------+-----------
   1003 | 王二麻子
   1006 | 孙西瓜
   1009 | 刘妞妞
 (3 rows)
 ​

Left semi join

 select * from person t1 left semi join scorep t2 on t1.uid = t2.uid;
 ​
 root@localhost:26257/defaultdb> select * from person where uid in (select uid from scorep);
   uid  |  name
 -------+---------
   1001 | 张三
   1002 | 李四
   1004 | 刘五
   1005 | 毛八
   1007 | 陈小坏
 (5 rows)
 ​
 root@localhost:26257/defaultdb> select * from person t1 where exists (select * from scorep t2 where t2.uid = t1.uid);
   uid  |  name
 -------+---------
   1001 | 张三
   1002 | 李四
   1004 | 刘五
   1005 | 毛八
   1007 | 陈小坏
 (5 rows)
 ​

join 算法分类

hash join， sort merge jion。nestloop join

Heoric
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
sql 中jion用法
weixin_43287568的博客
04-24 336
一、连接查询分类： 1.交叉连接（不常用）: 返回被连接的两个表所有数据行的笛卡尔积； 返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查 询条件的数据行数。 2.内连接： inner join 或 join 3.外连接 左外连接：left join 或 left outer join 右外连接：right join 或 right outer join 完全外连接：full join 或 full outer join 二、连接查询实例： 数据库中有两张表，一张人员（T_P
MongoDB中如何使用JOIN操作详解
09-09
然而，在实际应用中，有时我们需要连接不同集合的数据，MongoDB 从 3.2 版本开始引入了 $lookup 聚合操作符，来实现类似 SQL 中 JOIN 的功能。 在 SQL 中，JOIN 用于合并来自多个表的数据，通过共享的键来匹配记录...
Mysql - JOIN详解
weixin_34234829的博客
07-09 147
0 索引 JOIN语句的执行顺序 INNER/LEFT/RIGHT/FULL JOIN的区别 ON和WHERE的区别 1 概述 一个完整的SQL语句中会被拆分成多个子句，子句的执行过程中会产生虚拟表(vt)，但是结果只返回最后一张虚拟表。从这个思路出发，我们试着理解一下JOIN查询的执行过程并解答一些常见的问题。如果之前对不同J...
SQL的JOIN语法解析(inner join, left join, right join, full outer join的区别)
勇敢的心的网络笔记
08-25 562
总的来说，四种JOIN的使用/区别可以描述为: left join 会从左表(shop)那里返回所有的记录，即使在右表(sale_detail)中没有匹配的行。 right outer join 右连接，返回右表中的所有记录，即使在左表中没有记录与它匹配 full outer join 全连接，返回左右表中的所有记录 在表中存在至少一个匹配时，inner join 返回行。 关键字in
JOIN详解
点点滴滴一世界
10-20 915
JOIN详解 SQL中JOIN有多种：JOIN、INNER JOIN、FULL JOIN、FULL OUTER JOIN、LEFT JOIN、LEFT OUTER JOIN、RIGHT JOIN、LEFT OUTER JOIN。 同时不还要注意ON ，WHERE等条件使用。 注： 1、只有 FULL JOIN 完全强制连接顺序 2、大多数 LEFT JOIN 或 RI
Mysql - JOIN 详解
beidaol的专栏
05-14 259
Mysql - JOIN 详解索引JOIN语句的执行顺序INNER/LEFT/RIGHT/FULL JOIN的区别ON和WHERE的区别概述JOIN的执行顺序举例 转自：https://mp.weixin.qq.com/s/Lk9DeqRUVHDSzjNIS6US5Q 索引 JOIN语句的执行顺序 INNER/LEFT/RIGHT/FULL JOIN的区别 ON和WHERE的区别 概述 一个完整的...
join方法 详解
Alex Mahone的成长之路
07-07 740
@ 先看一个案例：小明和妈妈准备做饭，小明呢，负责买菜，妈妈负责做饭，做饭的前提呢是要等小明买完菜回来才能做饭，想一想如何实现呢？ ​ 以往常方式，我们是这样写的： // 省略了try catch 捕获 new Thread(() -> { Thread.sleep(6_000); System.out.println("买完菜回来了！"); }, "小明").start(); new Thread(() -> { Thread.sleep(5_000); System.
Hash Join功能设计文档1
08-03
《哈希连接（Hash Join）功能设计详解》 哈希连接（Hash Join）是一种在数据库系统中用于执行多表连接查询的高效算法，尤其适用于处理大规模数据。在Cedar数据库系统中，为了优化多表连接的性能，0.3版本引入了Hash...
Hash Join功能开发文档1
08-03
在Cedar数据库系统中，为了应对大规模数据连接操作带来的计算资源和内存资源消耗问题，0.3版本引入了Hash Join功能。这一功能通过构建哈希表来显著提高多表连接查询的效率。 1. **哈希连接的基本原理** - **生成...
laravel高级的Join语法详解以及使用Join多个条件
10-16
在 Laravel 框架中，数据库操作非常便捷，其中Join语句是用于处理多表关联查询的...为了深入理解并熟练运用这些技巧，建议查阅Laravel官方文档中的数据库查询部分，以便获取更多关于Join和其他数据库操作的详细信息。
join方法详解
qq_56572867的博客
11-28 629
join方法作用、用法 作用:因为新的线程加入了我们，所以需要等待新的线程执行完毕再出发 用法：main等待thread1执行完毕，注意是主线程等待子线程 普通用法演示 public class Join { public static void main(String[] args) throws InterruptedException { Thread thread1 = new Thread(new Runnable() { @Overrid
MySQL中join详解
vip97yigang的专栏
09-25 514
一．Join语法概述 join 用于多表中字段之间的联系，语法如下： ... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona table1:左表；table2:右表。 JOIN 按照功能大致分为如下三类： INNER JOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。 LEFT JOIN
mysql中join详解
chuanjiaoye5017的博客
01-05 62
下面是例子分析 表A记录如下： aID aNum 1 a20050111 2 a20050112 3 a20050113 4 a20050114 5 a20050115 表B记录如下: bID bName 1 2...
SQL join语句详解
ShuoTantai的博客
09-09 250
SQL join详解 表一（person） id name 1 刘备 2 赵云 3 华佗 4 诸葛亮 表二（event） id event 1 桃园结义 4 七擒孟获 5 大意失荆州 6 火烧连营 1、innner join（普通join） 内连接是最常见的一种连接，只连接匹配的行。 例： select * from person p...
sql各种join详解
chw65897的博客
04-14 258
话不多说直接来图 stu表 stu2表 left join join之后得到的是stu表和stu2表的共有部分 select stu.id, stu.name, stu.age, stu.work from stu left join stu2 on stu.id = stu2.id 发现lef tjoin之后都是stu表里面的数据，前提是 select 左表.字段 如果是 select 右表....
join方法解析
weixin_34221332的博客
03-20 91
2019独角兽企业重金招聘Python工程师标准>>> ...
join解析和遍历
夜鹏少的博客
11-06 790
第一种解析： var date = '{"user":"admin","pass":"123123"}'; var json = eval("("+ date +")"); alert(json.user) alert(json.pass) 第二种解析： var date = '{"user":"admin","pass":"123123"}'; var json = JSON.par
Mysql Join语法解析与性能分析--通过集合来看join
10年老码农
01-26 545
一．Join语法概述 join 用于多表中字段之间的联系，语法如下： ... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona table1:左表；table2:右表。 JOIN 按照功能大致分为如下三类： INNER JOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。 LEFT JOIN
mysqll的left join详解
最新发布
08-18
在MySQL中，left join可以使用inner join或left outer join来实现，它们的功能是相同的。因此，left join可以帮助我们在查询中获取到左表的所有记录，而不仅仅是与右表有匹配的记录。<span class="em">1</span><span...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值