Re: Help with SQL

The "+" is an outer join. Consult your manuals/references about specific usage. Basically you would use an outer join in the following situation example.

   TABLE CUSTOMER

     id                not null     number(3)
     name              not null     char(25)
     salesperson_id                 number(3)

   TABLE PERSON
     id                not null     number(3)
     name              not null     char(25)

You want to find all customer names on the customer table and their salesperson names in one report. But we have a problem. The data model allows a customer to have NO sales person by virtue of allowing null value in the salesperson_id column.

If we do the following query we would get ONLY those rows where a customer had a sales person assigned. This would be reflected by having a value in the customer.salesperson_id. What if you wanted on your report to include ALL customers regardless of whether that have an assigned salesperson!

  SELECT CUSTOMER.ID, CUSTOMER.NAME, PERSON.NAME     FROM CUSTOMER, PERSON
   WHERE CUSTOMER.SALESPERSON_ID = PERSON.ID;   ENTER the OUTER JOIN (+).

By placing the outer join symbol on one side or the other in the WHERE conditon ORACLE forces a null row for comparison purposes in the WHERE statement. In the above example modify it ie. PERSON_ID(+); Bottom line is our query will now produce a report of all customer regardless of whether they have a sales person assigned. Where to place the outer join symbol requires your knowledge of the organizations data model. Ask for the Entity Relationship (ER) Diagram.

John. Received on Fri Aug 02 1996 - 00:00:00 CEST