左连接未按预期拉出空值

Question

我有一个看起来像这样的查询（我已经更改了表名称）：

select @user_id
,      isnull(ur.rule_value, isnull(manr.rule_value, def.rule_value)) [rule_value]
,      isnull(urt.name, isnull(manrt.name, def.name)) [rule_type]
from   (select @user_id [user_id]
        , rule.rule_value
        , rule_type.name
        from rule
        join rule_type on rule_type.rule_type_id = rule.rule_type_id
        where rule.user_id = 1) def
join   user on user.user_id = def.user_id
join   manager man on man.manager_id = user.manager_id
left join rule ur on ur.user_id = user.user_id
left join rule_type urt on urt.rule_type_id = ur.rule_type_id
left join rule manr on manr.manager_id = man.manager_id
left join rule_type manrt on manrt.rule_type_id = manr.rule_type_id

我期望的是，当没有针对用户或用户经理的规则时，则应使用默认规则。但是，只有当用户有规则时我才会得到结果。

我已经尝试过左连接所有内容，但没有成功，并且 def 表的 select 语句返回了所有默认规则。

我做错了什么？

@user_id 是一个变量。

更新

架构示例

rule
rule_id user_id manager_id rule_value
1       1       1           27
2       1       1           24
3       1       1           25
4       1       1           44
5       1       1           88
1       2       4           2
2       2       4           23
3       2       4           18
3       NULL    4           19
4       NULL    4           20
5       NULL    4           21


rule_type
rule_id name
1       'Craziness'
2       'Number of legs'
3       'Hair ranking'
4       'Banana preference'
5       'Rule 5'

user
user_id manager_id ... other columns
1       1
2       4
3       4

manager
manager_id ... other columns
1
2
3
4
5
6

因此，如果 @user_id 是 2 那么我期望输出

2, 2, 'Craziness'
2, 23, 'Number of legs'
2, 18, 'Hair ranking'
2, 20, 'Banana preference'
2, 21, 'Rule 5'

但是如果 @user_id 是 < code>3 那么我期望输出

3, 27, 'Craziness'
3, 24, 'Number of legs'
3, 19, 'Hair ranking'
3, 20, 'Banana preference'
3, 21, 'Rule 5'

Answer 1

常规连接实际上是左内连接。您真正想要的是一个外部联接，它可以获取结果，即使它无法将其与另一个表联接。

Answer 2

要回答标题中的问题，左连接的作用与常规连接不同。 LEFT JOIN 与 LEFT OUTER JOIN 相同，意味着也会返回 LEFT 表中不符合 ON 条件的记录。

Answer 3

摆脱这个 :

left join rule ur on ur.user_id = user.user_id
left join rule_type urt on urt.rule_type_id = ur.rule_type_id
left join rule manr on manr.manager_id = man.manager_id
left join rule_type manrt on manrt.rule_type_id = manr.rule_type_id

并用这个 : 替换它，

left join rule ur on ur.user_id = user.user_id or ur.user_id = man.manager_id
left join rule_type urt on urt.rule_type_id = ur.rule_type_id

然后将你的开始 SELECT 更改为 :

select @user_id
,      isnull(ur.rule_value, def.rule_value) [rule_value]
,      isnull(urt.name, def.name) [rule_type]

本质上你做错的是你有一个实体（用户经理默认）并且你试图将它链接到另一个实体（规则）通过两个不同的连接。如果一个联接不起作用，但另一个联接起作用，则不会返回匹配的 NULL 供 ISNULL 查找。这有点违反直觉。

Answer 4

我已将查询分为两部分。

我认为因为表结构比我在原始问题中提出的更复杂，这意味着 CodeByMoonlight 的答案还不够 - 它给了我无法解释的重复行。

我的解决方案如下（关于我在问题中给出的细节）：

create table #user_rules
(
 user_id int,
 rule_value int,
 rule_type varchar(255),
 rule_type_id,
)
--Insert default values
insert into #user_rules
select @user_id [user_id]
    , rule.rule_value
    , rule_type.name
    , rule_type.rule_type_id
from rule
join rule_type on rule_type.rule_type_id = rule.rule_type_id
where rule.user_id = 1
--Update table with any available values
update #user_rules
set rule_value = ur.rule_value
from user u
join manager m on u.manager_id = m.manager_id
join rule ur on ur.user_id = u.user_id or (ur.manager_id = man.manager_id and ur.user_id is null)
join rule_type urt on urt.rule_type_id = ur.rule_type_id
where urt.rule_type_id = #urse_rules.rule_type_id
and u.user_id = @user_id