Outer Join with Tibero v6

I consider a specific example where we have a parent-child relationship. For example, when we build a menu for an application, we have the individual Menu Items. However, each Menu Item will have a Parent Menu Item. At the Root Level, we will have a set of Menu Items, whose parent will be the Root Menu Item.

The same example applies where we consider the directory structure on a disk. Like in the case of the Menu, we do not know till how many levels the directories may be nested.

For either of these example, we can consider a data structure as shown below.

Here, PARENT_NODULE is also a MODULE_CODE as stored in this table.

Now, we want to write a query such that we output the MODULE_CODE, MODULE_NAME, PARENT_MODULE Code and also the Parent Module Name. For creating this output, we would need an outer join.

For this, in Tibero v6, we could write a SQL Query the same as available in Oracle as follows:

SELECT A.MODULE_CODE, A.MODULE_NAME, A.PARENT_MODULE, B.MODULE_NAME PARENT_MODULE_NAME
   FROM MODULE A, MODULE B
   WHERE A.MODULE_CODE (+) = B.PARENT_MODULE;

Alternately, we could write the SQL Query as follows:

select a.MODULE_CODE, a.PARENT_MODULE from MODULE a 
LEFT OUTER JOIN MODULE b ON (a.PARENT_MODULE = b.MODULE_CODE); 

To output all the 4 columns (Module Code, Module Name, Parent Module Code, Parent Module Name), we could write the SQL Query as follows.

select a.MODULE_CODE MODULE_CODE, a.MODULE_NAME MODULE_NAME,
       a.PARENT_MODULE PARENT_MODULE, 
       decode(a.PARENT_MODULE, NULL, '', 
              a.PARENT_MODULE, 
              (select b.MODULE_NAME from MODULE b 
               where a.PARENT_MODULE = b.MODULE_CODE)) PARENT_MODULE_NAME 
from MODULE a;

This SQL Query can be extended to fetch details of particular module as follows:

select a.MODULE_CODE MODULE_CODE, a.MODULE_NAME MODULE_NAME,
       a.PARENT_MODULE PARENT_MODULE, 
       decode(a.PARENT_MODULE, NULL, '', 
              a.PARENT_MODULE, 
              (select b.MODULE_NAME from MODULE b 
               where a.PARENT_MODULE = b.MODULE_CODE)) PARENT_MODULE_NAME 
from MODULE a
where a.MODULE_CODE = 'GENERAL';

We use these queries in Paikari for the Module Maintenance and for the Access Control Modules. A demonstration of the Module Maintenance is provided in the video.