sql - 找不到元素时，如何打印列值的 'NULL' 或 '0' 值？

Question

我需要遍历一组值(小于 10)并查看它们是否在表中。如果是这样，我需要打印出所有记录值，但如果该项目不存在，我仍然希望它包含在打印结果中，尽管具有 NULL 或 0 值。因此，例如，以下查询返回:

select * 
  from ACTOR 
 where ID in (4, 5, 15);

+----+-----------------------------+-------------+----------+------+| ID | NAME                        | DESCRIPTION | ORDER_ID | TYPE |+----+-----------------------------+-------------+----------+------+|  4 | [TEST-1]                    |             |        3 | NULL ||  5 | [TEST-2]                    |             |        4 | NULL |+----+-----------------------------+-------------+----------+------+

但我想让它回来

+----+-----------------------------+-------------+----------+------+| ID | NAME                        | DESCRIPTION | ORDER_ID | TYPE |+----+-----------------------------+-------------+----------+------+|  4 | [TEST-1]                    |             |        3 | NULL ||  5 | [TEST-2]                    |             |        4 | NULL ||  15| NULL                        |             |        0 | NULL |+----+-----------------------------+-------------+----------+------+

这可能吗？

Answer 1

要获得您想要的输出，您首先必须构建一个派生表，其中包含您想要的 ACTOR.id 值。 UNION ALL 适用于小型数据集:

SELECT *
  FROM (SELECT 4 AS actor_id
          FROM DUAL
        UNION ALL
        SELECT 5
          FROM DUAL
        UNION ALL
        SELECT 15
          FROM DUAL) x

有了它，您可以 OUTER JOIN 到实际表以获得您想要的结果:

   SELECT x.actor_id,
          a.name,
          a.description,
          a.orderid,
          a.type
     FROM (SELECT 4 AS actor_id
             FROM DUAL
           UNION ALL
           SELECT 5
             FROM DUAL
           UNION ALL
           SELECT 15
             FROM DUAL) x
LEFT JOIN ACTOR a ON a.id = x.actor_id

如果 x 和 a 之间没有匹配项，则 a 列将为空。因此，如果您希望在 id 15 不匹配时 orderid 为零:

   SELECT x.actor_id,
          a.name,
          a.description,
          COALESCE(a.orderid, 0) AS orderid,
          a.type
     FROM (SELECT 4 AS actor_id
             FROM DUAL
           UNION ALL
           SELECT 5
             FROM DUAL
           UNION ALL
           SELECT 15
             FROM DUAL) x
LEFT JOIN ACTOR a ON a.id = x.actor_id