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.

Answer 2

You can use CASE like following.

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

Another approach can be using DENSE_RANK if you have one record for each provided id and @firstId, @secondId & @thirdId are in ascending order.

SELECT DENSE_RANK() 
         OVER( 
           ORDER BY id) rn, 
       mycolumn 
FROM   mytable 
WHERE  id IN ( @firstId, @secondId, @thirdId )