返回空值IN子句mysql [英] Return null values IN clause mysql

问题描述

在数据库中找不到数据时找不到打印.例如，在我的数据库中，我没有56443，因此它应该显示'not found'

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;

获得结果如下

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

我也希望not found行与56443

推荐答案

您需要使用其他方法.您将需要使用UNION ALL创建一个包含所有值的内联视图，然后将其与users表保持连接:

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提琴

查询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

[结果] :

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

这篇关于返回空值IN子句mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！