CODEWITHSUNDEEP
sqloracle-databasejoinleft-join
faujong
faujong

Reputation: 1127

How to do Oracle (+) left join query for multiple columns

We are using Oracle 11. When doing LEFT JOIN like below, the result of the query is 11,053 rows

…LEFT JOIN LIQ ON LIQ.ID = SEC.ID AND LIQ.UPD_DT  >= '08-JAN-19'
WHERE …

But, when doing LEFT JOIN using (+) like below, the result of the query is 10,136 rows

...FROM SEC, LIQ
WHERE LIQ.ID (+)= SEC.ID AND LIQ.UPD_DT  >= '08-JAN-19'

How can I use (+) with multiple columns ?

Thank you.

Upvotes: 0

Views: 5205

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

The short answer is to just include the (+) for both terms:

WHERE LIQ.ID (+) = SEC.ID AND LIQ.UPD_DT (+) >= '08-JAN-19'

although you appear to be comparing a date with a string, relying on implicit conversion and NLS settings; if so convert it or use a date literal:

WHERE LIQ.ID (+) = SEC.ID AND LIQ.UPD_DT (+) >= DATE '2019-01-08'

But changing from 'modern' (post-1992) ANSI join syntax to Oracle's ancient and proprietary syntax seems like a very odd thing to want to be doing. Even Oracle recommend you use don't use their syntax:

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:
...

(although there are still a lot of code examples in their docs which do, unhelpfully...)

There used to be some bugs around ANSI syntax, and there may still be rare cases where you see different behaviour or can't use the new syntax; but it's unlikely, and if you have a query that works there doesn't seem to be any advantage in regressing to the old ways.

Upvotes: 3

Related Questions