title: Converting a correlated sub-query to JOIN
body: <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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 &lt;
            (
             select
                 nvl( min(s1.due_DATE), to_date  ( '31-DEC-2999', 'DD-MM-YYYY') )
                 from   SCHED s1
                 where  s1.AREA_CODE = s.AREA_CODE 
                 and s1.COMP_CODE = 'c'
            )
         )
      )
     )
  order by a.EFF_DATE asc, s.due_DATE asc
  </code></pre>
  
  <p><strong>Modified Query:</strong></p>
  
  <pre><code>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 &lt; s2.min_date
        )
      )
    )
  order by s.EFF_DATE asc, s.due_DATE asc
  </code></pre>