含JOIN与UNION的WHERE语句查询结果不符，如何筛选指定日期？

你的查询问题出在第一个SELECT语句没有添加日期过滤条件，导致RIGHT JOIN会返回Gridhour表的所有小时记录，以及AppointmentGrid表中所有日期（而非仅2018-03-21）的匹配记录，最终和第二个SELECT的结果合并后，出现了其他日期的数据。

修正后的SQL写法 #

这里提供两种可行的修正方案：

方案1：给两个SELECT都添加日期过滤条件

SELECT 
    t1.HistoryID, 
    t1.CustomerID, 
    CONVERT(VARCHAR(5), t2.hour, 108) AS Hour,
    t1.Text1, 
    t1.Text2, 
    t1.HistoryID2, 
    t1.CustomerID2, 
    t1.AppointmentDate 
FROM AppointmentGrid t1 
RIGHT JOIN Gridhour t2 ON t1.AppointmentHour = t2.Hour
WHERE t1.AppointmentDate = '2018-03-21' OR t1.AppointmentDate IS NULL -- 保留当天无预约的小时记录
UNION
SELECT 
    t1.HistoryID, 
    t1.CustomerID, 
    CONVERT(VARCHAR(5), t1.AppointmentHour, 108) AS Hour,
    t1.Text1, 
    t1.Text2, 
    t1.HistoryID2, 
    t1.CustomerID2, 
    t1.AppointmentDate 
FROM AppointmentGrid t1 
LEFT JOIN Gridhour t2 ON t1.AppointmentHour = t2.Hour
WHERE t1.AppointmentDate = '2018-03-21';

方案2：将UNION结果整体过滤（更简洁）

SELECT * FROM (
    SELECT 
        t1.HistoryID, 
        t1.CustomerID, 
        CONVERT(VARCHAR(5), t2.hour, 108) AS Hour,
        t1.Text1, 
        t1.Text2, 
        t1.HistoryID2, 
        t1.CustomerID2, 
        t1.AppointmentDate 
    FROM AppointmentGrid t1 
    RIGHT JOIN Gridhour t2 ON t1.AppointmentHour = t2.Hour
    UNION
    SELECT 
        t1.HistoryID, 
        t1.CustomerID, 
        CONVERT(VARCHAR(5), t1.AppointmentHour, 108) AS Hour,
        t1.Text1, 
        t1.Text2, 
        t1.HistoryID2, 
        t1.CustomerID2, 
        t1.AppointmentDate 
    FROM AppointmentGrid t1 
    LEFT JOIN Gridhour t2 ON t1.AppointmentHour = t2.Hour
) AS CombinedResults
WHERE AppointmentDate = '2018-03-21' OR AppointmentDate IS NULL;

关键说明 #

• 方案1中，第一个SELECT的WHERE条件加上OR t1.AppointmentDate IS NULL是为了保留当天没有对应预约的小时记录（如果你的需求是显示当天所有时段，不管有没有预约）；如果只需要显示有预约的时段，可以去掉这个OR条件。
• 用UNION会自动去重，如果你的场景不需要去重，可以改用UNION ALL，性能会更好。

内容的提问来源于stack exchange，提问作者Dim