title: Performance of nested select
body: <p>I know this is a common question and I have read several other posts and papers but I could not find one that takes into account indexed fields and the volume of records that both queries could return.</p>
  
  <p>My question is simple really. Which of the two is recommended here written in an SQL-like syntax (in terms of performance).</p>
  
  <p>First query:</p>
  
  <pre><code>Select *
  from someTable s
  where s.someTable_id in
                      (Select someTable_id 
                       from otherTable o
                       where o.indexedField = 123)
  </code></pre>
  
  <p>Second query:</p>
  
  <pre><code>Select *
  from someTable
  where someTable_id in
                    (Select someTable_id 
                     from otherTable o
                     where o.someIndexedField = s.someIndexedField
                     and o.anotherIndexedField = 123)
  </code></pre>
  
  <p>My understanding is that the second query will query the database for every tuple that the outer query will return where the first query will evaluate the inner select first and then apply the filter to the outer query.</p>
  
  <p>Now the second query may query the database superfast considering that the someIndexedField field is indexed but say that we have thousands or millions of records wouldn't it be faster to use the first query?</p>
  
  <p>Note: In an Oracle database.</p>