O'Reilly logo
  • Dustin Waybright thinks this is interesting:

Can you guess what happens if you specify both the predicate that compares the supplier IDs from both sides and the one comparing the supplier country to Japan in the ON clause? Try it.

  S.companyname AS supplier, S.country,
  P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
  LEFT OUTER JOIN Production.Products AS P
    ON S.supplierid = P.supplierid
   AND S.country = N'Japan';


Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012


Specifying Japan as a predicate in the ON clause does not filter rows from the table as it would in the WHERE clause for an OUTER JOIN, instead, it impacts matching. All left rows in this query are preserved.