SQL OUTER JOIN

List all products and their total sales, including those that did not sell.

SELECT ProductName, SUM(I.UnitPrice) AS Total
  FROM Product P
  LEFT OUTER JOIN OrderItem I ON P.Id = I.ProductId
 GROUP BY ProductName
 ORDER BY SUM(I.UnitPrice)

Stroopwafels are the only product that did not sell.

ProductName	Total
Stroopwafels	NULL
Chocolade	71.40
Geitost	74.50
Genen Shouyu	86.80
Longlife Tofu	114.00
Aniseed Syrup	114.00
Louisiana Hot Spiced Okra	122.00

LEFT OUTER JOIN syntax.

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

RIGHT OUTER JOIN syntax.

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

FULL OUTER JOIN syntax.

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

The OUTER keyword is optional with any of the LEFT, RIGHT, and FULL JOINs.

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

Two customers did not place any orders.

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

SELECT FirstName, LastName, City, Country, TotalAmount
  FROM [Order] O 
 RIGHT OUTER JOIN Customer C ON O.CustomerId = C.Id
 WHERE TotalAmount IS NULL

This returns customers who, when joined, have no matching orders.

SELECT C.FirstName, C.LastName, C.Country AS CustomerCountry, 
       S.Country AS SupplierCountry, S.CompanyName
  FROM Customer C 
  FULL OUTER JOIN Supplier S ON C.Country = S.Country
 ORDER BY C.Country, S.Country

This returns all possible combinations of suppliers and customers. This includes suppliers without customers in their country, and customers without suppliers in their country, as well as customers and suppliers that are from the same country.

FirstName	LastName	CustomerCountry	SupplierCountry	CompanyName
NULL	NULL	NULL	Australia	Pavlova, Ltd.
NULL	NULL	NULL	Australia	G'day, Mate
NULL	NULL	NULL	Japan	Tokyo Traders
NULL	NULL	NULL	Japan	Mayumi's
NULL	NULL	NULL	Netherlands	Zaanse Snoepfabriek
NULL	NULL	NULL	Singapore	Leka Trading
Patricio	Simpson	Argentina	NULL	NULL
Yvonne	Moncada	Argentina	NULL	NULL
Sergio	Gutiérrez	Argentina	NULL	NULL
Georg	Pipps	Austria	NULL	NULL
Roland	Mendel	Austria	NULL	NULL
Pascale	Cartrain	Belgium	NULL	NULL
Catherine	Dewey	Belgium	NULL	NULL
Bernardo	Batista	Brazil	Brazil	Refrescos Americanas LTDA
Lúcia	Carvalho	Brazil	Brazil	Refrescos Americanas LTDA
Janete	Limeira	Brazil	Brazil	Refrescos Americanas LTDA
Aria	Cruz	Brazil	Brazil	Refrescos Americanas LTDA
André	Fonseca	Brazil	Brazil	Refrescos Americanas LTDA
Mario	Pontes	Brazil	Brazil	Refrescos Americanas LTDA
Pedro	Afonso	Brazil	Brazil	Refrescos Americanas LTDA
Paula	Parente	Brazil	Brazil	Refrescos Americanas LTDA
Anabela	Domingues	Brazil	Brazil	Refrescos Americanas LTDA
Elizabeth	Lincoln	Canada	Canada	Ma Maison
Elizabeth	Lincoln	Canada	Canada	Forêts d'érables
Yoshi	Tannamuri	Canada	Canada	Ma Maison
Yoshi	Tannamuri	Canada	Canada	Forêts d'érables
Jean	Fresnière	Canada	Canada	Ma Maison

• Our Sql LEFT JOIN Tutorial
• Our Sql RIGHT JOIN Tutorial
• Our Sql INNER JOIN Tutorial