Converting a correlated sub-query to JOIN
===


I have a correlated subquery which is creating performance issues. Being a correlated subquery it doesn't accept index hints either. So I am trying to convert it into a JOIN instead. Please find below the original query and the modified query. The modified query doesn't return any row but the original returns me 224 rows.

Appreciate any insight on what is wrong with my JOIN query, if it even makes sense to use JOIN instead of subquery in this case. Thanks.

    select Area_CODE,
           due_DATE,
           RATE,
       
    from SCHED S
    where (s.Area_CODE = 11001 and 
       (s.COMP_CODE = 'a' 
       or
          (s.COMPANY_CODE = 'b' 
              and s.due_DATE <
              (
               select
                   nvl( min(s1.due_DATE), to_date  ( '31-DEC-2999', 'DD-MM-YYYY') )
                   from   SCHED s1
                   where  s1.AREA_CODE = s1.AREA_CODE 
                   and s1.COMP_CODE = 'c'
              )
           )
        )
       )
    order by a.EFF_DATE asc, s.due_DATE asc


**Modified Query:**


SELECT
  Area_CODE,
       due_DATE,
       RATE
      
from SCHED S
LEFT JOIN
  (
    SELECT
      NVL( MIN(s1.due_DATE), to_date ( '31-DEC-2999', 'DD-MM-YYYY') ) AS
      min_date,
      s1.AREA_CODE AS a_code
    FROM
      SCHED s1
    WHERE
      s1.COMPANY_CODE = 'c'
    GROUP BY
      s1.AREA_code
  )
  s2
ON
  s2.A_CODE = s.area_code
WHERE
  (
    s.area_code = 11001
  AND
    (
      s.COMP_CODE = 'a'
    OR
      (
        s.COMP_CODE = 'b' 
      and s.due_DATE < R2.min_date
      )
    )
  )
order by s.EFF_DATE asc, s.due_DATE asc