SQL FULL JOIN Statement
What does a SQL FULL JOIN return?
FULL JOIN returns all matching records from both tables whether the other table matches or not.
Be aware that a FULL JOIN can potentially return very large datasets.
These two: FULL JOIN and FULL OUTER JOIN are the same.

The SQL FULL JOIN syntax
The general syntax is:
SELECT column-names FROM table-name1 FULL JOIN table-name2 ON column-name1 = column-name2 WHERE condition
The general FULL OUTER JOIN syntax is:
SELECT column-names FROM table-name1 FULL OUTER JOIN table-name2 ON column-name1 = column-name2 WHERE condition
SUPPLIER |
---|
Id |
CompanyName |
ContactName |
City |
Country |
Phone |
Fax |
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
SQL FULL JOIN Examples
Problem: Match all customers
and suppliers by country
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 suppliers that have no customers in their country,
and customers that have no suppliers in their country,
and 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 |
![]() |