SQL join on 性能分析

 今天突然遇到一个问题，到底是sql语句先join后where 效率高，还是先where后join高

经过测试是一样的

 SELECT MaterialID,Amount, ABC FROM (
SELECT MaterialID,Amount FROM  SameWayAgileXYtest.dbo.MaterialArrivalDetail WHERE sheetNo IN
 (SELECT TOP 1000 [SheetNo]   
  FROM [SameWayAgileXYtest].[dbo].[MaterialArrivalSheet]
   WHERE MONTH(CreateDate) = 1 AND ArriveTypeID = 1)) AS a 
 JOIN SameWayAgileXYtest.dbo.Material
 ON a.MaterialID = SameWayAgileXYtest.dbo.Material.ID
 JOIN SameWayAgileXYtest.dbo.MaterialABC
 ON SameWayAgileXYtest.dbo.Material.MaterialABC = SameWayAgileXYtest.dbo.MaterialABC.ID  


 SELECT MaterialID,Amount, ABC FROM  SameWayAgileXYtest.dbo.MaterialArrivalDetail 
 JOIN SameWayAgileXYtest.dbo.Material
 ON SameWayAgileXYtest.dbo.MaterialArrivalDetail.MaterialID = SameWayAgileXYtest.dbo.Material.ID
 JOIN SameWayAgileXYtest.dbo.MaterialABC
 ON SameWayAgileXYtest.dbo.Material.MaterialABC = SameWayAgileXYtest.dbo.MaterialABC.ID
 WHERE sheetNo IN
 (SELECT TOP 1000 [SheetNo]   
  FROM [SameWayAgileXYtest].[dbo].[MaterialArrivalSheet]
   WHERE MONTH(CreateDate) = 1 AND ArriveTypeID = 1)
   
  SELECT    MaterialID,Amount, ABC FROM  SameWayAgileXYtest.dbo.MaterialArrivalDetail,SameWayAgileXYtest.dbo.Material
  ,SameWayAgileXYtest.dbo.MaterialABC
  WHERE SameWayAgileXYtest.dbo.MaterialArrivalDetail.MaterialID = SameWayAgileXYtest.dbo.Material.ID
  AND SameWayAgileXYtest.dbo.Material.MaterialABC = SameWayAgileXYtest.dbo.MaterialABC.ID
  AND sheetNo IN
 (SELECT TOP 1000 [SheetNo]   
  FROM [SameWayAgileXYtest].[dbo].[MaterialArrivalSheet]
   WHERE MONTH(CreateDate) = 1 AND ArriveTypeID = 1)

用Ctrl+L运行一遍每个都站33.3%
总结以前老觉得两个不一样，是因为以前是全连接，所以先where后会节省很多数据量。但是如果条件有的话，就不需要那么明细了

但是建议还是先where 后join

PS:今天导入导出基础数据真的很多问题，各种约束要求ak也是第一痴接触，以前只是知道pk,fk,今天才发现还有其他三种check,default,ak(uk).

还有发现数据库的复制方法还真多，以前只知道备份还原、附加、用带数据的sql语句、还有复制表和表结构。今天这个导入导出数据貌似更灵活，但是相对难度也稍微大点，要自己写sql语句，在匹配对用列。不过还是蛮充实的