Oracle sql - what does (+) represent here

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(+) 

1 answer

  • answered 2017-12-06 01:39 Used_By_Already

    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)