In SQL, a LEFT JOIN performs a join starting with the left table.
Then, any matching records from the right table will be included.
Rows without a match will have NULL column values.
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.
ORDER |
---|
Id |
OrderDate |
OrderNumber |
CustomerId |
TotalAmount |
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
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 |