asked on

how to perform full outer join with + in oracle

hi how can i do the full outer join in this sql using +

  WITH    by_parent    AS
    (
        SELECT    obj_parent
        ,         LISTAGG (obj_child, ',') WITHIN GROUP (ORDER BY obj_child)  AS obj_children
        FROM      cal_erd_link
        GROUP BY  obj_parent
    )
    ,    by_child    AS
    (
        SELECT    obj_child
        ,         LISTAGG (obj_parent, ',') WITHIN GROUP (ORDER BY obj_parent)  AS obj_parents
        FROM      cal_erd_link
        GROUP BY  obj_child
    )
    SELECT    NVL (p.obj_parent, c.obj_child)    AS obj_name
    ,         p.obj_children
    ,         c.obj_parents
    FROM             by_child   c
    FULL OUTER JOIN  by_parent  p  ON  p.obj_parent  = c.obj_child
    ;

Open in new window

am in 11gR2

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

I agree with the above post: Use ANSI if you can.

Since you want it anyway, see if this example helps:

drop table parent purge;
create table parent (id number);

drop table child purge;
create table child (id number, parent_id number);


insert into parent values(1);
insert into parent values(2);

insert into child values(1,1);
insert into child values(2,1);
insert into child values(3,2);
commit;


select p.id,c.id
from child c full outer join parent p on c.id=p.id
/

select p.id,c.id
from child c, parent p 
where c.id=p.id(+)
/

Open in new window

PortletPaul

Without testing at all, the old equivalent for full outer join involves a UNION, along these lines:

SELECT    obj_name
,         p.obj_children
,         c.obj_parents
FROM ( 
      SELECT p.obj_parent as obj_name, p.obj_children, c.obj_parents
      FROM by_parent p, by_child c
      WHERE  p.obj_parent  = c.obj_child(+)
      UNION
      SELECT c.obj_child, p.obj_children, c.obj_parents
      FROM by_parent a, by_child b
      WHERE  p.obj_parent(+)  = c.obj_child
    )

Open in new window

Even more reason to simply use the ANSI syntax :)

Alex [***Alex140181***]

I don't allow any joins using + from anyone in my projects.

Hell YES! Get rid of those proprietary Oracle syntax ASAP ;-)
There are numerous reasons coding ANSI style, and rather none for using Oracle's internal syntax :-((

chalie001

ASKER

its correct