一对多值表关联查询无匹配项时如何默认返回单行结果

问题根源 #

你遇到的两个问题的成因非常明确：

1. 查询1使用内连接关联ContactLine表，只有满足「工作邮箱」过滤条件的关联行才会保留，自然过滤掉了无匹配工作邮箱的用户。
2. 查询2虽然将ContactLine的关联改为左连接，但User表和UserContact表是1对多的关联关系，一个用户对应多条联系人记录，即使邮箱匹配不到，也会按UserContact的行数返回多行，最终出现重复结果。

解决方案 #

因为你不想使用DISTINCT避免误删有效重复数据，最稳妥的思路是先把每个用户对应的工作邮箱整理为1对1的结构，再和用户表关联，以下两种写法兼容性和灵活性都较高：

方案1：关联预聚合子查询（全数据库兼容） #

先通过子查询按用户ID聚合得到每个用户的工作邮箱，确保一个用户ID仅对应一行数据，再和用户表左关联，完全不会产生重复行：

SELECT 
    u.UserName,
    wc.WorkEmail AS Email
FROM Usr u
LEFT JOIN (
    -- 子查询聚合得到每个用户的工作邮箱，1行对应用户1个ID
    SELECT 
        uc.UserId,
        MAX(c.Value) AS WorkEmail 
        -- 若有多个工作邮箱的取值规则，可调整聚合逻辑，比如按更新时间取最新可用KEEP语法
    FROM UserContact uc
    INNER JOIN ContactLine c 
        ON c.ContactNbr = uc.ContactNBr 
        AND c.ContactType = 'Email' 
        AND c.ContactCategory = 'Work'
    GROUP BY uc.UserId
) wc ON u.UserId = wc.UserId

方案2：标量子查询（支持Oracle、PostgreSQL、SQL Server等主流数据库） #

如果需要关联多个不同类型的联系方式，用标量子查询的写法更灵活，不会产生多表关联的笛卡尔积问题：

SELECT 
    u.UserName,
    (
        SELECT c.Value
        FROM UserContact uc
        INNER JOIN ContactLine c 
            ON c.ContactNbr = uc.ContactNBr 
            AND c.ContactType = 'Email' 
            AND c.ContactCategory = 'Work'
        WHERE uc.UserId = u.UserId
        -- 可自定义排序规则取优先级最高的邮箱，比如按联系人ID升序取第一个
        ORDER BY uc.ContactNBr ASC
        FETCH FIRST 1 ROW ONLY
    ) AS Email
FROM Usr u

两种写法都不需要用DISTINCT，也不会干扰你后续新增其他关联表和字段的逻辑。

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