SQL FULL JOIN

SQL FULL JOIN Explained

A FULL JOIN returns all records from both tables.

This includes records that do not match.

Non-matching rows return columns with NULL values.

A FULL JOIN can potentially return large datasets.

Example

#

Problem: 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 

The results include all customers of which 2 have not placed orders.

Syntax

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.


More Examples

SQL FULL JOIN 2 tables

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: Match all customers and suppliers by country.
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.

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

You may also like



Guides