Using a set of numbers inside a database without creating a temporary table
Posted
by Zizzencs
on Stack Overflow
See other posts from Stack Overflow
or by Zizzencs
Published on 2010-05-03T16:17:55Z
Indexed on
2010/05/04
8:18 UTC
Read the original article
Hit count: 250
sql
|sql-server-2008
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.
© Stack Overflow or respective owner