Why table exists two times under different aliases in FROM clause of SQL query?

Question

I have this query:

 SELECT to_number(gr.code) group_index,             
       gr.NAME group_name, f.*,                         
       gr.description gr_desc                           
      FROM obj$groups gr, obj$group_objects gro,         
       obj$group_objects gro2, tdf$flex_fields f,       
       inv$requests w, inv$Direction_Criterions c       
     WHERE gr.NO = gro.object_no                        
       AND gro.group_no = obj$group_service.path_to_no(
       'Some Condition String', 
       tdf$flex_field_service.get_local_list_group_no)
       AND gro2.group_no = gr.NO                        
       AND f.NO = gro2.object_no                        
       AND w.no = 11593597                               
       AND c.direction_no = w.direction_no              
       AND f.no = c.criterion_no                        
     ORDER BY to_number(gr.code), f.name

Why are two same tables (group_objects) present here? I tried to reverse-engineer this, but couldn't myself, maybe anyone here already know of this trick?

This happens in Oracle database.

Answer 1

It's an operation called self-join. When you want to join records from the same table.

It usually happens when you have records related to records in the same table. Example:

create table tree
(
   id number primary key,
   parent_id number,
   value varchar2(100)
);

So, if you want to retrieve nodes and their parents you would do:

select c.id, c.value, p.value as parent_value
  from tree c inner join tree p on (c.parent_id = p.id)

Something similar is happening in the query you posted.

Answer 2

group_objects is being joined to groups in the clause gro2.group_no = gr.NO and to flex_fields in the clause f.NO = gro2.object_no. I suspect this covers the case where obe set of group object isn't exactly the same as the other set and this limits the rows in the two joins where one join removes a group_object that would then not be available to join to the other table.

Answer 3

It's hard to divine the original programmer's intent from this snippet, especially without a description of what the various tables hold.

However, it appears to me as if the final result of this query is supposed to report information from two different records from the group_objects table — one in which the group no matches "Some Condition String" and the other in which the group no matches a column value from the groups table. If I had to guess, it's retrieving an operation in which an item was transferred between two groups, or substituted to be used in place of an object from a second group, or something like that.

Answer 4

For illustration, the equivalent with the standard EMP table would be:

select e.ename, m.ename manager_name
from   emp e, emp m
where  m.empno = e.mgr;

Or in more modern syntax:

select e.ename, m.ename manager_name
from   emp e
join   emp m on m.empno = e.mgr;

i.e. show the names of employees with managers and the name of their managers.

The point is that the same table is used twice in the query in a different "role". It need not be a self-join, there could be another table (or more) in between like this:

select e.ename, pm.ename projmanager_name
from   emp e
join   project_assignments pa on p.empno = pa.empno
join   projects p on p.proj_id = pa.proj_id
join   emp pm on pm.empno = p.projmanager_empno;

i.e. show the names of employees assigned to projects and show the name of the project manager of that project.