SQL FULL JOIN

The FULL JOIN keyword return rows when there is a match in one of the tables.

Syntax:

SELECT column_name(s) FROM tablename1  FULL JOIN tablename2 ON tablename1.column_name=tablename2.column_name

For Example,

The "Customer" table:

PId	LastName	FirstName	Address	City
1	Vaishnavi	Ola	Anna Nagar	Sandnes
2	Shajil	Tove	T Nagar	Sandnes
3	Vinoth	Kari	Shivaji St, T Nagar	Stavanger

The "Orders" table:

O_Id	OrderNo	PId
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the Customer and their orders, and all the orders with their Customer.

Query like,

SELECT Customer.Name, Customer.City, Orders.OrderNo FROM Customer FULL JOIN Orders ON Customer.PId=Orders.PId ORDER BY Customer.Name

The result-set:

LastName	City	OrderNo
Vaishnavi	Bangalore	22456
Vaishnavi	Bangalore	24562
Vinoth	Chennai	77895
Vinoth	Chennai	44678
Shajil	Chennai	34764

The FULL JOIN keyword returns all the rows from the left table (Customer), and all the rows from the right table (Orders). If there are rows in "Customer" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customer", those rows will be listed as well.