title: Re-qwrite Oracle SQL using ANSI Oracle
body: <p>I am new to oracle and sql, I would like to know how can I re-write the following sql query using Oracle ANSI join conditions.</p>
  
  <pre><code>  SELECT     emp_no,
      (SELECT   emp_title
         FROM   hr_v_employee
         WHERE         organization_code LIKE
             SUBSTR (emp.depart_code, 0, 4) || '00'
               AND emp_position_code =
                  (SELECT     MIN (emp_position_code)
                          FROM     hr_v_employee
                                  WHERE     organization_code LIKE
                                      SUBSTR (emp.depart_code,
                                     0,
                                     4
                                     )
                   || '00'))
                   || ' '
                   || NVL (employee_deptartment, '-')
                       employee_deptartment
          FROM     employees e, employee_details o
        WHERE     emp.emp_no = o.emp_no(+)
  </code></pre>
  
  <p>Edit 1</p>
  
  <p>This what I have tried, my question is it possible to use join or ANSI standard for sub-query as well?</p>
  
  <pre><code> SELECT     emp_no,
          (SELECT   emp_title
             FROM   hr_v_employee
             WHERE         organization_code LIKE
                 SUBSTR (emp.depart_code, 0, 4) || '00'
                   AND emp_position_code =
                      (SELECT     MIN (emp_position_code)
                              FROM     hr_v_employee
                                      WHERE     organization_code LIKE
                                          SUBSTR (emp.depart_code,
                                         0,
                                         4
                                         )
                       || '00'))
                       || ' '
                       || NVL (employee_deptartment, '-')
                           employee_deptartment
              FROM     employees e left outer join employee_details o (emp.emp_no = o.emp_no)
  </code></pre>