CODEWITHSUNDEEP
sqlsql-servertemp-tables
user3790083
user3790083

Reputation: 190

MSSQL temp table returns all rows when using IN subquery

I recently stumbled upon this example. Why does query return all rows when using non-defined column on temp table "id". This only works for id and it only works when using IN subquery.

DECLARE @test TABLE
( Id INT)

INSERT INTO @test
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3

SELECT * FROM @test

SELECT 1 AS 'myid'
INTO #tmpId

--Returns one rows - OK
SELECT * FROM @test WHERE id in (SELECT myId FROM #tmpId)
--Returns all rowns - WHY
SELECT * FROM @test WHERE id in (SELECT id FROM #tmpId)
--ERROR - no column - OK
SELECT * FROM @test WHERE id in (SELECT asdf FROM #tmpId)
--ERROR - no column - OK
SELECT id FROM #tmpId

DROP TABLE #tmpId

Upvotes: 1

Views: 50

Answers (2)

Ab Bennett
Ab Bennett

Reputation: 1432

In the second query you are comparing the Id column to itself as doesn't exist in inner table, so always true

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Always use table aliases when you have multiple tables. The first query should be written as:

SELECT t.* FROM @test t WHERE t.id in (SELECT tt.myid FROM #tmpId tt)

This does what you want.

If you write the second query as:

SELECT t.* FROM @test t WHERE t.id in (SELECT tt.id FROM #tmpId tt)

You will get an error. Your version is interpreted as a correlated subquery:

SELECT t.* FROM @test t WHERE t.id in (SELECT t.id FROM #tmpId tt)

That is not what you want. It is equivalent to:

SELECT t.* FROM @test t WHERE t.id IS NOT NULL;

Upvotes: 2

Related Questions