SQL Joins and exclusions

Assuming a query as such in T-SQL.

   SELECT *  
     FROM Stock S    
LEFT JOIN StockBarcode SB ON SB.StockID = S.StockID
                         AND SB.ShopID = @ShopID 
                         AND SB.Inactive = 0    
LEFT JOIN StockBarcode SB1 ON SB1.StockID = S.StockID
                          AND SB1.ShopID = 0 
                          AND SB1.Inactive = 0    
    WHERE S.StockID = @StockID

My understanding is that I could rewrite the query as such

   SELECT * 
     FROM Stock S    
LEFT JOIN StockBarcode SB ON S.StockID = SB.StockID 
                         AND SB.ShopID = @ShopID    
LEFT JOIN StockBarcode SB1 ON S.StockID = SB1.StockID 
                          AND SB1.ShopID = 0     
    WHERE S.StockID = @StockID 
      AND ISNULL(SB.Inactive, 0) = 0 
      AND ISNULL(SB1.Inactive, 0) = 0

...and the results would be the same. (Please correct me if I am wrong) Which is the optimal query and why? Would the case be different if I was using another database engine such as MySql?

Thanks in advance to any answers :-)

EDIT: For clarification here is the entire query as it stands at the moment if that will help.

SELECT 
    SSCLRU.SupplierCode,  
    S.[Description],
    S.TaxRate AS GSTRate,
    ISNULL(ISNULL(SB.PackPrice, SB1.PackPrice), S.RRP) AS Price,
    ISNULL(SB.PackSize, SB1.PackSize) AS Quantity,
    ISNULL(SB.SalePrice, SB1.SalePrice) AS SalePrice,
    ISNULL(SB.SaleDateFrom, SB1.SaleDateFrom) AS SalePriceStartDate,
    ISN开发者_高级运维ULL(SB.SaleDateTo, SB1.SaleDateTo) AS SalePriceEndDate
FROM Stock S

LEFT JOIN StockSupplierCodePreferredLastReceivedUnique SSCLRU ON
S.StockID = SSCLRU.StockID

LEFT JOIN StockBarcode SB ON
S.StockID = SB.StockID AND
SB.ShopID = @ShopID AND
SB.Inactive = 0

LEFT JOIN StockBarcode SB1 ON
S.StockID = SB1.StockID AND
SB1.ShopID = 0 AND
SB1.Inactive = 0

WHERE S.StockID = @StockID 

---

The 1st one is clearer because you don't have to worry about non-matching rows in the WHERE

However, I'd probably use this construct to fully separate join and filter conditions

FROM
   Stock S
   LEFT JOIN
   StockSupplierCodePreferredLastReceivedUnique SSCLRU ON S.StockID = SSCLRU.StockID

   LEFT JOIN
   (
    SELECT StockID, ...
    FROM StockBarcode
    WHERE ShopID = @ShopID AND Inactive = 0
   ) SB ON S.StockID = SB.StockID

   LEFT JOIN
   (
    SELECT StockID, ...
    FROM StockBarcode
    WHERE ShopID = 0 AND Inactive = 0
   ) SB1 ON S.StockID = SB1.StockID
WHERE
   S.StockID = @StockID 

The derived tables could be pushed into a CTE (or 2 ) as well.

---

They are not quite the same, unless StockBarcode.Inactive is not nullable.

If StockBarcode.Inactive is nullable, then the first query will not return any details for StockBarcodes where Inactive is null (since they fail the join condition), while the second query will include them if they match the other join conditions - they will match the where condition.

---

Which is the optimal query and why?

Look at the query plan.

But I bet the 1st should be more performant, since SB.Inactive = 0 condition can be covered by index.

Would the case be different if I was using another database engine such as MySql?

Sure, the execution plan and performance is strictly dependent on vendor.