title: Add filter on left outer join in oracle
body: <p>I Have a scenario where there are 3 tables, First table have id, name. Second table have id, status, Third table have id, dept_id.</p>
  <p>First table is the parent table and have superset of data. Second table doesn’t have all the records while third have all of the records as First table.
  <a href="https://i.stack.imgur.com/h89pJ.png" rel="nofollow noreferrer">Table A,Table B, Table C</a></p>
  <p>My query is to retrieve ids based on dept_id. If I pass status then only ids tagged to that dept and status should be displayed else if I don’t pass status or if I pass null then all ids tagged to input dept_id should be displayed.</p>
  <p>Here inputs are dept_id and status(not mandatory)</p>
  <p><a href="https://i.stack.imgur.com/h89pJ.png" rel="nofollow noreferrer">Tables</a></p>
  <p>Expected output:
  I am passing deptid = 'A' and if I pass status = 'new' then only 1,2 has to be displayed and if I don't pass status then all 1,2,3,4 have to be displayed.
  I am trying to insert the output into gt table twice by using if status is not null else pass status. I don’t want to write 2 inserts and try to optimize my code here.</p>
  <pre><code>If status is not null then
  Insert into gt_temp
  Select a.id from a,b,c
  Where a.id=b.id(+)
  And a.id=c.id
  And c.dept_id= ‘A’
  And b.status= ‘new’;
  Else
  Insert into gt_temp
  Select a.id from a,b,c
  Where a.id=b.id(+)
  And a.id=c.id
  And c.dept_id= ‘A’;
  
  End if;
  </code></pre>