Can we write case statement without having else statement

Question

I have this query:

select case when id=1 then 'A'
when id=2 then 'B'
end 
from test

It is giving me o/p as

Id
A
B
NULL
NULL
NULL

I don't want to have NULL values in my output, I only want to compare in A and B, is it possible in case statement.

Answer 1

A case expression can only manipulate the value of an expression, not remove rows from the result. If you want to omit the nulls from the result, you'll have to add a where clause:

SELECT CASE WHEN id = 1 THEN 'A'
            WHEN id = 2 THEN 'B'
       END 
FROM   test
WHERE  id IN (1, 2) -- HERE

Answer 2

You can use a WHERE clause to restrict the output.

SELECT CASE WHEN id=1 THEN 'A'
WHEN id=2 THEN 'B'
END 
FROM test
WHERE id IN (1,2)

Or if you wanted to showcase some other value instead of null use an else part inside the CASE statement.

SELECT CASE WHEN id=1 THEN 'A'
WHEN id=2 THEN 'B' ELSE 'Invalid'
END 
FROM test