title: SQL - Relationship between a SubQuery and an Outer Table
body: <p><strong>Problem</strong></p>
  
  <p>I need to better understand the rules about when I can reference an outer table in a subquery and when (and why) that is an inappropriate request. I've discovered a duplication in an Oracle SQL query I'm trying to refactor but I'm running into issues when I try and turn my referenced table into a grouped subQuery.</p>
  
  <p>The following statement works appropriately:</p>
  
  <pre><code>SELECT  t1.*  
  FROM    table1 t1, 
  INNER JOIN table2 t2 
          on t1.id = t2.id        
          and t2.date = (SELECT max(date) 
                         FROM   table2  
                         WHERE  id = t1.id) --This subquery has access to t1
  </code></pre>
  
  <p>Unfortunately table2 sometimes has duplicate records so I need to aggregate t2 first before I join it to t1. However when I try and wrap it in a subquery to accomplish this operation, suddenly the SQL engine can't recognize the outer table any longer. </p>
  
  <pre><code>SELECT  t1.* 
  FROM    table1 t1, 
  INNER JOIN (SELECT * 
              FROM  table2 t2
              WHERE t1.id = t2.id              --This loses access to t1
                and t2.date = (SELECT max(date) 
                               FROM   table2 
                               WHERE  id = t1.id)) sub on t1.id = sub.id 
                               --Subquery loses access to t1
  </code></pre>
  
  <p>I know these are fundamentally different queries I'm asking the compiler to put together but I'm not seeing why the one would work but not the other. </p>
  
  <p>I know I can duplicate the table references in my subquery and effectively detach my subquery from the outer table but that seems like a really ugly way of accomplishing this task (what with all the duplication of code and processing).</p>
  
  <p><strong>Helpful References</strong></p>
  
  <ul>
  <li><p>I found this fantastic description of the order in which clauses are executed in SQL Server: (<a href="https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause/1944492#1944492">INNER JOIN ON vs WHERE clause</a>). I'm using Oracle but I would think that this would be standard across the board. There is a clear order to clause evaluation (with FROM being first) so I would think that any clause occuring further down the list would have access to all information previously processed. I can only assume my 2nd query somehow changes that ordering so that my subquery is being evaluated too early?</p></li>
  <li><p>In addition,  I found a similar question asked (<a href="https://stackoverflow.com/questions/2645485/referencing-outer-querys-tables-in-a-subquery">Referencing outer query&#39;s tables in a subquery</a>
  ) but while the input was good they never really explained why he couldn't do what he is doing and just gave alternative solutions to his problem. I've tried their alternate solutions but it's causing me other issues. Namely, that subquery with the date reference is fundamental to the entire operation so I can't get rid of it.</p></li>
  </ul>
  
  <p><strong>Questions</strong></p>
  
  <ul>
  <li><p>I want to understand what I've done here... Why can my initial subquery see the outer table but not after I wrap the entire statement in a subquery?</p></li>
  <li><p>That said, if what I'm trying to do can't be done, what is the best way of refactoring the first query to eliminate the duplication? Should I reference table1 twice (with all the duplication that requires)? Or is there (probably) a better way of tackling this problem?</p></li>
  </ul>
  
  <p>Thanks in advance!</p>
  
  <p><strong>------EDIT------</strong></p>
  
  <p>As some have surmised these queries above are not the actually query I'm refactoring but an example of the problem I'm running into. The query I'm working with is a lot more complicated so I'm hesitant to post it here as I'm afraid it will get people off track.</p>
  
  <p><strong>------UPDATE------</strong></p>
  
  <p>So I ran this by a fellow developer and he had one possible explanation for why my subquery is losing access to t1. Because I'm wrapping this subquery in a parenthesis, he thinks that this subquery is being evaluated before my table t1 is being evaluated. This  would definitely explain the 'ORA-00904: "t1"."id": invalid identifier' error I've been receiving. It would also suggest that like arithmetic order of operations, that adding parens to a statement gives it priority within certain clause evaluations. I would still love for an expert to weigh in if they agree/disagree that is a logical explanation for what I'm seeing here.</p>