title: Inner Join vs Cross Apply - Multi-part identifier could not be bound
body: <p>Consider the following schema:</p>
  
  <pre><code>create table TableA (A1 int)
  create table TableB (B1 int, B2 int)
  create table TableC (C1 int, C2 int)
  create table TableD (D1 int)
  </code></pre>
  
  <p>And the following query:</p>
  
  <pre><code>SELECT * 
  FROM TableA a
  INNER JOIN TableB b ON b.B1=a.A1
  INNER JOIN (SELECT TOP 1 * 
              FROM TableC c
              WHERE c.C1=b.B1 ORDER BY c.C2) foo ON foo.C1=a.A1
  INNER JOIN TableD d ON d.D1=foo.C1
  </code></pre>
  
  <p>In SQL Fiddle (SQL Server 2008) I get the following result:</p>
  
  <pre><code>The multi-part identifier "b.B1" could not be bound.: SELECT * FROM TableA a INNER JOIN TableB b ON b.B1=a.A1 INNER JOIN (SELECT TOP 1 * FROM TableC c WHERE c.C1=b.B1 ORDER BY c.C2) foo ON foo.C1=a.A1 INNER JOIN TableD d ON d.D1=foo.C1
  </code></pre>
  
  <p>But replacing the <em>INNER JOIN</em> of the subquery by <em>CROSS APPLY</em> fixes the issue:</p>
  
  <pre><code>SELECT * 
  FROM TableA a
  INNER JOIN TableB b ON b.B1=a.A1
  CROSS APPLY (SELECT TOP 1 * 
              FROM TableC c
              WHERE c.C1=b.B1 AND c.C1=a.A1 ORDER BY c.C2) foo
  INNER JOIN TableD d ON d.D1=foo.C1
  </code></pre>
  
  <p>My questions are:</p>
  
  <p>1) Why the first query fail?</p>
  
  <p>2) Why the second query doesn't fail?</p>
  
  <p>3) <em>CROSS APPLY</em> is specific to SQL Server. Which would be the SQL Standard solution to this problem?</p>
  
  <p><strong>Important note</strong>: Don't try to understand the logic behind TableA, ..., TableD. They're just an abstraction to a more complex query (which is awful to read). I think you'll get the idea of the issue.</p>