T-SQL: Where xxx IN temporary table

Question

I have a temp table and want to check in a where clause wether a certain id/string is contained in the temp table.

Select...
WHERE MyId  IN MyTempTable

I get a general error in MS SQL Management studio.

is the "In" operator not suited for temp tables?

score 34 · Accepted Answer · answered Dec 06 '10 at 15:30

34

Your syntax is wrong:

SELECT ...
  FROM MyTable
 WHERE MyID IN (SELECT MyID
                  FROM MyTempTable)

I don't much like the IN operator, so I prefer this:

SELECT ...
  FROM MyTable
 WHERE EXISTS (SELECT *
                 FROM MyTempTable
                WHERE MyTable.MyID = MyID)

But it's largely a matter of taste.

answered Dec 06 '10 at 15:30

Marcelo Cantos

181,030
38
327
365

+1 you're correct because generally it's better for NOT IN vs NOT EXISTS which are quite different. So why not use EXISTS consistently? INTERSECT works too. See my [answer here](http://stackoverflow.com/questions/4249891/combining-datasets-with-except-versus-checking-on-is-null-in-a-left-join/4249958#4249958) please – gbn Dec 06 '10 at 20:41
1

@gbn: As I said, it is largely a matter of taste, but my reasons are more based on my understanding of the relational model than just consistency. Firstly, `IN` is a bit of a wart in the language that is strictly unnecessary; anything that can be expressed using `(NOT) IN` has an equivalent `(NOT) EXISTS`. But also, it is a semantic oddity that doesn't hark from either the relational algebra or the relational calculus. It was plonked in there by SQL's designers for reasons I can't fathom. It must have been a big deal, though, since it is apparently the reason for the S in SQL ("Structured"). – Marcelo Cantos Dec 06 '10 at 21:48
NOT IN does not equal NOT EXISTS hence my comment. A NULL in the NOT IN list will give no results. Why is "IN "there? I guess when ANSI was put together they took IN from one of the then current SQL dialects – gbn Dec 07 '10 at 08:42
@gbn: You are right, but I avoid nulls like the plague (atrocities like `NOT IN ('a', 'b', 'c', NULL)` being just one of many motivations), so I don't generally consider such scenarios in my thinking. Also, I wasn't referring to ANSI SQL, but the original design work that spawned those dialects. – Marcelo Cantos Dec 07 '10 at 09:51
@Lisa: The obvious way. If you can't figure it out, post another question with whatever code you've tried. – Marcelo Cantos Dec 07 '10 at 10:21

score 5 · Answer 2 · answered Dec 06 '10 at 15:31

5

Your syntax is slightly wrong. You need to do:

SELECT ...
  FROM ...
 WHERE MyId IN (SELECT MyId 
                  FROM MyTempTable);

answered Dec 06 '10 at 15:31

Neil Knight

47,437
25
129
188

score 0 · Answer 3 · answered Dec 06 '10 at 15:31

0

in requires a list of specific items. Tables generally have more than one field, how is it to know which field you mean?

You can use a subquery, where field in (select whatever from #temp)

answered Dec 06 '10 at 15:31

Donnie

45,732
10
64
86

T-SQL: Where xxx IN temporary table

3 Answers3