Re: LEFT JOIN in ORACLE

Florian Marinoiu wrote:

> Hi,
>
> I am quite new to ORACLE and after some years of work with MS SQL I am
> trying to write some queries for an ORACLE server.
>
> My queries are for the version 8.1.7.
>
> I took the example of one of my old SQL selects:
> The SQL Server version
>
> SELECT A.tsUpdate, A.id, A.Code, AD.Name, AD.Description
> FROM Application A left outer join ApplicationDescription AD
> on A.id = AD.idApplication
> WHERE A.Code = @pCode AND
> ISNULL(AD.Name,'') = ISNULL(ISNULL(@pName, AD.Name),'') AND
> ISNULL(AD.Description,'') = ISNULL(ISNULL(@pDescription,
> AD.Description), '')
>
> This select returned all the lignes from the A table and the
> corresponding lines(if they existed form the AD table). Besides, as it
> was a code that was running in a search st proc if I put something in
> the @pName or @pDescription parameters the code was returning only the
> lines that had the text in AD.Code or AD.Description but if I put
> nothing in thses params the code made only thge left join.
>
> Now the ORACLE code:
> SELECT A.tsUpdate, A.id, A.Code, AD.Name, AD.Description
> FROM Application A, ApplicationDescription AD
> WHERE A.id = AD.idApplication(+)
> AND Code = NVL(pCode, Code)
> AND NVL(Name,'') = NVL(pName,'')
> AND Description = NVL(pDescription, Description)
>
> It seems that running this code returns the same result as the inner
> join as I get no lines where there is no corresponding line in the AD
> table.
>
> Could someone please tell me what am I doing wrong.
>
> Also is there a correspondent to the SQL Server Books Online. I looked
> in the program folders of ORACLE and I do not seem to have any
> documentation installed on the PC.
> Let's say I want the complete structure of a SELECT or UPDATE with all
> the possibilities that exists for these commands. Where could I find
> them?
>
> Thanks,
> Florian

FOrget about 8i, install 9i (which, by the way, supports your left joins...)

-- 
Regards, Frank van Bortel