SQL LEFT JOIN

List all suppliers and their products, including suppliers with no products.

SELECT CompanyName, ProductName
  FROM Supplier S
  LEFT JOIN Product P ON S.Id = P.SupplierId

The results show that, in fact, all suppliers do have products.

CompanyName	ProductName
Exotic Liquids	Chai
Exotic Liquids	Chang
Exotic Liquids	Aniseed Syrup
New Orleans Cajun Delights	Chef Anton's Cajun Seasoning
New Orleans Cajun Delights	Chef Anton's Gumbo Mix
New Orleans Cajun Delights	Louisiana Fiery Hot Pepper Sauce
New Orleans Cajun Delights	Louisiana Hot Spiced Okra
Grandma Kelly's Homestead	Grandma's Boysenberry Spread
Grandma Kelly's Homestead	Uncle Bob's Organic Dried Pears
Grandma Kelly's Homestead	Northwoods Cranberry Sauce

LEFT JOIN syntax.

SELECT column-names
  FROM table-name1 
  LEFT JOIN table-name2 ON column-name1 = column-name2
 WHERE condition

LEFT OUTER JOIN syntax.

SELECT column-names
  FROM table-name1 
  LEFT OUTER JOIN table-name2 ON column-name1 = column-name2
 WHERE condition

LEFT JOIN and LEFT OUTER JOIN are the same. The OUTER keyword is optional.

SELECT OrderNumber, TotalAmount, FirstName, LastName, City, Country
  FROM Customer C 
  LEFT JOIN [Order] O ON O.CustomerId = C.Id
 ORDER BY TotalAmount

Customers without orders are listed first (i.e. TotalAmount is NULL).

OrderNumber	TotalAmount	FirstName	LastName	City	Country
NULL	NULL	Diego	Roel	Madrid	Spain
NULL	NULL	Marie	Bertrand	Paris	France
542912	12.50	Patricio	Simpson	Buenos Aires	Argentina
542937	18.40	Paolo	Accorti	Torino	Italy
542897	28.00	Pascale	Cartrain	Charleroi	Belgium
542716	28.00	Maurizio	Moroni	Reggio Emilia	Italy
543028	30.00	Yvonne	Moncada	Buenos Aires	Argentina
543013	36.00	Fran	Wilson	Portland	USA

• Our Sql JOIN Tutorial
• Our Sql RIGHT JOIN Tutorial
• Our Sql INNER JOIN Tutorial
• Our Sql OUTER JOIN Tutorial