Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery
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?
|Where to download and how to install sqldmo.dll||5||38|
|SQL log file keeps growing despite getting successful log backups||4||35|
|Query / Window function ?||3||18|
|TOOLS - convert T-SQL TO PL/SQL||3||9|
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
20 Experts available now in Live!