T-SQL CTE Question (SQL Server 2008)

Why does the following query work, when there is no column named Agentid in the 开发者_JAVA技巧"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

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) 

Because you are doing explicit join in that with

SELECT * FROM TAB1, TAB2 

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