SQL: Oracle vs ANSI

[uv]

Howdy

I've recently started writing my Oracle SQL scripts in ANSI - I find it much cleaner, much easier to read, much easier to make sure joins aren't missed, allows you to use multiple outer joins against the same table and isn't proprietary. My colleagues, however, all use regular Oracle joins - and are adamant that the old-school method is faster and better.

Which do you guys use?

ANSI:

cursor  cs_jobs is
select  w.id wid,
        w.ext_sys_ref ext,
        o.name opn,
        g.name gpn,
        nvl(h.date_time,p.date_time) app,
        u.user_id||' - '||u.name usr
from    assign.worklist w
left    join assign.possibledates p      on p.worklist_id = w.id
left    join assign.users u              on u.id = w.user_id
left    join assign.scheduledetail d     on d.worklist_id = w.id
left    join assign.scheduleheader h     on h.id = d.schedule_id
left    join assign.operatives o         on o.id = h.operative_id
left    join assign.operativegroups g    on g.id = o.group_id
where   trunc(w.date_booked) = trunc(sysdate)
and     w.location_postcode like '&&outcode'||'%';

Oracle:

cursor  cs_jobs is
select  w.id wid,
        w.ext_sys_ref ext,
        o.name opn,
        g.name gpn,
        nvl(h.date_time,p.date_time) app,
        u.user_id||' - '||u.name usr
from    assign.operativegroups g,
        assign.operatives o,
        assign.scheduleheader h,
        assign.scheduledetail d,
        assign.users u,
        assign.possibledates p,
        assign.worklist w
where   trunc(w.date_booked) = trunc(sysdate)
and     w.location_postcode like '&&outcode'||'%'
and     p.worklist_id(+) = w.id
and     u.id(+) = w.user_id
and     d.worklist_id(+) = w.id
and     h.id(+) = d.schedule_id
and     o.id(+) = h.operative_id
and     g.id(+) = o.group_id;

 

[Haircut]

I thought the Oracle way died out years ago.
Unless you're running Oracle8i there's no reason do queries like that.

ANSI is much more readable and allows full outer joins.

I'm currently on a project using Oracle 11gR2 working with an Oracle ACE who tells me that ANSI queries are the done thing these days.

 

[chiggz]

I currently prefer the latter simply because I'm used to it at work! But am slowly trying to move to ansi format.. it is the easier to manage imo

 

[uv]

Unless you're running Oracle8i there's no reason do queries like that.QUOTE]

We've only recently moved from 8i to 10gR2, so I hadn't used ANSI previously (having always worked with Oracle 8i)