MSSQL temp table returns all rows when using IN subquery

Question

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

You are comparing the I'd column to itself in the second query so always true — Ab Bennett
– Ab Bennett, Commented Nov 16, 2017 at 11:52

Gordon Linoff · Accepted Answer · 2017-11-16 11:52:17Z

2

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;

answered Nov 16, 2017 at 11:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ab Bennett · Accepted Answer · 2017-11-16 11:53:28Z

1

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

answered Nov 16, 2017 at 11:53

Ab Bennett

1,43219 silver badges27 bronze badges

Collectives™ on Stack Overflow

MSSQL temp table returns all rows when using IN subquery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related