Re: What does the (+) mean?

Hi

For full outer join use union in 8i and below

SQL> create table a
  2 (x int)
  3
SQL> / Table created.

SQL> insert into a
  2 values(1);

1 row created.

SQL> insert into a
  2 values(2);

1 row created.

SQL> create table b
  2 (x int);

Table created.

SQL> insert into b
  2 values(2);

1 row created.

SQL> insert into b
  2 values(3);

1 row created.

SQL> select a.x,b.x
  2 from a,b
  3 where a.x=b.x(+);

         X X
---------- ----------

         1
         2          2

  1 select a.x,b.x
  2 from a,b
  3 where a.x=b.x(+)
  4 union
  5 select a.x,b.x
  6 from a,b
  7* where b.x=a.x(+)
SQL> /          X X
---------- ----------

         1
         2          2
                    3

in 9i and above use the ANSI sql syntax

SQL> select a.x,b.x
  2 from a full outer join b
  3 on a.x=b.x
  4
SQL> /          X X
---------- ----------

         2          2
         1
                    3

regards
Hrishy

Galen Boyer <galenboyer_at_hotpop.com> wrote in message news:<uekt5j6bz.fsf_at_standardandpoors.com>...
> On Mon, 9 Feb 2004, thief_NOSPAM_at_hotmail.com wrote:
> > Sorry to ask a newbie-type question, but isn't a full outer
> > join like:
> >
> > SELECT * FROM CLR, SEASON;
>
> No, your query is a cartesian product.
>
> TableA
> 1
> 2
>
>
> TableB
> 2
> 3
>
>
> Cartesian product of A and B brings back
> 1 2
> 1 3
> 2 2
> 2 3
>
> Full outer join brings back
>
> 1 NULL
> 2 2
> NULL 3
Received on Mon Feb 09 2004 - 00:15:31 CST