对于IN列表中的缺失值，返回NULL [英] Return NULL for missing values in an IN list

问题描述

我有一个这样的表:

 id | val
 ---------
  1 | abc
  2 | def
  5 | xyz
  6 | foo
  8 | bar

和类似的查询

 SELECT id, val FROM tab WHERE id IN (1,2,3,4,5)

返回

 id | val
 ---------
  1 | abc
  2 | def
  5 | xyz

有没有办法让它在缺少ID的情况下返回NULL，即

Is there a way to make it return NULLs on missing ids, that is

 id | val
 ---------
  1 | abc
  2 | def
  3 | NULL
  4 | NULL
  5 | xyz

我想应该有一个棘手的LEFT JOIN，但不能将我的头缠住它.

I guess there should be a tricky LEFT JOIN with itself, but can't wrap my head around it.

我看到人们在想我要按顺序填补空白"，但实际上我想要的是将NULL替换为IN列表中的缺失值.例如，这个

I see people are thinking I want to "fill the gaps" in a sequence, but actually what I want is to substitute NULL for the missing values from the IN list. For example, this

 SELECT id, val FROM tab WHERE id IN (1,100,8,200)

应该返回

 id | val
 ---------
  1 | abc
100 | NULL
  8 | bar
200 | NULL

此外，顺序并不重要.

只需添加几个相关链接:

Just adding a couple of related links:

• 如何选择由常量填充的多行?
• 是否有可能没有桌子选择多行吗?

• How to select multiple rows filled with constants?
• Is it possible to have a tableless select with multiple rows?

推荐答案

您可以使用此技巧:

SELECT v.id, t.val
FROM
  (SELECT 1 AS id
   UNION ALL SELECT 2
   UNION ALL SELECT 3
   UNION ALL SELECT 4
   UNION ALL SELECT 5) v
  LEFT JOIN tab t
  ON v.id = t.id

请在此处看到小提琴.

这篇关于对于IN列表中的缺失值，返回NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！