SQL alternative to "OR" in the where clause when using an 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: "the outer join operator (+) is not allowed on the OR or IN operand." I know that using two unions with internal joins is a solution, but I cannot use it because I actually have a lot of code (the code I provided is just an example).

Edit: I need to do this through oracle syntax because I work with a data warehouse and our ETL does not fully support explicit syntax. Maybe I don’t see something, and it can be written differently?

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

Answer 1

Since you need to use the old-style outer join syntax, here is one way (simplified since you did not provide us with sample data and / or table creation scripts):

 with assignments as (select 1 assignment_id, 1 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual union all select 2 assignment_id, 1 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('04/08/2015', 'dd/mm/yyyy') end_date from dual union all select 3 assignment_id, 1 person_id, to_date('06/08/2015', 'dd/mm/yyyy') start_date, to_date('10/08/2015', 'dd/mm/yyyy') end_date from dual union all select 4 assignment_id, 2 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual), employees as (select 1 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual union all select 3 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual) select * from assignments dah, employees emp where dah.person_id = emp.person_id (+) and dah.start_date <= emp.end_date (+) and dah.end_date >= emp.start_date (+); ASSIGNMENT_ID PERSON_ID START_DATE END_DATE PERSON_ID_1 START_DATE_1 END_DATE_1 ------------- ---------- ---------- ---------- ----------- ------------ ---------- 2 1 02/08/2015 04/08/2015 1 01/08/2015 03/08/2015 1 1 01/08/2015 03/08/2015 1 01/08/2015 03/08/2015 3 1 06/08/2015 10/08/2015 4 2 02/08/2015 03/08/2015 

Are you sure you got your outer join in the right direction? Are you sure that you are not actually using the following:

 with assignments as (select 1 assignment_id, 1 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual union all select 2 assignment_id, 1 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('04/08/2015', 'dd/mm/yyyy') end_date from dual union all select 3 assignment_id, 1 person_id, to_date('06/08/2015', 'dd/mm/yyyy') start_date, to_date('10/08/2015', 'dd/mm/yyyy') end_date from dual union all select 4 assignment_id, 2 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual), employees as (select 1 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual union all select 3 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual) select * from assignments dah, employees emp where dah.person_id (+) = emp.person_id and dah.start_date (+) <= emp.end_date and dah.end_date (+) >= emp.start_date; ASSIGNMENT_ID PERSON_ID START_DATE END_DATE PERSON_ID_1 START_DATE_1 END_DATE_1 ------------- ---------- ---------- ---------- ----------- ------------ ---------- 1 1 01/08/2015 03/08/2015 1 01/08/2015 03/08/2015 2 1 02/08/2015 04/08/2015 1 01/08/2015 03/08/2015 3 01/08/2015 03/08/2015 

Answer 2

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 a from clause. Always use explicit join syntax.

Note. Technically, your outer join syntax will have tables in a different order:

 from assignments dah left join employees emp on . . . 

I changed them on purpose. left join stores all rows in the first table, even those that have no matches. Syntax + harder to execute. + goes to the side that will receive NULL values. However, for me it seems less likely that the unsurpassed rows are in the assignment table.

If you have the right relationship with the foreign key, all tasks must have the right person. However, I may not understand your data, and you can change your tables to what you are really trying to do.

EDIT:

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

  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 archaic + notation if you want. Also note: they do not do exactly the same. This will detect overlap when one period is fully rooted in another period.

Answer 3

It should work if you translate the deprecated external union operator ( (+) ) into an explicit external connection:

 SELECT * FROM assignments dah LEFT OUTER JOIN employees emp 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) ) 

Answer 4

Please use this if you cannot use ANSI LEFT OUTER JOIN Syntax:

First: in your request there is no bracket - the second, in addition to the initial JOIN, you can rewrite x between min(+) AND max(+) as (min is NULL OR x >= min) AND (max is NULL OR x <= max)

 SELECT * FROM assignments dah, employees emp WHERE dah.person_id = emp.person_id(+) AND ( (emp.date_from IS NULL OR dah.effective_start_date >= emp.date_from) AND (emp.date_to IS NULL OR dah.effective_start_date <= emp.date_to) AND (emp.valid_from IS NULL OR dah.effective_start_date >= emp.valid_from) AND (emp.valid_to IS NULL OR dah.effective_start_date <= emp.valid_to) OR (emp.date_from IS NULL OR dah.effective_end_date >= emp.date_from) AND (emp.date_to IS NULL OR dah.effective_end_date <= emp.date_to) AND (emp.valid_from IS NULL OR dah.effective_end_date >= emp.valid_from) AND (emp.valid_to IS NULL OR dah.effective_end_date <= emp.valid_to) ) 

I think this selects what you want - a left join with all rows where start_date or end_date is between two dates.

You want all rows to be either the result of a LEFT JOIN with identifiers, or with a right date or rows with only an end date, without any identifier to WHERE ( id1=id2(+) AND ...) OR ( ... ) ... Your query was essentially the following: WHERE ( id1=id2(+) AND ...) OR ( ... ) because AND is a stronger binding than OR.

If you know emp.date_from and emp.date_to are valid or NULL

So, if there is never a case where only date_from is NULL but date_to is valid, you can significantly reduce the report:

 SELECT * FROM assignments dah, employees emp WHERE dah.person_id = emp.person_id(+) AND ( emp.date_from IS NULL 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 OR 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 ) 

Answer 5

You need to use an explicit LEFT JOIN as follows:

 SELECT * FROM assignments dah LEFT JOIN employees emp 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)