Join Behaviour on Duplicates & NULL

 

                                

 

Inner Join Result with Duplicate–

 

Left Outer Join with Duplicate-

 

 

 

Right Outer Join with Duplicate

 

Joins with NULL : NULL  cannot be used in Join, is we can not join anything with NULL , even NULL can not join with NULL.

             

No Change in Inner Join –

Left Outer Join –

Here in result record no 5 has NULL from t1 but another NULL is comonig as there is no match.

Right Outer Join –

Here in result record no 5 has NULL values but this null value is not the one which is stored in T1. This is because C1 column from T2 table having value 4 is not matching with any of the records in T2.

Let’s check the FULL OUTER JOIN –

 

Another Example Holding NULL in both tables –

 

Inner Join –here NULL is not shown in the result even though it is matching

 

 

This can be handled using NVL function –

Left Outer Join –

Right Outer Join –

Full Outer Join –

 

 

Now how to handle this ? Since here join has some issue and giving multiple records .

Using NVL –