CODEWITHSUNDEEP
sqlsql-servernull
Vishwanath jawalkar
Vishwanath jawalkar

Reputation: 123

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

Upvotes: 1

Views: 7451

Answers (4)

Tim Schmelter
Tim Schmelter

Reputation: 460018

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)

Upvotes: 4

Serg
Serg

Reputation: 22811

Just left join a table of 'Test1','Test3','Test5' to the table in question

select id,t.name
from
(
    select 'Test1' as Name
    union
    select 'Test3' as Name
    union
    select 'Test5' as Name
) t
left join table1 on t.Name = table1.Name;

This will enumerate all matching ids or return null as id. You may wish to return only 3 rows then

select max(id) -- min(id)
    , t.name 
from
(
    select 'Test1' as Name
    union
    select 'Test3' as Name
    union
    select 'Test5' as Name
) t
left join table1 on t.Name = table1.Name
group by t.name;

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146409

Sure use a Values table constructor with an outer join:

Select o.id, m.name
   from (VALUES ('Test1'), ('Test3'), ('Test5')) t(name)
      left join myData o 
         on o.name = t.name

Upvotes: 2

JohnHC
JohnHC

Reputation: 11195

It's not pretty, but a CTE will do it

with MyData as
(
    select 'Test1' as MyName
    union
    select 'Test3' as MyName
    union
    select 'Test5' as MyName
)
select MyData.MyName, T1.id
from MyData
left join Table1 T1
on T1.Name = MyData.MyName

Upvotes: 2

Related Questions