SQL FULL JOIN

List all customers and their order dates, including those without orders.

SELECT C.FirstName, C.LastName, O.OrderDate
  FROM Customer C
  FULL JOIN [Order] O ON C.Id = O.CustomerId
 ORDER BY O.OrderDate 

FirstName	LastName	OrderDate
Diego	Roel	NULL
Marie	Bertrand	NULL
Paul	Henriot	7/4/2012 12:00:00 AM
Karin	Josephs	7/5/2012 12:00:00 AM
Mario	Pontes	7/8/2012 12:00:00 AM
Mary	Saveley	7/8/2012 12:00:00 AM

The results include all customers of which 2 have not placed orders.
Note: FULL JOIN result sets can potentially be very large

FULL JOIN syntax.

SELECT column-names
  FROM table-name1 
  FULL 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

FULL JOIN and FULL OUTER JOIN are the same. The OUTER keyword is optional.

SELECT C.FirstName, C.LastName, C.Country AS CustomerCountry, 
       S.Country AS SupplierCountry, S.CompanyName
  FROM Customer C 
  FULL 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 JOIN Tutorial
• Our Sql LEFT JOIN Tutorial
• Our Sql RIGHT JOIN Tutorial