返回空值IN子句mysql

Question

在数据库中找不到数据时无法打印。例如，在我的数据库中，我没有56443，因此它应该打印'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

Answer 1

您需要使用不同的方法。您需要使用UNIONALL创建一个包含所有值的内联视图，然后将其与users表左联接：

SQL Fiddle

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

Answer 2

这是因为您将一个值与null aka进行比较。未知的与零值进行比较时，请始终使用IS运算符。CASE WHEN（u.uid为null）THEN"not found"ELSE"found"END）作为结果

试试这个（更新的答案）：

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