FULL OUTER JOIN

If you want to combine the results of both LEFT and RIGHT join than we use FULL JOIN keyword. The joined table which created after FULL JOIN contains all records from both the two tables, and fills null value for those places where missing matches take place.

Syntax: Below is the syntax of FULL JOIN :-

SELECT columnname 
FROM tablename1
FULL JOIN tablename2
ON tablename1.commonname = tablename2.commonname;

example:- Below is the customers table for explain FULL JOIN:-

  CUSTOMER-ID   NAME      AGE     ADDRESS       SALARY   
  001           suresh    23      rishikesh      20000
  002           pankaj    28      ranikhet       19999
  003           ramesh    22      punjab         10000
  004           ram       23      delhi          10000

Below is ORDERS table :-

ORDER-Id      DATE             CUSTOMER-ID   AMOUNT
 102         1-9-2015            003          5000 
 100         4-9-2015            002          1500 
 101         5-9-2015            001           760 
 103         10-9-2015           003          2090 

Now, join these two tables using FULL JOIN

 SELECT  CUSTOMER-ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     FULL JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER-ID;

This would produce the following result:

CUSTOMER-ID         NAME     AMOUNT  DATE                

001               suresh      760      5-9-2015
002               pankaj      1500     4-9-2015
003               ramesh      5000     1-9-2015
003               ramesh      2090     10-9-2015
004               ram         NULL     NULL