SQL LEFT JOIN

SQL LEFT JOIN Explained

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 return columns with NULL values.

Example

#

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

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

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

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

SQL 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

The ORDER BY TotalAmount shows the customers without orders 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