title: How many types of SQL subqueries are there?
body: <p>In an effort to understand what types of subqueries can be correlated I wrote the SQL query shown below. It shows all types of subqueries I can think of a SQL <code>select</code> statement can include.</p>
  
  <p>Though the example shown below runs in Oracle 12c, I would prefer to keep this question database agnostic. In the example below I included all 7 types of subqueries I can think of:</p>
  
  <pre><code>with 
    daily_login as ( -- 1. Independent CTE [XN]
      select user_id, trunc(login_time) as day, count(*) from shopper_login
        group by user_id, trunc(login_time)
    ),
    frequent_user as ( -- 2. Dependent CTE [XN]
      select user_id, count(*) as days from daily_login group by user_id
      having count(*) &gt;= 2
    ),
    referrer (frequent_id, id, rid, ref_level) as ( -- 3. Recursive CTE [XN]
      select fu.user_id, s.id, s.ref_id, 1 from frequent_user fu 
        join shopper s on fu.user_id = s.id
      union all
      select r.frequent_id, s.id, s.ref_id, r.ref_level + 1 from referrer r
        join shopper s on s.id = r.rid
    )
  select s.id, s.name, r.id as original_referrer,
      ( -- 4. Scalar Subquery [CN]
      select max(login_time) from shopper_login l 
        where l.user_id = s.id and l.success = 1
      ) as last_login,
      m.first_login
    from shopper s
    join referrer r on r.frequent_id = s.id
    join ( -- 5. Table Expression / Inline View / Derived Table [XN]
      select user_id, min(login_time) first_login from shopper_login
        where success = 1 group by user_id
    ) m on m.user_id = s.id
    where r.rid is null
      and s.id not in ( -- 6. Traditional Subquery [CN]
        select user_id from persona
          where description = 'Fashionista'
            and id in ( -- 7. Nested subquery [CN]
              select user_id from users where region = 'NORTH')
      );
  </code></pre>
  
  <p>Legend:</p>
  
  <pre><code>[C]: Can be correlated
  [X]: Cannot be corretaled
  [N]: Can include nested subqueries
  </code></pre>
  
  <p>My questions are:</p>
  
  <ul>
  <li>Did I get all possible types? Are there alternative names for these types of subqueries?</li>
  <li>Am I correct thinking that only Scalar (#4), Traditional (#6), and Nested (#7) subqueries can be correlated?</li>
  <li>Am I correct thinking Table Expressions and CTEs (#1, #2, and #3) cannot be correlated? (however, they can include Nested subqueries that can be correlated)</li>
  </ul>