Oracle outer join not working as expected

Question

I have the following query:

select *
from   per_all_assignments_f paaf,
       pay_all_payrolls_f    payr
where  sysdate between paaf.effective_start_date and paaf.effective_end_date
and    paaf.assignment_type in ('E', 'C')
and    paaf.primary_flag = 'Y'
and    payr.payroll_id (+) = paaf.payroll_id
and    nvl(payr.attribute1 (+), '$XXX$') in ('TINT')
and    paaf.effective_start_date between nvl(payr.effective_start_date (+), to_date('01/01/1000', 'dd/mm/yyyy')) 
                                     and nvl(payr.effective_end_date   (+), to_date('31/12/4712', 'dd/mm/yyyy'))

I'm expecting to get back 7 employee assignments due to the fact that there are 7 on the TINT payroll. However it returns 10035 rows with 7 having details for the payroll table and the other ones having blank data. So the outer join is not working as I expected. What am I doing wrong?

Oracle recommends to not use `(+)` operator any longer. You should really get used to explicit `JOIN` and `OUTER JOIN` syntax rather than implicit joins in the `WHERE` clause — , Aug 05 '13 at 16:29

score 1 · Answer 1 · answered Aug 05 '13 at 14:54

Try writing this with standard join syntax, moving the conditions on the second table into the on clause:

select *
from   per_all_assignments_f paaf left outer
       pay_all_payrolls_f    payr
       on payr.payroll_id = paaf.payroll_id and
          nvl(payr.attribute1, '$XXX$') in ('TINT') and
          paaf.effective_start_date between nvl(payr.effective_start_date,
                                                to_date('01/01/1000', 'dd/mm/yyyy')) 
                                     and nvl(payr.effective_end_date,
                                             to_date('31/12/4712', 'dd/mm/yyyy'))
where paaf.assignment_type in ('E', 'C') and
      paaf.primary_flag = 'Y' and
      sysdate between paaf.effective_start_date and paaf.effective_end_date;

Bob Jarvis - Слава Україні · Accepted Answer · 2013-08-06T10:57:20.247

It sounds like what you really want is an INNER join. Drop the (+)'s and see if you get back what you're looking for. In addition, @GordonLinoff makes an excellent suggestion - get comfortable with using the ANSI join syntax, which is both more expressive and more comprehensible than the old-style "put all the conditions in the WHERE clause and then try to puzzle it out" style.

Here's how I'd rewrite this query using the ANSI-standard join syntax:

SELECT *
  FROM PER_ALL_ASSIGNMENTS_F paaf
  INNER JOIN PAY_ALL_PAYROLLS_F payr
    ON payr.PAYROLL_ID = paaf.PAYROLL_ID
  WHERE SYSDATE BETWEEN paaf.EFFECTIVE_START_DATE
                    AND paaf.EFFECTIVE_END_DATE AND
        paaf.ASSIGNMENT_TYPE IN ('E', 'C') AND
        paaf.PRIMARY_FLAG = 'Y' AND
        payr.ATTRIBUTE1 = 'TINT' AND
        paaf.EFFECTIVE_START_DATE BETWEEN NVL(payr.EFFECTIVE_START_DATE, TO_DATE('01/01/1000', 'DD/MM/YYYY')) 
                                      AND NVL(payr.EFFECTIVE_END_DATE, TO_DATE('31/12/4712', 'DD/MM/YYYY'))

One other minor matter. I notice you're using BETWEEN with date values which can potentially be problematic. Let's say, for instance, the you've got an EFFECTIVE_START_DATE of 01-JAN-2013 and an EFFECTIVE_END_DATE of 30-JUN-2013 on some row in PER_ALL_ASSIGNMENTS_F, and let's further say that the current date and time are 30-JUN-2013 at 07:02:04 AM. Given these data values, this row from PER_ALL_ASSIGNMENTS_F will NOT be selected, although you might expect it to be. The issue is that when the only fields filled in on a DATE value are the day, month, and year, then the hour, minutes, and seconds default to zero - thus the end date is really 30-JUN-2013 at 00:00:00, which is prior to the current date/time of 30-JUN-2013 at 07:02:04 AM, and consequently the date comparison causes the row to be ignored. I don't know how the EFFECTIVE_END_DATE fields are populated in your database - hopefully they're completely filled in, e.g. 30-JUN-2013 23:59:59 - but if not you might need to make your date comparison something like

TRUNC(SYSDATE) BETWEEN paaf.EFFECTIVE_START_DATE
                   AND paaf.EFFECTIVE_END_DATE

or

SYSDATE BETWEEN paaf.EFFECTIVE_START_DATE
            AND paaf.EFFECTIVE_END_DATE + INTERVAL '1' DAY - INTERVAL '1' SECOND

(The former is probably a better choice, as the latter form will preclude the use of any non-function-based index which may exist on exist on (EFFECTIVE_START_DATE, EFFECTIVE_END_DATE).

Share and enjoy.

Oracle outer join not working as expected

2 Answers2

Linked