SQL Subselect "Bug"

SQL Subselect "Bug"

Columns and column expressions from the outer query are visible in the context of the subquery, so it is technically valid query (of course, it will fail if you run it as a separate query). This is why it is good practice to use table aliases to reference columns in subqueries.

Here you have an example:

--Bug subqueries

DROP TABLE #Bugtest

CREATE table #Bugtest (Id int)

INSERT INTO #Bugtest VALUES (1)
INSERT INTO #Bugtest VALUES (2)
INSERT INTO #Bugtest VALUES (3)
INSERT INTO #Bugtest VALUES (4)
INSERT INTO #Bugtest VALUES (5)

DECLARE @UsersToDelete TABLE (IdNumber int);
INSERT INTO @UsersToDelete (IdNumber) VALUES (2)

--subselect Error
SELECT * FROM #Bugtest WHERE Id IN (SELECT Id FROM
@UsersToDelete)

If you run top subselect only, it shows an error.

--subselect OK
SELECT * FROM #Bugtest WHERE Id IN (SELECT IdNumber FROM
@UsersToDelete)

Take care making these type of sentences because it corrupts their results.