CODEWITHSUNDEEP
mysqlsql
Raj
Raj

Reputation: 2028

I want the sql IN operator to return null when record is not found

I want to get null value for each element if there is no match in the in operator value, e.g. 

SELECT name
FROM table
WHERE id IN (1, 2, 3, 4, 5)

In such a case I want to get null value in case IN operator value does not exists, e.g. for

IN (1, 2, 3, 4, 5)

I want return value as

'ABC', null, 'XYZ', null, null

Currently it just returns

'ABC', 'XYZ'

Upvotes: 0

Views: 1635

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562398

This predicate:

WHERE id IN (1, 2, 3, 4, 5)

Can only be applied to rows that exist in the table. That's how the WHERE clause works, it is evaluated for rows that exist, and if the conditions are true, that row is included in the result set of the query.

A predicate cannot invent new rows that don't exist in your table.

So no, you can't use IN ( ) to insert NULL rows where the id is missing in your table.

Upvotes: 1

Andrew Zagornyy
Andrew Zagornyy

Reputation: 1

You can create temporary table, e.g.:

create tmp_table (name varchar2(32);

and use this pl/sql procedure:

declare
name_tmp varchar2(32);
for i in (SELECT * FROM table WHERE id IN (1, 2, 3, 4, 5)) loop
SELECT name name_tmp into FROM table id=i.id;
exception when no_data_found then name_tmp := null; end;
insert into tmp_table (name) values (name_tmp) ;
end loop; 
end;

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37377

Just create helper table with all values:

create table helpTable (uid int);
insert into helpTable values (1),(2),(3),(4),(5);

and then left join it and use case statement to determine if there should be displayed name when there's corresponding value (not null):

SELECT case when ht.uid is not null then name end `name`
FROM table t
LEFT JOIN helpTable ht ON t.id = ht.uid;

After you are done, DROP TABLE IF EXISTS helpTable;.

If you don't want to create table, you could do this (but I am not 100% sure that it'll work):

SELECT case when ht.uid is not null then name end `name`
FROM table t
LEFT JOIN (
    select 1 `uid`
    union all
    select 2
    union all
    select 3
    union all
    select 4
    union all
    select 5
) ht ON t.id = ht.uid;

Upvotes: 1

Related Questions