<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>

The plan you have there does not provide much information at all.
Use SQL*Plus and use dbms_xplan to get a more detailed plan.  Look for a script called utlxpls.sql.
This gives a bit more information:-
```
```
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1837 | 23881 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BAR  |    18 |   468 |     2   (0)| 00:00:01 |
|   2 |  TABLE ACCESS FULL| FOO  |  1837 | 23881 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("BAR"."A"=:B1)

Note
-----
   - dynamic sampling used for this statement

18 rows selected.
```
```
I didn't create any indexes or foreign keys or collect statistics on the tables, which would change the plan and the join mechanism choosen.  Oracle is actually doing a NESTED LOOPS type join here.  Step 1, your inline sub-select, is performed for every row returned from FOO.
This way of performing a SELECT is not quicker.  It could be the same or slower.  In general try and join everything in the main WHERE clause unless it becomes horribly unreadable.