Return null if value does not exist

Question

I have a table with project data, and I want to return NULL or "Not_Exist" as a result if the project_ID was not found.

For example:

SELECT a.[Project ID], a.[Stage]
FROM Projects a
WHERE a.[Project ID]  IN ('CR324S', 'a')

CR324S exists in the table but 'a' is not there, so the results that I want would be:

Project ID     Stage
-----------------------------
CR324S         Implementation
a              Not_Exist

Answer 1

You can try below -

1. first construct a set of value using union
2. then use left join and put your condition in ON Clause

SELECT a.[pid], coalesce(b.[Stage],'Not_Exist') as stage
from
(
    select 'CR324S' as pid 
    union 
    select 'a'
)A left join Projects B on A.pid=B.[Project ID] and [Project ID]  IN ('CR324S','a')

OR

SELECT a.[pid], coalesce(b.[Stage],'Not_Exist') AS stage
FROM   (VALUES ('CR324S'), ('a')) A(pid)
LEFT JOIN Projects B on A.pid=B.[Project ID] AND [Project ID]  IN ('CR324S','a')