SQL FULL JOIN Statement

  • 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 definitive guide
for data professionals
Order today! 
  See 2 min video

The SQL FULL JOIN syntax

The general syntax is:

  1. SELECT columnnames
  2. FROM tablename1 FULL JOIN tablename2
  3. ON columnname1 = columnname2
  4. WHERE condition

The general FULL OUTER JOIN syntax is:

  1. SELECT columnnames
  2. FROM tablename1 FULL OUTER JOIN tablename2
  3. ON columnname1 = columnname2
  4. WHERE condition

SQL FULL JOIN Examples

Problem: Match all customers and suppliers by country

  1. SELECT C.FirstName, C.LastName, C.Country AS CustomerCountry,
  2. S.Country AS SupplierCountry, S.CompanyName
  3. FROM Customer C FULL JOIN Supplier S
  4. ON C.Country = S.Country
  5. 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

“SQL FULL JOIN Statement”의 6개의 댓글

  1. 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!

댓글 남기기