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
chalie001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Why would you want to? That's going backwards and most companies I know have already adopted to ANSI join syntax.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
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

0
PortletPaulfreelancerCommented:
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 :)
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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 :-((
0
chalie001Author Commented:
its correct
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.