T-SQL CTE Question (SQL Server 2008)

Question

Hi,

Why does the following query work, when there is no column named Agentid in the "NewAccounts"-CTE?

WITH 
NewAccounts AS (
    SELECT 3 as Newaccountid
),
MovedAUM AS (
    SELECT 1 AS Agentid, 2 as Col2
)
SELECT * FROM MovedAUM WHERE agentid IN (SELECT Agentid FROM NewAccounts)

The following, modified query returns the error message as expected:

WITH 
NewAccounts AS (
    SELECT 3 AS newaccountid
)
SELECT Agentid FROM NewAccounts

Answer 1

The first one works because MovedAUM is in scope within the nested SELECT. It is actually returning MovedAUM.AgentId for each row in NewAccounts. In other words the WHERE clause is doing nothing - it's equivalent to WHERE 1 = 1.

This slightly modified version will fail:

WITH  
NewAccounts AS ( 
    SELECT 3 as Newaccountid 
), 
MovedAUM AS ( 
    SELECT 1 AS Agentid, 2 as Col2 
) 
SELECT * FROM MovedAUM WHERE agentid IN
    (SELECT NewAccounts.Agentid FROM NewAccounts) 

Answer 2

Because you are doing explicit join in that with

SELECT * FROM TAB1, TAB2 

Answer 3

SQL Server knows, it has the Agentid columns and can use it, however it is not what you want, don't use IN, use EXISTS it will prevent this

WITH 
NewAccounts AS (
    SELECT 3 as Newaccountid
),
MovedAUM AS (
    SELECT 1 AS Agentid, 2 as Col2
)
SELECT * FROM MovedAUM WHERE EXISTS (SELECT 1 
                              FROM NewAccounts 
                              where NewAccounts.Agentid = MovedAUM.agentid )

See also here: http://sqlservercode.blogspot.com/2007/04/you-should-never-use-in-in-sql-to-join.html