1

I understand that (+) can be used to represent an outer join in oracle sql. What does the (+) in this code mean? 

where sysdate between start_date(+) and end_date(+)
lostinOracle
  • 408
  • 1
  • 4
  • 14
  • It means that the writer of the query is using archaic JOIN syntax and should learn how to use proper, explicit JOINs, particularly for outer joins. – Gordon Linoff Dec 06 '17 at 01:41
  • How can it be archaic if it's still supported by Oracle? (joke) But it is proprietary and non-standard, and there's no reason for using it because Oracle has now supported standard OUTER JOIN syntax for many years. – Bill Karwin Dec 06 '17 at 01:49
  • The guy I learned from just left my company and he was very fond of using +'s. Really appreciate this community's help. I don't believe this is a duplicate question and having this as a standalone question might help other newbies like me looking for a solution. – lostinOracle Dec 06 '17 at 01:57
  • @BillKarwin, there is (at least) one exception: When you like to create MATERIALIZED VIEW with FAST REFRESH then you have to use the old Oracle style for join. Oracle considers this as "lack of documentation" - not as a bug! I don't know whether this behavior changed in most recent Oracle release (i.e. 12.2) – Wernfried Domscheit Dec 06 '17 at 07:09
  • @WernfriedDomscheit, I continue to be disappointed by Oracle. – Bill Karwin Dec 06 '17 at 16:38

1 Answers1

2

It allows for an outer join to "survive" the where clause conditions. i.e. it also allows NULLs to be returned. e.g. below 

select *
from from_table ft, outer_table ot
where sysdate between ot.start_date(+) and ot.end_date(+) 
and ft.id = ot.ft_fk(+)

an equivalent for that could be:

select *
from from_table ft
left join outer_table ot on ft.id = ot.ft_fk and sysdate between ot.start_date and ot.end_date

or, an equivalent for that could be:

select *
from from_table ft
left join outer_table ot on ft.id = ot.ft_fk
where (sysdate between ot.start_date and ot.end_date OR ot.start_date IS NULL)
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51