得意犹堪夸世俗，诏黄新湿字如鸦。这篇文章主要讲述Join 和 Apply 用法全解相关的知识，希望能为你提供帮助。
       
在关系型数据库系统中，为了满足第三范式（3NF），需要将满足“传递依赖”的表分离成单独的表，通过Join 子句将相关表进行连接，Join子句共有三种类型：外连接，内连接，交叉连接；外连接分为：left join、right join、full join；内链接是：inner join，交叉连接是：cross join。
一，Join子句的组成
Join子句由连接表，连接类型和On子句组成，伪代码如下：

from  Left_Table [inner|left|right|full]  join  Right_Table         [on  condition]

1，根据位置，将参与Join的两个表分为左表和右表

• 在Join子句中，左表和右表进行笛卡尔集合运算，左表中的任意一行都和右表中的所有行进行“组合”，生成虚拟表（Virtual Table），虚拟表的数据行总数Rows（VT）=Rows(left_table)*Rows(right_table)；
  
• 左表和右表进行Join操作，没有先后顺序，这点和Apply子句不同，Apply子句的左表先于右表执行运算；
  

2，连接类型
在外连接中，left，right和full关键字标识Join子句的"保留表"：在进行外连接查询时，保留表中的数据全部返回，不会被on子句过滤。
3，On子句，用于都虚拟表进行过滤
在on子句表达式中，常用的运算符是相等（=），也可以使用不等(> ,< > )，like等运算符，返回的结果是布尔值；
on子句表达式的操作数，可以是表列（Column），常量，表达式，例如；

• on left_table.column=right_table.column
  
• on left_table.column=value
  
• on left_table.column+xx=value
  
• 不过滤：比如设置on 1=1
  

4，On子句决定Join的顺序
如果一个查询包含多个Join子句，那么On子句决定Join子句执行的顺序；执行Join的顺序是：tb和tc先执行连接操作，ta和tb后执行连接操作。

from  taleft  join  tbleft  join  tc        on  tb.column=tc.column         on  ta.column=tb.column

5，On子句过滤和Where子句过滤
On子句的执行顺序先于where子句，在进行过滤时，On子句无法过滤保留表，但是where子句能够过滤保留表；
对于inner join，由于没有保留表，所以，在On子句和where子句中进行过滤，结果是一样的，但是，建议明确区分where子句和on子句的职能，on子句用于过滤连接的虚拟表，where用于对最终的结果集进行过滤。
例如：在On子句中，ta.column2=value1 不会过滤左表ta，如果不满足该条件，那么右表相应的数据列设置为NULL，left关键字保证左表中的所有数据行都返回；where子句（ta.column3=value2）过滤左表ta；

from  taleft  join  tb        on  ta.column1=ta.column1                and  ta.column2=value1where  ta.column3=value2

二，创建测试代码

文章图片
  View Code
三，left join（左外连接）
1，left join算法
把左表作为保留表，返回左表的全部数据，对于右表中不匹配on子句条件数据行，返回NULL；

select  *  from  dbo.ta  a  left  join  dbo.tb  b      on  a.a=b.ca

文章图片

2，使用常量过滤左表
在左外连接中，左表会返回所有数据，对于“and left_table.column=value”，是在第一个条件成立时，对返回的结果进行过滤，而左表数据会全部返回，当不满足条件时，设置右表数据为NULL；

select  *  from  dbo.ta  a  left  join  dbo.tb  b      on  a.a=b.ca  and  a.a=1

文章图片

3，使用where子句过滤左表
where子句是对结果集进行过滤的最后一个Filter

select  *  from  dbo.ta  a  left  join  dbo.tb  b      on  a.a=b.ca  where  a.a=1

文章图片

4，使用where子句过滤右表
如果使用where子句对右表进行过滤，一般可以转换成inner join

select  *  from  dbo.ta  a  left  join  dbo.tb  b  on  a.a=b.ca  where  b.ca=1

文章图片

四，right join（右外连接）
right join 算法是把右表作为保留表，将右表中的数据全部显示出来，对于左表中匹配不到的数据行，将其字段值设置为NULL；

select  *  from  dbo.ta  a  right  join  dbo.tb  b  on  a.a=b.ca

文章图片

五，inner join（内连接）
算法是：inner join没有保留表，只返回满足 on 子句条件的数据行，对于不满足on子句条件的数据行，不返回

select  *  from  dbo.ta  a  inner  join  dbo.tb  b  on  a.a=b.ca

文章图片

六，full join（全连接）
算法是：full join 把左表和右表都作为保留表，如果左表和右表中的数据行满足On子句条件，那么显示数据行数据，如果不匹配，则相应的字段设置为null。

select  *  from  dbo.ta  a  full  join  dbo.tb  b  on  a.a=b.ca

文章图片

七，cross join（交叉连接）
算法是：cross join 是对左表和游标进行笛卡尔乘积，cross join没有on子句，笛卡尔乘积是将左表中的任意一行数据和右表中的所有数据行进行组合，cross join 将笛卡尔乘积后的结果直接显示出来

select  *  from  dbo.ta  a  cross  join  dbo.tb  b

文章图片

八，自连接用于累积求和
自连接是指一个table 和自己进行join，例如以下语句，表 dbo.ta和自身进行inner join，计算b字段的累积和。

select  t1.a,sum(t2.b)  as  b  from  dbo.ta  t1  inner  join  dbo.ta  as  t2  on  t1.a> =t2.a  group  by  t1.a

