SQL Alternative for 'OR' in where clause when using outer join

Question

I have the following query:

select * from
from assignments dah, employees emp 
where 
    dah.person_id=emp.person_id(+)
and 
(dah.effective_end_date between emp.date_from(+) and emp.date_to(+)
and dah.effective_end_date between emp.valid_from(+) and emp.valid_to(+))
or   
(dah.effective_start_date between emp.date_from(+) and emp.date_to(+)
and dah.effective_start_date between emp.valid_from(+) and emp.valid_to(+))

I get the following message: 'outer join operator (+) not allowed in operand of OR or IN'. I know about using 2 unions with inner joins is a solution, but I can't use it because I actually have a lot of code (code I provided is just an example).

Edit: i need this done via oracle syntax, because i work with data warehousing and our ETL doesn't fully support explicit syntax. Maybe there is something i am not seeing and this can be written differently?

Edit nr.2 : Maybe date overlapping logic can somehow be implemented without using OR and with oracle syntax?

Answer 1

Use explicit left join syntax:

select *
from employees emp left join
     assignments dah 
     on dah.person_id = emp.person_id and
        ((dah.effective_end_date between emp.date_from and emp.date_to and
          dah.effective_end_date between emp.valid_from and emp.valid_to
         ) or
         (dah.effective_start_date between emp.date_from and emp.date_to and
          dah.effective_start_date between emp.valid_from and emp.valid_to
         )
        );

A simple rule is never to use a comma in the from clause. Always use explicit join syntax.

Note: Technically, your outer join syntax would have the tables in the other order:

from assignments dah left join
     employees emp 
     on . . .

I swapped them on purpose. The left join keeps all rows in the first table, even those with no matches. The + syntax is harder to follow. The + goes on the side that would get the NULL values. However, to me, this seems less likely that the unmatched rows are in the assignments table.

If you have proper foreign key relationships, then all the assignments should have a correct person. I may not understand you data, however, and you might want to reverse your tables for what you are really trying to do.

EDIT:

As for overlaps, I would be inclined to use the simpler:

     on dah.person_id = emp.person_id and
        (dah.effective_end_date >= emp.date_from and
         dah.effective_start_date <= emp.date_to 
        )

You can even write this using the archaic + notation, if you like. Also note: these do not do exactly the same things. This will detect overlaps where one period is entirely embedded in another period.