Re: Going mad - outer join problem

From: ddf <oratune_at_msn.com>
Date: Mon, 20 Oct 2008 07:41:40 -0700 (PDT)
Message-ID: <7d10d894-4570-48cb-a331-564d03aee12a@b31g2000prf.googlegroups.com>

On Oct 20, 4:08 am, Jeremy <jeremy0..._at_gmail.com> wrote:
> In article <48FB79A2.7010..._at_gmail.com>, mdeme..._at_gmail.com says...>
>
> > select *
> > from   a,
> >         b
> > where  a.id = b.id (+)
> > and    nvl(b.code(+),'TYPE') = 'TYPE'
> > order by a.id;
>
> is the right answer. Thanks and also to David and Serge for their help.
>
> When I was taught SQL by oracle I don't recall there ever being
> references to e.g. "LEFT OUTER JOIN" - it was all the (+) notation.
>
> I guess they are methods to achieve the same objective but what are the
> pros and cons?
>
> --
> jeremy

The syntax has changed over the years, and ANSI join syntax is available in later releases of Oracle. The problem here is that there are records in table B for id 2; if we create a situation where no such records exist the outer join on the id columns works:

SQL> create table A

  2  (        id    number,
  3           name  varchar2(10));

Table created.

SQL>
SQL>
SQL> create table B
  2  (        id        number,
  3           code      varchar2(10),
  4           descrip   varchar2(10));

Table created.

SQL>
SQL>
SQL> insert all
  2  into a values (1,'ITEM1')
  3  into a values (2,'ITEM2')
  4  into a values (3,'ITEM3')
  5  into b values (1,'LOC','San Mateo')
  6  into b values (1,'TYPE','PUBLIC')
  7  into b values (1,'POINTS','17')

  8 into b values (2,'LOC','Burlingame')   9 into b values (2,'POINTS','14')
 10 select * from dual;

8 rows created.

SQL>
SQL>
SQL> select *

  2 from a left outer join (select * from b where code = 'TYPE') b on (b.id = a.id)
  3 order by a.id;

        ID NAME ID CODE DESCRIP ---------- ---------- ---------- ---------- ----------

         1 ITEM1               1 TYPE       PUBLIC
         2 ITEM2
         3 ITEM3

SQL> David Fitzjarrell Received on Mon Oct 20 2008 - 09:41:40 CDT