在实际的产品环境中，经常利用自联结进行累加求和的计算，例如有如下一个Table：dbo.FinanceMonth，每个月的产量是Quantity，计算一年内到该月份为止的所有月份的Quantity的累积值。

文章图片
  View Code

文章图片

使用自链接计算累积值

文章图片

select  a.MonthNum,sum(b.quantity)  as  TotalQuantity  from  dbo.FinanceMonth  a  inner  join  dbo.FinanceMonth  b  on  a.MonthNum> =b.MonthNum  group  by  a.MonthNum  order  by  a.MonthNum

文章图片


文章图片

九，apply 用法
1，join和apply的区别
join 子句左表和右表的计算是不分先后的，从性能上考虑，最好把小表作为左表，当右表数据量大的时候，会减少查询的时间消耗。apply子句的左表和右表是区分先后顺序的，apply是先计算左表，后计算右表，因此apply子句不是集合操作语句。如果右表是一个表值函数，apply会先取得左表中的一行记录的值，作为参数值传递给表值函数进行计算，左表中的一行记录和“右表”进行笛卡尔乘积做为最终结果。如果右表查询出来的结果是空的，那么右表字段设置为null。

文章图片

select  *  from  dbo.ta  a  outer  apply  (select  *  from  dbo.tb  b  where  a.a=b.ca)  p

文章图片


文章图片

从查询结果上看，跟left join是相同的，但是在性能上，outer apply 比left join要差，因为TSQL 擅长集合操作，使用集合的思想编写的代码性能一般都很高，left join是集合操作语句，性能优于outer apply
虽然apply性能低，但是也有其用武之地，当需要按照顺序进行连接时，apply是最好的选择。
2，apply的两种用法
outer apply 和cross apply的相同点是：

• 先计算左表，后计算右表；
  
• 对左表中的每一行记录，右表都要“逐行”计算，类似于相关子查询，实际上，TSQL对apply进行优化之后，并不是逐行，而是逐N行；
  

outer apply 和cross apply的不同点是：

• outer apply：将左表作为保留表，如果右表没有匹配行，那么右表中的字段会设置为null，类似于left join。
  
• cross apply：没有保留表，对于左表中的一行记录，如果右表中没有匹配行，那么该行记录不显示在最终结果集中，类似于inner join。
  

文章图片

select  *  from  dbo.ta  a  cross  apply  (select  *  from  dbo.tb  b  where  a.a=b.ca)  p

文章图片


文章图片

十，join语句的应用
1，使用cross join能够快速产生大量顺序数字
cross join的结果集中数据行的数量是：左表数据行数和右表数据行数的乘积，由于每个table都有10个数字（从0到9），4个table进行cross join能够快速产生10的4次方，即10000个顺序数字。

文章图片

; with  num  as  (  select  n  from(values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))  as  p(n)  )  select  a.n+b.n*10+c.n*100+d.n*1000  as  n  --into  dbo.num  from  num  across  join  num  b  cross  join  num  c  cross  join  num  d  order  by  n

文章图片

2，使用left join 查询不存在于右表的数据行
如果左表中的数据不存于右表，那么右表的字段是null，通过在 where 子句中设置filter，能够查询出存在于左表，但是不存在于右表的数据行

select  *  from  dbo.ta  t1  left  join  dbo.tb  t2  on  t1.a=t2.ca  where  t2.ca  is  null;

 
【Join 和 Apply 用法全解】

推荐阅读

• 

  什么是碱性岩
• 

  洛阳奥斯卡影院6月13号放映什么电影？洛阳奥斯卡影院今日放映表
• 

  京东“京银计划” 是什么 怎么参与？
• 

  共栅极放大电路分析,场效应管共栅极放大电路
• 

  2022年三伏天时间表？
• 

  活珠子不适合哪些人吃
• 

  m当前“最接近完美”的五款手机，别看价格贵，用三年毫无压力
• 

  上海电商有哪些 什么是电商上海，什么是电商上海人
• 

  家用茶具多少钱的合适 – 高档茶盘图片价格表
• 

  冰箱不结冰是怎么回事？原因分析与解决方法
• 

  热水器不烧水怎么办,是因为这些原因
• 

  增加注册资本后股东的股权比例
• 

  白发傅筹第几集知道身世
• 

  CPB水前肌底精华好不好？CPB水前肌底精华怎么用
• 

  兄弟T310如何清零废墨？
• 

  炒杜仲与生杜仲的区别 炒杜仲与生杜仲的不同之处
• 

  《侠客风云传前传》激活方法及特权简单介绍
• 

  取暖器品牌排名？哪个比较好的？
• 

  中风|女性中风有奇特症状？盘点易被女性忽视的11个中风症状！
• 

  减肥可以吃红枣吗

• android studio环境变量配置
• Android NDK开发篇（Java与原生代码通信(原生方法声明与定义与数据类型)）
• 浩哥解析MyBatis源码——binding绑定模块之MapperRegisty
• 如何在Python中下载Torrent文件（代码实现示例）
• 如何在Python中使用Google自定义搜索引擎API（）
• 如何在Python中使用Github API（用法示例教程）
• 如何在Python中使用Google Drive API（用法示例）
• 如何在Python中翻译语言（代码实现示例）
• 如何在Python中制作URL缩短器（代码实现示例）