SQL Server reuse aliases in subqueries

Question

This might sound like a dumb question - apologies, I'm new to SQL Server and I just want to confirm my understanding. I've got a query that is aggregating values in a table as a subquery in different ways for different columns, e.g. for a transaction on a given day, transactions in the previous month, previous 6 months, before that, after that.

I aliased the main table as tx, then the subquery alias as tx1 so I could use for example:

tx1.TransactionDate < tx.TransactionDate

I created one column, copied it and amended the WHERE conditions.

I assumed that the scope of an alias in the subquery is bound to that subquery, so it didn't matter that the alias was the same in each case.

It seems to work, but then as neither the main table tx is altered nor the subquery tables tx1 I wouldn't know if the scope of the alias tx1 was bound to each subquery or if the initial tx1 was being reused.

Am I correct in my assumption?

Query:

SELECT tr.transaction_value , 
       Isnull( 
       ( 
              SELECT Sum(tr1.transaction_value) 
              FROM   [MyDB].[dbo].[Transactions] tr1 
              WHERE  tr1.client_ref = tr.client_ref),0) 
and    tr1.transaction_date > tr.transaction_date ),0) AS 'Future_Transactions' ,isnull( 
( 
       SELECT sum(tr1.transaction_value) 
       FROM   [MyDB].[dbo].[Transactions] tr1 
       WHERE  tr1.client_ref = tr.client_ref),0) 
AND 
tr1.transaction_date < tr.transaction_date ),0) AS 'Prior_Transactions' FROM [MyDB].[dbo].[Transactions]

Post code in the question. I've edited your question. Here is a related post that can help you: http://stackoverflow.com/questions/188828/sql-table-alias-scope — Ricardo Pontual, Oct 28 '16 at 13:52

Matej Hlavaj · Accepted Answer · 2016-10-28T13:44:26.123

I think that following script can explain you everything.

SELECT 1,1,GETDATE()
INSERT INTO @t ( Id, UserId, TranDate )
SELECT 2,1,GETDATE()
INSERT INTO @t ( Id, UserId, TranDate )
SELECT 3,1,GETDATE()



SELECT tx.Id/*main alias*/,
       tx1.Id /*First subquery alias*/,
       tx2.Id /*Second subquery alias*/,
       (SELECT Id FROM @t txs /*alias only in this one subquery/must be different from main if you want use main alias in it...*/ 
        WHERE txs.Id = tx.Id+2 /*here is used main value = subquery value+2*/) AS Id
FROM @t tx /*main*/
JOIN (SELECT * 
      FROM @t tx 
      WHERE tx.Id =  1 /*this one using subquery values + you are not able to use here main value*/ 
      ) tx1 --alias of subquery
      ON tx.Id = tx1.Id /*here is used main value = subquery value*/
CROSS APPLY (SELECT TOP 1 * 
             FROM @t txc /*This one must be different from main if you want use it to comparison with main*/
             WHERE txc.Id > tx.Id /*this one using subquery value > main value*/ 
             ) tx2 --alias of subquery
WHERE tx.Id = 1 AND /*Subquery alias canot reference on First subquery value*/
      tx1.Id = 1 AND/*Subquery alias*/
      tx2.Id = 2 /*Subquery alias*/

It means that yea, it could be reused, but only if you dont want compare main / sub, because if you reuse it and for example you try to do folowing statement in subquery tx.Id > tx.Id It causes that only values in subquery will be compared. In our example it causes that you dont get anything because you comaring values in same row...

SQL Server reuse aliases in subqueries

1 Answers1