Re: Question about (+) in WHERE clause

the position of the (+) makes a big difference. it turns the query into either a right join or a left join.

by way of explanation, the (+) is put on the side of the condition which you want to accept null values from. EMPLOYEES:
FIRST_NAME LAST_NAME ADDRESS_ID

John        Doe        1
Mary        Smith      2

ADDRESS:
ADDRESS_ID STATE

1            PA
3            NJ
4            DE


SELECT    E.FIRST_NAME, A.STATE
FROM      EMPLOYEES E, ADDRESS A
WHERE     E.ADDRESS_ID (+) =A.ADDRESS_ID

--->returns
FIRST_NAME STATE
John PA
  {null} NJ
  {null} DE

SELECT E.FIRST_NAME, A.STATE

FROM      EMPLOYEES E, ADDRESS A
WHERE     E.ADDRESS_ID =A.ADDRESS_ID (+)

--->returns
FIRST_NAME STATE

John        PA
Mary        {null}

christos_tsoukalas_at_yahoo.com (Chris) wrote in message news:<1d9407f8.0111120852.5a10caa1_at_posting.google.com>...
> Hi Mike,
>
> But does the position of (+) make any difference in the query?
>
> That is, how the query:
>
> SELECT E.FIRST_NAME, A.STATE
> FROM EMPLOYEES E, ADDRESS A
> WHERE E.LAST_NAME LIKE 'j%'
> AND E.ADDRESS_ID (+) =A.ADDRESS_ID
>
> differs from the query:
>
> SELECT E.FIRST_NAME, A.STATE
> FROM EMPLOYEES E, ADDRESS A
> WHERE E.LAST_NAME LIKE 'j%'
> AND E.ADDRESS_ID =A.ADDRESS_ID (+)
>
> Thanks,
>
> Chris
>
Received on Tue Nov 13 2001 - 21:21:42 CET