条件放Join里面与Where里面的区别

--1, 放Join里面
SELECT   a.*, b.StorerCode AS ParentStorerCode
FROM     #tmpINV a
         LEFT JOIN Wms_Bas_Storer b
           ON b.CompanyID = @CompanyID
           AND b.StockID = @StockID
           AND a.ParentStorerID = b.StorerID


--2, 放Where里面
SELECT a.*, b.StorerCode AS ParentStorerCode
FROM   #tmpINV a LEFT JOIN Wms_Bas_Storer b ON a.ParentStorerID = b.StorerID
WHERE  b.CompanyID = @CompanyID
AND    b.StockID = @StockID

第二种写法准确来说是有语病的
将本应该放在外连接里面的条件放到Where里面会导致外连接变成了内连接
如第二种写法得出的结果将跟下面的语句一样
--2.2, 放Where里面就变成了Inner Join
SELECT   a.*, b.StorerCode AS ParentStorerCode
FROM     #tmpINV a
         Inner JOIN Wms_Bas_Storer b
           ON b.CompanyID = @CompanyID
           AND b.StockID = @StockID
           AND a.ParentStorerID = b.StorerID