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

通过下面的使用，来辨析"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


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

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