- FULL JOIN returns all matching records from both tables whether the other table matches or not.
- FULL JOIN can potentially return very large datasets.
- 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
SQL FULL JOIN Examples
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 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.
Results: 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 |
Touche. Outstanding arguments. Keep up the
amazing work.
I’m not sure where you are getting your info, but
great topic. I needs to spend some time learning
more or understanding more. Thanks for wonderful info
I was looking for this information for my mission.
Cbd oil that works 2020
Having read this I believed it was extremely informative.
I appreciate you taking the time and energy to put this short
article together. I once again find myself personally spending a lot of time both reading and posting comments.
But so what, it was still worthwhile! best rated cbd oil http://j.mp/3drY6lE cbd oil that works 2020
http://j.mp/3drY6lE
Hi there! I know this is somewhat off-topic however I had to ask.
Does operating a well-established website such as yours require a massive amount work?
I’m brand new to operating a blog but I do write in my journal on a daily basis.
I’d like to start a blog so I can easily share my own experience and feelings online.
Please let me know if you have any kind of suggestions
or tips for brand new aspiring blog owners. Appreciate
it!
I all the time used to read article in news papers but now as I am a user of net
therefore from now I am using net for articles or reviews, thanks to web.
No matter if some one searches for his required thing, thus he/she wishes to be available that in detail, thus that thing
is maintained over here.