How to change a UNION to a IN clause?

Question

I need to get a maximum of 3 distinct records from the same table, so currently I'm doing:

SELECT 1, mycolumn FROM mytable WHERE id = @firstId
UNION ALL
SELECT 2, mycolumn FROM mytable WHERE id = @secondId
UNION ALL
SELECT 3, mycolumn FROM mytable WHERE id = @thirdId

The actual SELECT part contains over 20 columns and the FROM part contains a number of JOINs. The first column is a constant and is always fixed depending on the record. I don't know how many records might return. It could be anything from 0 to 3 records.

Is it possible to change the above query so that it uses IN like this:

SELECT ???, mycolumn FROM mytable WHERE id IN (@firstId, @secondId, @thirdId)

But how do I explicitly map each record to the fixed constant if I use IN?

Answer 1

You may use a CASE expression here with a single query:

SELECT
    CASE id WHEN @firstId  THEN 1
            WHEN @secondId THEN 2
            WHEN @thirdId  THEN 3 END AS val,
    mycolumn
FROM mytable
WHERE
    id IN (@firstId, @secondId, @thirdId);

If you wish to also order by the computed column, then add ORDER BY val to the end of the above query.