T-SQL: Where xxx IN temporary table

sql-server where in-operator temp-tables
Elisabeth picture Elisabeth · Dec 6, 2010 · Viewed 23.1k times · Source

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?

Answer

Marcelo Cantos picture Marcelo Cantos · Dec 6, 2010

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.

Related questions

How to use NULL or empty string in SQL

I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.

Read More
SQL : BETWEEN vs <= and >=

In SQL Server 2000 and 2005: what is the difference between these two WHERE clauses? which one I should use on which scenarios? Query 1: SELECT EventId, EventName FROM EventMaster WHERE EventDate BETWEEN '10/15/2009' AND '10/18/2009' Query 2: SELECT EventId, EventName FROM …

Read More
CASE in WHERE, SQL Server

I'm doing some search, where users are choosing in dropdown some clauses. When they leave some box empty, I want query to ignore clause. I know CASE, and best I thought of is that if I pass 0 to parameter in …

Read More