title: Alias scoping in a (Correlated) subquery embedded in the select statement
body: <p>My work is running SQL Server 2008 and I spend a lot of time querying the database for information as a side piece to my job.  If I need information that isn't at the same aggregate level as my dataset I use an embedded query in the select statement. Usually it's 2 or 3 slightly different versions of the same number, so they both query the same tables. (See example below)</p>
  
  <p>The question is <em>what is the scoping of the aliases for the subqueries embedded in a select statement</em>. The two options I've thought of are:</p>
  
  <ol>
  <li>At the Script level and must be unique to all subqueries and
  tables? </li>
  <li>At the Subquery level and can share the same aliases in
  each.</li>
  </ol>
  
  <p>I know for tables aliased in the From statement they must be unique.  I thought that the fact that the queries were executing on each row generation that it might be a different situation. </p>
  
  <p>Examples (completely made up, let me know any obvious errors and I'll correct them):</p>
  
  <p>Script Level - unique aliases for all subqueries and tables:</p>
  
  <pre><code>Select
    p.purchaseid, p.purchasedate, 
    s.storename, c.customerid, 
  (select count(p2.purchaseid) 
    from purchases p2 inner join 
         store s2 on p2.storeid = s2.storeid
    where s2.storeid = s.storeid
      and p2.purchasedate = p.purchasedate) as 'Store Daily Total Purchases',
  (select count(p3.purchaseid) 
    from purchases p3 inner join 
         store s3 on p3.storeid = s3.storeid
    where p3.customerid = p.customerid
      and p3.purchasedate = p.purchasedate) as 'Customer Daily Total Purchases'
  from 
    purchases p inner join   
    customer c on p.customerid = c.customerid
    store s on p.storeid = s.storeid
  </code></pre>
  
  <p>Query Level - common aliases for subqueries ok:</p>
  
  <pre><code>Select
    p.purchaseid, p.purchasedate, 
    s.storename, c.customerid, 
  (select count(p2.purchaseid) 
    from purchases p2 inner join 
         store s2 on p2.storeid = s2.storeid
  where s2.storeid = s.storeid
    and p2.purchasedate = p.purchasedate) as 'Store Daily Total Purchases',
  (select count(p2.purchaseid) 
    from purchases p2 inner join 
         store s2 on p2.storeid = s2.storeid
  where p2.customerid = p.customerid
    and p2.purchasedate = p.purchasedate) as 'Customer Daily Total Purchases'
  from 
    purchases p inner join   
    customer c on p.customerid = c.customerid
    store s on p.storeid = s.storeid
  </code></pre>