AS91 AS91 - 21 days ago 5
SQL Question

Use EXISTS with derived table

SELECT *
FROM
( SELECT ID, Name
FROM ....
) AS DT
WHERE NOT EXISTS (SELECT 1 FROM DT AS DT2 WHERE DT.ID=DT2.ID AND DT2.Name='A')


Trying to use a Derived table DT in an existential subquery but getting the error:


Invalid object name 'DT'


Does the scope of a derived table not extend to an existential subquery?

Answer

The scope extend to the inner subquery, but you are trying to clone the table.

In the exists () you could use ( SELECT ID, Name FROM .... ) AS DT but is better using WITH

WITH cte as ( 
       SELECT ID, Name
       FROM ....
) 
SELECT *
FROM cte as DT
WHERE NOT EXISTS (SELECT 1 
                  FROM cte AS DT2 
                  WHERE DT.ID=DT2.ID 
                    AND DT2.Name='A')

Suggestion: As you can see the scope of DT like DT.id will work, the problem is you try to clone the derivated table and you cant. Behind door this is exactly what CTE does.

SELECT *
FROM ( SELECT ID, Name
       FROM ....) as DT
WHERE NOT EXISTS (SELECT 1 
                  FROM ( SELECT ID, Name
                         FROM ....) AS DT2 
                  WHERE DT.ID=DT2.ID 
                    AND DT2.Name='A')