Using CTE Result with IN in SQL Server

Question

I am trying to find employees names with title who don't receive any bonus and I get the following error:

WITH Result AS
(
    SELECT
        A.assoc_id  
    FROM
        Employee A 
    WHERE 
        assoc_id NOT IN (SELECT B.assoc_ref_id FROM bonus B) 
)
SELECT
    E.firstname, E.lastname, T.assoc_title 
FROM
    Employee E 
INNER JOIN
    Title T ON E.assoc_id = T.assoc_ref_id
WHERE
    E.assoc_id IN (Result ) /*(Result ) throws error*/

However, this works fine for me and I get the desired result:

WITH Result AS
(
    SELECT
        E.firstname, E.lastname, T.assoc_title 
    FROM
        Employee E 
    INNER JOIN
        Title T ON E.assoc_id = T.assoc_ref_id
    WHERE
        E.assoc_id IN (SELECT A.assoc_id FROM Employee A 
                       WHERE assoc_id NOT IN (SELECT B.assoc_ref_id 
                                              FROM bonus B))
)
SELECT * FROM Result

What wrong am I doing in the first query? Can we use joins while querying CTE result at all?

Thanks.

Answer 1

You are fundamentally not understanding how to use a table expression.

You need:

where E.assoc_id in (select assoc_id from Result);

A bit more explanation - A CTE, just like a sub-query or view, produces a logical table expression; and, just like any other table, you can't just reference it since that does not make any sense to the optimizer, just like any other table you query it by selecting from it. In addition, in many cases the optimizer can and will expand the table expression to be logically part of the main query (ie not a distinctly executed and self-contained object).