Vishwanath jawalkar Vishwanath jawalkar - 1 month ago 6
SQL Question

Display NULL if some value is not found in SQL

I have a sample data here


id name
----------
1 Test1
2 Test2
3 Test3
4 Test4


So when I execute this QUERY

select id,name from table1 where name IN ('Test1','Test3','Test5')


It gives me an output of


id name
----------
1 Test1
3 Test3


Is there any way I can get ouput like this


id name
----------
1 Test1
3 Test3
null Test5

Answer

You could use table value constructors(>= 2008):

SELECT CASE WHEN EXISTS(SELECT 1
                     FROM   table1 t
                     WHERE  E.Name = t.Name) 
           THEN E.Id
           ELSE NULL END AS Id,
       Name
FROM   (VALUES(1,'Test1'),(3,'Test3'),(5,'Test5')) E(Id,Name)  
Comments