Clever Geek Handbook

Return null IN values ​​in mysql

No print found when there is no data in the database. For example there is no in my database 56443

, so 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 the result like this

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

I also expect not found

row with56443

+2


source to share


2 answers


You need to take a different approach. You will need to create an inline view with all values ​​using UNION ALL and then join it with a list of users:

SQL Fiddle

Request 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

<strong> [Results]:

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


source


This is because you are comparing a value to a null value. unknown. Always use the IS operator when comparing against null values. CASE WHEN (u.uid 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
+1


source






More articles:


All Articles