Re: A (+) in the where clause

George Oliver (oliver_at_io.nosc.mil) wrote:

: Reynaldo Paulo (rpaulo_at_metz.une.edu.au) wrote:
: : Does anyone out there know what the (+) in the following
: : statement means?

: : select <something>
: : into <avariable>
: : from <atable>
: : where atable.field (+) = <somevalue>

: : Many thanks.
 

: : +---------------------------------------------------------------------------+
: : |Rey V. Paulo | "I am not bound to please thee with my |
: : |University of New England | answer." |
: : |Internet: rpaulo_at_metz.une.edu.au | - Shylock in The Merchant of Venice |
: : +---------------------------------------------------------------------------+
 

: The (+) operator is used for an outer join. An outer join is used when you
: want to return rows that match some value or values in a different table and
: also include rows that don't match. So in the example above, you will get
: those rows from atable where atable.field matches <somevalue> and those rows
: where atable.field does not match <somevalue>
 

: George Oliver
: NRaD, Code 423
: oliver_at_nosc.mil

Sorry I was not paying attention. I'll attempt to get out of this hole. If <somevalue> is just a static value, it won't matter whether you have the (+) operator or not. Contrary to what I said this morning, the example above will only return those rows that atable.field matchs <somevalue>. Generaly the outer join is used when joining two or more tables together. So if you have two tables with a foreign key, you can have a case where one table is a superset of the other. With an outer join you can write a query that will select all of the rows from the superset table where the foreign keys match. In addition, an outer join will return the rows in the superset that didn't match the foreign key in the subset table. Maybe the example below will explain it:

SQL> DESC FOO

 Name                            Null?    Type
 ------------------------------- -------- ----
 TNAME                                    CHAR(30)

SQL> DESC ROO
 Name                            Null?    Type
 ------------------------------- -------- ----
 TNAME                                    CHAR(30)
 CNAME                                    CHAR(30)

SQL> SELECT FOO.TNAME, ROO.CNAME FROM FOO, ROO WHERE FOO.TNAME = ROO.TNAME;

TNAME                          CNAME
------------------------------ ------------------------------
T1                             C1
T2                             C2

SQL> SELECT FOO.TNAME, ROO.CNAME FROM FOO, ROO WHERE FOO.TNAME (+) = ROO.TNAME;

TNAME                          CNAME
------------------------------ ------------------------------
T1                             C1
T2                             C2

SQL> SELECT FOO.TNAME, ROO.CNAME FROM FOO, ROO WHERE FOO.TNAME = ROO.TNAME (+);

TNAME                          CNAME
------------------------------ ------------------------------
T1                             C1
T2                             C2

SQL> So if you use your example above, it will only return those values that match <somevalue> and you get a syntax error if you try to attach an (+) to a constant value.

Hope this is clear. Any flames I receive for the previous posting are deserved.

Geo.

George Oliver
NRaD, Code 423
oliver_at_nosc.mil Received on Tue Aug 03 1993 - 20:44:17 CEST