使用Right/Left Join无法显示MasterGroups全量PICGroupName的解决方法

原始查询代码 #

SELECT  
    TRX.TrnNo TerminationID, 
    TRX.FullName EmployeeName, 
    TRX.EmployeeID SN, 
    TRX.Division, 
    TRX.LastDayDate LWD, 
    TRX.EffectiveDate ETD, 
    IIF(TRX.Type = 'TYP1','SAP','TRACE') Type, 
    TRX.Status, 
    MU.EmployeeID PICEmployeeID, 
    MU.Name PICName, 
    MG.Name PICGroupName,
    MG.ID PICGroupID
FROM UserLocation ul
JOIN MasterUsers mu ON ul.xupj = mu.XUPJ
JOIN UserRole UR ON UR.XUPJ = MU.XUPJ
JOIN MasterUserGroups MUG ON MUG.UserXUPJ = MU.XUPJ
CROSS APPLY
(
    SELECT * FROM 
        (
            SELECT Tr.TrnNo, Tr.EmployeeID, tr.FullName, ISNULL(mu.Location, em.Location_Id) LocationID, Division, tr.LastDayDate, tr.EffectiveDate, tr.Type, tr.Status
            FROM EmployeeMaster em
            LEFT JOIN MasterUsers mu on em.employee_id = mu.employeeid
            CROSS APPLY
            (
                SELECT * FROM TrnRequest tr 
                WHERE (tr.[DeletedDate] IS NULL) AND (tr.[Status] IN (N'DRFT', N'NEW')) AND (tr.[Status] IS NOT NULL) AND (tr.[DeletedDate] IS NULL)
                AND em.Employee_Id = tr.EmployeeID
            ) Tr
        ) X WHERE ul.Location_Id = X.LocationID
) TRX
RIGHT JOIN MasterGroups MG ON  MUG.GroupID = MG.ID
WHERE mu.IsActive = 1 AND UR.RoleID = 2
AND TRX.TrnNo IN ('TRM-1007')

问题原因 #

你用了RIGHT JOIN但没得到全量MasterGroups数据，核心原因是：

• WHERE子句里的TRX.TrnNo IN ('TRM-1007')会过滤掉所有没有匹配到TRM-1007交易的行（这些行的TRX.TrnNo是NULL，不满足IN条件）。
• mu.IsActive = 1 AND UR.RoleID = 2同样会过滤掉RIGHT JOIN后没有匹配到有效用户/角色的MasterGroups行，因为这些行的mu和UR字段是NULL，不满足条件。

调整后的查询代码 #

SELECT  
    TRX.TrnNo TerminationID, 
    TRX.FullName EmployeeName, 
    TRX.EmployeeID SN, 
    TRX.Division, 
    TRX.LastDayDate LWD, 
    TRX.EffectiveDate ETD, 
    IIF(TRX.Type = 'TYP1','SAP','TRACE') Type, 
    TRX.Status, 
    MU.EmployeeID PICEmployeeID, 
    MU.Name PICName, 
    MG.Name PICGroupName,
    MG.ID PICGroupID
FROM MasterGroups MG
LEFT JOIN MasterUserGroups MUG ON MG.ID = MUG.GroupID
LEFT JOIN MasterUsers mu ON MUG.UserXUPJ = mu.XUPJ AND mu.IsActive = 1
LEFT JOIN UserRole UR ON mu.XUPJ = UR.XUPJ AND UR.RoleID = 2
LEFT JOIN UserLocation ul ON mu.XUPJ = ul.xupj
OUTER APPLY
(
    SELECT * FROM 
        (
            SELECT Tr.TrnNo, Tr.EmployeeID, tr.FullName, ISNULL(mu_inner.Location, em.Location_Id) LocationID, Division, tr.LastDayDate, tr.EffectiveDate, tr.Type, tr.Status
            FROM EmployeeMaster em
            LEFT JOIN MasterUsers mu_inner on em.employee_id = mu_inner.employeeid
            CROSS APPLY
            (
                SELECT * FROM TrnRequest tr 
                WHERE tr.[DeletedDate] IS NULL 
                  AND tr.[Status] IN (N'DRFT', N'NEW') 
                  AND tr.[Status] IS NOT NULL
                  AND em.Employee_Id = tr.EmployeeID
                  AND tr.TrnNo IN ('TRM-1007') -- 把交易筛选移到这里
            ) Tr
        ) X WHERE ul.Location_Id = X.LocationID
) TRX

关键调整点说明 #

• 调整表关联顺序：把MasterGroups作为主表，用LEFT JOIN关联其他表，逻辑上更直观，确保先获取全量PIC组。
• 将过滤条件移到JOIN的ON子句：
  • mu.IsActive = 1放到MasterUsers的LEFT JOIN条件里，避免过滤掉没有匹配用户的组。
  • UR.RoleID = 2放到UserRole的LEFT JOIN条件里，同理保留无匹配角色的组。
• 把交易筛选移到子查询内部：tr.TrnNo IN ('TRM-1007')直接加到TrnRequest的WHERE条件里，只筛选目标交易，但不会过滤掉没有该交易的组（这些组的TRX字段会显示NULL）。
• 用OUTER APPLY替代CROSS APPLY：CROSS APPLY会过滤掉没有匹配结果的行，OUTER APPLY则保留这些行，对应没有TRM-1007交易的组，TRX相关字段会显示NULL，正好符合你检查覆盖情况的需求。
• 子查询内的MasterUsers别名重命名：避免和外部的mu表冲突，改成mu_inner，防止字段引用混淆。

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