Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
SELECT S.companyname AS supplier, S.country, P.productid, P.productname, P.unitprice FROM Production.Suppliers AS S INNER JOIN Production.Products AS P ON S.supplierid = P.supplierid WHERE S.country = N'Japan'; SELECT S.companyname AS supplier, S.country, P.productid, P.productname, P.unitprice FROM Production.Suppliers AS S, Production.Products AS P WHERE S.supplierid = P.supplierid AND S.country = N'Japan';
Because the Production.Suppliers table is the preserved side of the join, Supplier XYZ is returned even though it has no matching products. As you recall, an inner join did not return this supplier. It is very important to understand that, with outer joins, the ON and WHERE clauses play very different roles, and therefore, they aren’t interchangeable. The WHERE clause still plays a simple filtering role—namely, it keeps true cases and discards false and unknown cases. In our query, the WHERE clause filters only suppliers from Japan, so suppliers that aren’t from Japan simply don’t show up in the output. However, the ON clause doesn’t play a simple filtering role; rather, it’s more a matching role. In other words, a row in the preserved side will be returned whether the ON predicate finds a match for it or not. So the ON predicate only determines which rows from the nonpreserved side get matched to rows from the preserved side—not whether to return the rows from the preserved side. In our query, the ON clause matches rows from both sides by comparing their supplier ID values. Because it’s a matching predicate (as opposed to a filter), the join won’t discard suppliers; instead, it only determines which products get matched to each supplier. But even if a supplier has no matches based on the ON predicate, the supplier is still returned. In other words, ON is not final with respect to the preserved side of the join. WHERE is final. So when in doubt whether to specify the predicate in the ON or WHERE clauses, ask yourself: Is the predicate used to filter or match? Is it supposed to be final or nonfinal?
Join the community of 500,000 technology professionals and ask your questions.