title: Efficient Alternative to Outer Join
body: <p>The <code>RIGHT JOIN</code> on this query causes a <code>TABLE ACCESS FULL</code> on <code>lims.operator</code>.  A regular join runs quickly, but of course, the samples '<code>WHERE authorised_by IS NULL</code>' do not show up.</p>
  
  <p>Is there a more efficient alternative to a <code>RIGHT JOIN</code> in this case?</p>
  
  <pre><code>  SELECT   full_name
    FROM       (SELECT   operator_id AS authorised_by, full_name
                  FROM lims.operator)
    RIGHT JOIN (SELECT   sample_id, authorised_by
                  FROM   lims.sample
                 WHERE   sample_template_id = 200)
    USING (authorised_by)
  </code></pre>
  
  <p>NOTE: All columns shown (except full_name) are indexed and the primary key of some table.</p>