sql-server - 如果 SQL Server CASE 值为 NULL，则不返回任何内容

Question

我有一个函数，可以根据某些条件返回多行数据和一列数据。

ALTER FUNCTION [dbo].[GetFavoriteFruits]
(   
    @FruitId uniqueidentifier
)
RETURNS TABLE 
AS
RETURN 
(
    Select Name FROM Fruits WHERE FruitID = @FruitId
    UNION ALL
    Select Name FROM Vegetables WHERE VegetableID = @FruitId
    UNION ALL
    Select
    CASE
    WHEN EXISTS(Select Name FROM Fruits WHERE FruitID = @FruitId) THEN 'Fruit Exists'
    END
    UNION ALL
    Select
    CASE
    WHEN EXISTS(Select Name FROM Vegetables WHERE VegetableID = @FruitId) THEN 'Vegetable Exists'
    END
    UNION ALL
    Select
    CASE
    WHEN EXISTS(Select Name FROM Fruits WHERE FruitID = @FruitId) OR EXISTS(Select Name FROM Vegetables WHERE VegetableID = @FruitId) THEN 'Either Fruit or Vegetable exists'
    END
)

当表中存在水果和蔬菜的值时，一切都很好。但如果前两种情况的值不存在，则返回的输出(来自最后 3 个查询)为

NULL
NULL
NULL

有没有办法可以避免这些 NULL 并且不返回与前两个查询类似的任何内容。

Answer 1

我绝不希望这是经过确认的答案，我发布此内容只是为了向您展示上述评论的答案:

 ALTER FUNCTION [dbo].[GetFavoriteFruits]
(   
    @FruitId uniqueidentifier
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT x.Name FROM (
    Select Name FROM Fruits WHERE FruitID = @FruitId
    UNION ALL
    Select Name FROM Vegetables WHERE VegetableID = @FruitId
    UNION ALL
    Select
    CASE
    WHEN EXISTS(Select Name FROM Fruits WHERE FruitID = @FruitId) THEN 'Fruit Exists'
    END
    UNION ALL
    Select
    CASE
    WHEN EXISTS(Select Name FROM Vegetables WHERE VegetableID = @FruitId) THEN 'Vegetable Exists'
    END
    UNION ALL
    Select
    CASE
    WHEN EXISTS(Select Name FROM Fruits WHERE FruitID = @FruitId) OR EXISTS(Select Name FROM Vegetables WHERE VegetableID = @FruitId) THEN 'Either Fruit or Vegetable exists'
    END
) ) AS x
WHERE x.Name IS NOT NULL