Using a set of numbers inside a database without creating a temporary table

Question

I have a set of numbers and a table in a database with the id (primary key) and text (not null) columns. I would like to create a query that returns all the numbers in the set and the associated text from the table. Unfortunately not all numbers exist in the database's id column, so this won't work:

select id, text
from table
where id in (<set of numbers>)

For the non-existing ids the best would be to return null as the text from the query.

Is there a way to produce the desired output without first creating a temporary table from the set inside the database?

The database engine in use is a Microsoft SQL Server 2008 SP1 but I'd be interested in any solution with any database engine.

Answer 1

The solutions are highly database specific

• mysql has no good way to do the above without temporary tables
• oracle can do the requested very efficiently with recursive queries (details here)
• ms sql can do it with recursive queries, but I don't know how efficiently

Check the linked article and see which of the queries MS SQL 2008 supports and how it performs on them.