title: Single-row subqueries in Oracle -- what is the join plan?
body: <p>I've just discovered that Oracle lets you do the following:</p>
  
  <pre><code>SELECT foo.a, (SELECT c 
                 FROM bar 
                 WHERE foo.a = bar.a) 
  from foo
  </code></pre>
  
  <p>As long as only one row in bar matches any row in foo.</p>
  
  <p>The explain plan I get from PL/SQL developer is this:</p>
  
  <pre><code>SELECT STATEMENT, GOAL = ALL_ROWS               
   TABLE ACCESS FULL  BAR 
   TABLE ACCESS FULL  FOO 
  </code></pre>
  
  <p>This doesn't actually specify how the tables are joined.  A colleague asserted that this is more efficient than doing a regular join.  Is that true? What is the join strategy on such a select statement, and why doesn't it show up in the explain plan?</p>
  
  <p>Thanks.</p>