mysql - MySql，如果没有返回行，则返回默认值

我有一个问题如下

SELECT cap_id FROM cap_master WHERE
        (cap_type = 'Type1' AND cap_desc = 'CapDesc1')
     OR (cap_type = 'Type2' AND cap_desc = 'CapDesc2')
     OR (cap_type = 'Type3' AND cap_desc = 'CapDesc3')
     OR (cap_type = 'Type4' AND cap_desc = 'CapDesc4')
order by cap_type,cap_desc

这将根据where条件返回多行，我要查找的是不返回任何行的条件，我应该有一个默认值，即“0”。到现在为止，我还没有得到任何的争吵。
例如，如果第三个条件（cap_type='Type3'和cap_desc='CapDesc3'）不匹配，我希望输出如下：

23
34
0
45

我检查了给出的解决方案，比如
Return a value if no rows match
Return Default value if no row found -mysql
但似乎它们不能处理返回的多行。任何指点都将非常感谢。
这里有一个Fiddle可以玩。

最佳答案

你想要一个left join：

select coalesce(cm.cap_id, 0) as cap_id
from (select 'Type1' as cap_type, 'CapDesc1' as cap_desc union all
      select 'Type2' as cap_type, 'CapDesc2' as cap_desc union all
      select 'Type3' as cap_type, 'CapDesc3' as cap_desc union all
      select 'Type4' as cap_type, 'CapDesc4' as cap_desc
     ) c left join
     cap_master cm
     on cm.cap_type = c.cap_type and cm.cap_desc = c.cap_desc
order by c.cap_type, c.cap_desc;

如果需要支持NULLcap_desc（这不是原始问题的一部分），可以执行以下操作：

select coalesce(cm.cap_id, 0) as cap_id
from (
      select 'Type5' as cap_type, null as cap_desc
     ) c left join
     cap_master cm
     on cm.cap_type = c.cap_type and
        (cm.cap_desc = c.cap_desc or cm.cap_desc is null and c.cap_desc is null)
order by c.cap_type, c.cap_desc;

Here是一个SQL小提琴。