FULL JOIN is used to retrieve all the matching records plus the unmatched records from both the tables joined.
You can say that it gives the result of both LEFT JOIN and Right JOIN.
SQL FULL JOIN Syntax
SELECT column_name1, column_name2 ...
FROM table1
FULL JOIN table2
ON table1.column_name=table2.column_name
Below is shown the "Customers" table:
C001 |
Fredrick Johnson |
FJohnson@yahoo.com |
Berkeley Gardens 12 Brewery |
London |
WX1 6LT |
UK |
C002 |
Mary Davidson |
Mary@gmail.com |
35 King George |
Madrid |
28023 |
Spain |
C003 |
George Clooney |
George@hotmail.com |
Obere Str. 57 |
Berlin |
12209 |
Germany |
C004 |
David Louis |
David@mail.com |
2743 Bering St. |
NewJersy |
07019 |
USA |
C005 |
Johnny Anderson |
Johnny@gmail.com |
55 Grizzly Peak Rd. |
NewJersy |
07019 |
USA |
C006 |
Paul Henriot |
PaulHenriot@yahoo.com |
59 rue de l'Abbaye |
Paris |
51100 |
France |
C007 |
Henry King |
Henry@mail.com |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
C008 |
Robert John |
Robert@gmail.com |
87 Polk St. Suite 5 |
San Francisco |
94117 |
USA |
In the Customers table, "CustomerID" is Primary Key with unique values. You can see that the "CustomerID" column values does not repeat.
Below is shown the "Orders" table:
D001 |
C001 |
4/3/2007 12:00:00 AM |
150.23 |
D002 |
C003 |
1/22/2005 12:00:00 AM |
123.57 |
D003 |
C005 |
3/19/2003 12:00:00 AM |
220.43 |
D004 |
C007 |
7/21/2008 12:00:00 AM |
157.72 |
D005 |
C008 |
2/28/2009 12:00:00 AM |
223.78 |
D006 |
C011 |
3/21/2005 12:00:00 AM |
245.34 |
In the Orders table, the "OrderID" is the Primary Key. Here you can see that "CustomerID" column is the Foreign Key.
The Customers and the Orders tables are related to each other using the "CustomerID" column.
SQL Full JOIN Example
To retrieve all the matching records from Orders table and Customers table and also the unmatched records from both the tables.
Text Editor below has the SQL Query, click the "View Result" button, to see the result.
Select Customers.CustomerID, Customers.CustomerName, Customers.EmailID, Orders.OrderID,
Orders.Date, Orders.Amount
From Customers
Full JOIN Orders on Customers.CustomerID = Orders.CustomerID
Order By Orders.OrderID desc
Note: UnMatched records are filled with NULL values.
SQL in Text Editor: