如果部分Union查询不返回任何结果，则整个Union查询不返回任何结果

Question

我已经创建了下面的Union查询，它在大多数情况下都工作得很好：

SELECT [%$##@_Alias].[Contact ID], [%$##@_Alias].[Mailing Name]
FROM (SELECT [Referrals - Contacts Within Organisations].[Contact ID], [Referrals - Contacts Within Organisations].[Mailing Name],  [Referrals - Contacts Within Organisations].[Surname], [Referrals - Contacts Within Organisations].[First name]
FROM [Referrals - Contacts Within Organisations]
UNION SELECT "0" as [Contact ID], "View all contacts" as [Mailing Name], "0" as [Surname], "0" as [First name]
FROM [Referrals - Contacts Within Organisations])  AS [%$##@_Alias]
ORDER BY [%$##@_Alias].Surname, [%$##@_Alias].[First name];

这将在查询返回的任何内容的顶部添加第一行"View all contacts“。

但是，如果它的“实际”查询部分没有返回任何结果，那么整个查询就不会返回任何结果，而我总是希望初始行出现。

这是可能的吗?如果是这样，我做错了什么？谢谢。

编辑:感谢大家的帮助。最后的工作查询如下所示，供其他需要此类功能的人员参考：

SELECT A.[Contact ID], A.[Mailing Name]
FROM (SELECT "0" as [Contact ID], "View all contacts" as [Mailing Name], "0" as [Surname], "0" as [First name]
FROM [Dummy] 
UNION
SELECT [Referrals - Contacts Within Organisations].[Contact ID], [Referrals - Contacts Within Organisations].[Mailing Name],  [Referrals - Contacts Within Organisations].[Surname], [Referrals - Contacts Within Organisations].[First name]
FROM [Referrals - Contacts Within Organisations]) AS A
ORDER BY A.Surname, A.[First name];

Answer 1

要在SQL中始终返回一行数据，即使是手动提供值，也需要从至少包含一行的表中进行选择，我建议使用包含一个随机信息字段的tblDummy。

例如：

SELECT "Hello", "Goodbye"
FROM tblDummy

顺便说一句，我也会试着摆脱：

[%$##@_Alias]

这可以通过添加以下内容为派生表添加别名来实现：

(SELECT * FROM Blah) AS A

例如，将派生表命名为A