Return all values from IN Clause on SQL Server 2000

Question

Is there a way to retrieve all the data from an IN clause?

Let's assume my table got (ID,Name):

0 Banana
1 Mango
2 Papaya
3 Lemon

and my query:

SELECT * FROM Fruits WHERE Name IN (Banana,Mango,Orange)

I Want 'Orange' to return, with an empty ID (since there's no register). How to do this?

Answer 1

You can't use the IN clause for this. You would need to get the target fruits into a table that you can outer join against.

SELECT ...
FROM 
(SELECT 'Banana' AS fruit UNION ALL SELECT 'Mango' UNION ALL SELECT 'Orange') f 
LEFT JOIN Fruits ON Fruits.Name = f.fruit

Or Option 2 (as long as your list is <= 8000 characters). Create a UDF like the one here (but using varchar(8000) instead of varchar(max)). Then use it as follows.

SELECT ...
FROM dbo.fnSplitStringList('Banana,Mango,Orange') f 
LEFT JOIN Fruits ON Fruits.Name = f.StringLiteral

Answer 2

Right join by name, but you need a separate table with names.

Answer 3

If you are doing it once, I wrote a program, it simplifies creating the "IN" clause, here: InClauseCreator. If, however, you are doing it regularly, then you'd be better off importing data into a temp table and then doing a left join, close to what Martin Smith suggested.