SQL FULL OUTER JOIN

SQL FULL OUTER JOIN

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

FULL OUTER JOIN Syntax:

SELECT column_name(s)

FROM table1

FULL OUTER JOIN table2

ON table1.column_name = table2.column_name

WHERE condition;


Customer table:

id

name     

age 

  address
salary

1

Ramesh

32

Ahmedabad 
2000.00 

2

Khilan

25

Delhi     
1500.00 

3

Kaushik

23

Kota      
2000.00 
4Chaitali25Mumbai    
6500.00 
5Hardik27Bhopal    
8500.00 
6Komal22MP        
4500.00 
7Muffy24Indore    
10000.00 


 Orders table:

oid

  date 

customer_id

Amount

102 

 2009-10-08

3

3000 

100 

2009-10-08

3

1500 

101 

2009-11-20 

2

1560 
103 
2008-05-20
42060 


Example : 

   SELECT  ID, NAME, AMOUNT, DATE

   FROM CUSTOMERS

   FULL JOIN ORDERS

   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

The OUTPUT for the above query:

id

name

amount

date
1Ramesh   
NULL 
NULL 
2Khilan   
1560 
2009-11-20
3kaushik  
3000 
2009-10-08
3kaushik  
1500 
2009-10-08 
4Chaitali 
2060 
 2008-05-20 
5Hardik   
NULL 
NULL 
6Komal    
NULL 
NULL 
7Muffy    
NULL 
NULL 
3kaushik  
3000 
2009-10-08
3kaushik  
1500 
2009-10-08 
2Khilan   
1560 
2009-11-20
4Chaitali 
2060 
2008-05-20