A left outer join keeps every row from the first table named in the query and adds matching data from the second table. When no match exists, right-table columns contain null values. Think of a spreadsheet lookup that preserves the complete first list, even when some entries have no match. section 9.2.1
Show every order and any promotion
SELECT oh.OrderID, p.PromotionCode FROM orderheader oh LEFT OUTER JOIN promotion p ON oh.PromotionID = p.PromotionID; Here, orderheader is the left table because it appears first. Every order appears, while orders without a promotion show null for PromotionCode. Add WHERE oh.OrderID <= 1008 to limit the orders without changing that left-join behavior. section 9.2.1
Use left joins carefully in nearby tasks:
- Keep a filter on the right table in the ON condition when unmatched left rows must remain. A right-table condition in WHERE removes null matches and effectively produces inner-join results. section 9.2.1
- Rewrite a right join by swapping the table order and using a left join; LEFT JOIN is the shorter equivalent of LEFT OUTER JOIN. section 9.2.4
- Apply the pattern to orders with optional promotions, customers without orders, or customers and their qualifying orders. section 9.4
SELECT
oh.OrderID,
p.PromotionCode
FROM orderheader oh
LEFT OUTER JOIN promotion p
ON oh.PromotionID = p.PromotionID;
A complete query makes the preserved table, match condition, and returned columns easy to inspect. section 9.2.1
