Return null values IN clause mysql

Question

Print not found when there is no data found in database. For example in my database I do not have 56443 therefore it should print 'not found'

SELECT uid, (CASE WHEN (u.uid = null) THEN 'not found' ELSE 'found' END) as result
FROM (SELECT uid
            FROM users
            WHERE uid IN (1,2,56443,3)) as u;

Getting result as follows

+--------+--------+
| uid    | result|
+--------+--------+
| 1      | found | 
| 2      | found |
| 3      | found |
+--------+--------+

I am also expecting not found row with 56443

You sould use u.uid is null instead of u.uid = null, as null values can not be compared using = operator in sql. — Anupam Singh
– Anupam Singh, Commented Mar 31, 2016 at 5:22

cha · Accepted Answer · 2016-03-31 05:18:40Z

4

You need to use a different approach. You will need to create a inline view with all the values using the UNION ALL, and then left join it with the users table:

SQL Fiddle

Query 1:

SELECT a.uid, (CASE WHEN (u.uid is null) THEN 'not found' ELSE 'found' END) as     result
FROM (select 1 as UID FROM dual
      UNION ALL
      select 2 as UID FROM dual
      UNION ALL
      select 56443 as UID FROM dual
      UNION ALL
      select 3 as UID FROM dual) as a
LEFT JOIN users u on a.uid = u.uid

[Results]:

|   UID |    result |
|-------|-----------|
|     1 |     found |
|     2 |     found |
|     3 |     found |
| 56443 | not found |

answered Mar 31, 2016 at 5:18

cha

10.4k1 gold badge20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mubeen Over a year ago

Wow. Thank you very much for your help. I really appreciate it

1000111 Over a year ago

The query works for this particular case. If you have a different set of values then you have to form this query dynamically otherwise you won't be able to utilize this query. Better do this dummy row generations in application level. @mubeen

mubeen Over a year ago

@1000111 Thank you for your worry. But query is fine :)

Eduard Uta · Accepted Answer · 2016-03-31 05:22:38Z

1

That is because you are comparing a value with null aka. unknown. Always use the IS operator when comparing to null values. CASE WHEN (u.uid is null) THEN 'not found' ELSE 'found' END) as result

Try this instead (updated answer):

SELECT u2.uid, (CASE WHEN (u1.uid is null) THEN 'not found' ELSE 'found' END) 
as     result
FROM users u1
RIGHT JOIN 
(select 1 as uid union all
 select 2 as uid union all
 select 3 as uid union all
 select 56443 as uid
) u2
on u1.uid = u2.uid

edited Mar 31, 2016 at 5:22

answered Mar 31, 2016 at 5:09

Eduard Uta

2,6165 gold badges30 silver badges39 bronze badges

4 Comments

mubeen Over a year ago

Try it, but same result :( .

user180100 Over a year ago

Nope, sqlfiddle.com/#!9/c38d26/1 because there still is no 56443 in users

mubeen Over a year ago

This also worked! Thank you.. But I can mark only one answer

Eduard Uta Over a year ago

I noticed the other answer. Awesome.. It's fine you got the correct answer. Cheers!

Collectives™ on Stack Overflow

Return null values IN clause mysql

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related