title: TABLE/CAST/MULTISET vs subquery in FROM clause
body: <p>The following query doesn't work. It is expected to fail since <code>temp.col</code> references something that is unavailable in that context.</p>
  
  <pre><code>with temp as  (
         select 'A' col from dual
         union all
         select 'B' col from dual
       )
  select *
  from temp,
       (select level || temp.col from dual connect by level &lt; 3);
  </code></pre>
  
  <p>The error message from Oracle is : ORA-00904: "TEMP"."COL": invalid identifier</p>
  
  <p>But why is the next query working ? I see CAST/MULTISET as a way to go from a SQL table to a collection type and TABLE to go back to a SQL table. Why do we use such round-trip ? I guess to make the query work, but how ?</p>
  
  <pre><code>with temp as  (
         select 'A' col from dual
         union all
         select 'B' col from dual
       )
  select *
  from temp,
       table(
         cast(
           multiset(
             select level || temp.col from dual connect by level &lt; 3
           ) as sys.odcivarchar2list
         )
       ) t;
  </code></pre>
  
  <p>The result is :</p>
  
  <pre><code>COL COLUMN_VALUE
  --- ------------
  A   1A          
  A   2A          
  B   1B          
  B   2B          
  </code></pre>
  
  <p>Look how the second column is named <code>COLUMN_VALUE</code>. Looks like a generated name by one of the construct CAST/MULTISET or TABLE.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>With the accepted answer below, I checked the documentation and found that the TABLE mechanism is a <em>table collection expression</em>. The expression between rounded brackets is the <em>collection expression</em>. The documentations defines a mechanism called <strong>left correlation</strong> :</p>
  
  <blockquote>
    <p>The collection_expression can reference columns of tables defined to
    its left in the FROM clause. This is called left correlation. Left
    correlation can occur only in table_collection_expression. Other
    subqueries cannot contains references to columns defined outside the
    subquery.</p>
  </blockquote>
  
  <p>So this is like LATERAL in 12c.</p>