Using a set of numbers inside a database without creating a temporary table
- by Zizzencs
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.
