"in"与"left jion / right join" 在sql使用中的联系与区别

最新推荐文章于 2023-02-07 13:51:40 发布

jackleechina2011212

最新推荐文章于 2023-02-07 13:51:40 发布

阅读量618

点赞数

分类专栏： sql 文章标签：数据库

本文链接：https://blog.csdn.net/jackleechina2011212/article/details/84252541

版权

sql 专栏收录该内容

34 篇文章 0 订阅

订阅专栏

通过下面的使用，来辨析"in"与"left jion / right join" 适合使用的场合。
1）in
sql代码如下


select  sum(actualSpun) as totalYarnPurchaseOrderQuantity 
from ordersummary 
where orderSummaryId in 
	  ( select orderSummaryId 
		from purchaseOrder_material_colorNo_yarnCount_clNoOrYrNo 
		where purchaseOrder_material_colorNo_yarnCount_id in 
			 (select purchaseOrder_material_colorNo_yarnCount_id 
			  from purchaseOrder_material_colorNo_yarnCount 
			  where purchaseOrder_material_colorNo_id in 
				 (select purchaseOrder_material_colorNo_id 
				  from purchaseOrder_material_colorNo 
				  where purchaseOrder_material_id in
					   (select purchaseOrder_material_id 
						from purchaseOrder_material
						where purchaseOrderId =435
						)
				 )
			)
	   )

运行结果如下：
totalYarnPurchaseOrderQuantity
364.624

2）left jion / right join
sql代码


select  sum(actualSpun) as totalYarnPurchaseOrderQuantity,
		purchaseOrderId
from   (select actualSpun, 
			   t3.purchaseOrderId
		from orderSummary 
		right join 
			(select orderSummaryId,
					t2.purchaseOrderId 
			from purchaseOrder_material_colorNo_yarnCount_clNoOrYrNo 
			right join
				 (select purchaseOrder_material_colorNo_yarnCount_id,
						 t1.purchaseOrderId
				  from purchaseOrder_material_colorNo_yarnCount 
				  right join
					 (select purchaseOrder_material_colorNo_id,
							 t0.purchaseOrderId
					  from purchaseOrder_material_colorNo 
					  right join
						   (select purchaseOrder_material_id,
								   purchaseOrder.purchaseOrderId 
							from purchaseOrder_material 
							right join purchaseOrder on purchaseOrder_material.purchaseOrderId=purchaseOrder.purchaseOrderId
							)t0
					 on t0.purchaseOrder_material_id = purchaseOrder_material_colorNo.purchaseOrder_material_id
					 )t1
				on t1.purchaseOrder_material_colorNo_id=purchaseOrder_material_colorNo_yarnCount.purchaseOrder_material_colorNo_id
				)t2
			on t2.purchaseOrder_material_colorNo_yarnCount_id=purchaseOrder_material_colorNo_yarnCount_clNoOrYrNo.purchaseOrder_material_colorNo_yarnCount_id 
			)t3
		on t3.orderSummaryId=orderSummary.orderSummaryId
		)t4
group by purchaseOrderId

运行结果如下：
totalYarnPurchaseOrderQuantity purchaseOrderId
364.624 435
364.624 436
364.624 437
364.624 438
364.624 439
364.624 440
364.624 441
364.624 442
364.624 443
364.624 444
364.624 445

3)in


select  sum(actualSpun) as totalYarnPurchaseOrderQuantity 
from ordersummary 
where orderSummaryId in 
	  ( select orderSummaryId 
		from purchaseOrder_material_colorNo_yarnCount_clNoOrYrNo 
		where purchaseOrder_material_colorNo_yarnCount_id in 
			 (select purchaseOrder_material_colorNo_yarnCount_id 
			  from purchaseOrder_material_colorNo_yarnCount 
			  where purchaseOrder_material_colorNo_id in 
				 (select purchaseOrder_material_colorNo_id 
				  from purchaseOrder_material_colorNo 
				  where purchaseOrder_material_id in
					   (select purchaseOrder_material_id 
						from purchaseOrder_material
						where purchaseOrderId in 
                              (select purchaseOrderId 
							  from purchaseOrder)
						)
				 )
			)
	   )

运行结果如下：
totalYarnPurchaseOrderQuantity
364.624
364.624
364.624
364.624
364.624
364.624
364.624
364.624
364.624
364.624
364.624

[color=red]
比较：
一）
3）中的运行结果类似2），不同的是3）中的列只有最后一个表的列，而2）中有right join 所以保留了最初一个表的列的信息。

二）
若要得到的结果是具体“某一类”的结果，可以用1）的写法，1）中没有用到left join和right join 执行效率应该更快，并且因为是具体某一类的结果，所以直接知道最初表的那一列的purchaseOrderId的值。[/color]