A join condition, also called a join predicate, is the Boolean condition that tells SQL how rows from two joined tables correspond. It usually appears after ON, such as matching each order to its customer with oh.CustomerID = c.CustomerID. section 8.6
Match orders to a particular customer
SELECT oh.OrderID FROM orderheader oh JOIN customer c ON oh.CustomerID = c.CustomerID WHERE c.FirstName = 'Chris' AND c.LastName = 'Dixon'; The join condition connects each order to its customer. The WHERE condition then keeps only Chris Dixon’s rows. With several tables, each JOIN can have its own condition—for example, connecting order headers to order items and titles through OrderID and TitleID. section 8.6
With an outer join, place a condition carefully. Filtering a right-table value in WHERE can remove unmatched rows and make a LEFT OUTER JOIN behave like an INNER JOIN. Put the right-table filter in ON when you want every left-table row retained, with either the matching value or NULL. section 9.2.1
SELECT
oh.OrderID,
p.PromotionCode
FROM orderheader oh
LEFT OUTER JOIN promotion p
ON oh.PromotionID = p.PromotionID
AND p.PromotionCode = '2OFF2015';
See the join condition that retains every order while restricting the promotion match to 2OFF2015. section 9.2.1
