SQL LEFT JOIN

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.

SQL LEFT JOIN

Example

#

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.

Result:  78 records
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

Syntax

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.


More Examples

LEFT JOIN example

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List all customers and the total amount they spent irrespective whether they placed any orders or not.
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).

Result:  832 records
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

You may also like



Guides


vsn 3.1