SQL LEFT JOIN

What is a LEFT JOIN in SQL?

  • A LEFT JOIN performs a join starting with the first (left-most) table.
  • Then, any matched records from the second table (right-most) will be included
  • LEFT JOIN and LEFT OUTER JOIN are the same.

The SQL LEFT JOIN syntax

The general LEFT JOIN syntax is

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

The general LEFT OUTER JOIN syntax is

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

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone

SQL LEFT JOIN Example

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

Note: The ORDER BY TotalAmount shows the customers without orders first (i.e. TotalMount 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


Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas