我不能用这些查询得到相同的结果

Question

我需要从SQL Server 2008转换此查询：

select distinct 
    i.art, i.des1, isnull(l.porc_tol, 0) as porc_tol, 
    0.0, a.cia, a.alm, 
    i.art, i.lin, i.s_lin, i.fam, i.s_fam, i.marca, a.cve_pro 
from
    invsas v (nolock), 
    invars s (nolock), 
    inviar i (nolock), 
    invart a (nolock), 
    invtol l (nolock) 
where
    v.cia = 'DEM' 
    and v.cve_suc = '001' 
    and s.cia = v.cia 
    and s.alm = v.alm 
    and s.sub_alm = v.cve 
    and i.art = s.cve_art
    and a.cia = s.cia 
    and a.alm = s.alm 
    and a.art = i.art 
    and l.cia =* s.cia 
    and l.suc =* v.cve_suc 
    and l.cve_art =* i.art 

到SQL Server 2012。我做了以下更改：

SELECT DISTINCT
    i.art, i.des1, ISNULL(l.porc_tol, 0) as porc_tol, 
    0.0, a.cia, a.alm, 
    i.art, i.lin, i.s_lin, i.fam, i.s_fam, i.marca, a.cve_pro 
FROM
    invart a (nolock),
    invtol l (nolock) 
RIGHT OUTER JOIN 
    invars s ON l.cia = s.cia
RIGHT OUTER JOIN 
    invsas v on l.suc = v.cve_suc
RIGHT OUTER JOIN 
    inviar i on l.cve_art = i.art
WHERE
    v.cia = 'DEM' 
    AND v.cve_suc = '001' 
    AND s.cia = v.cia 
    AND s.alm = v.alm 
    AND s.sub_alm = v.cve 
    AND i.art = s.cve_art
    AND a.cia = s.cia 
    AND a.alm = s.alm 
    AND a.art = i.art 

但是，当我运行这两个查询时，我得到了不同的结果。会出什么问题呢？

Answer 1

惟一可能“产生”NULL值的表是表l，它参与了与其他几个表的外连接。在您的版本中，还可以在from子句中使用逗号分隔符，并在where子句中使用联接条件。这实际上是来自两个世界的混合语法。对于确定哪个是外部联接的作用域，这可能会造成混淆。

我建议使用这种“转换”，即除了最后一个表之外的所有表都是内部连接的，然后使用外部连接将最后一个表包括在查询中：

select distinct 
           i.art, i.des1, isnull(l.porc_tol,0) as porc_tol, 0.0, a.cia, 
           a.alm, i.art, i.lin, i.s_lin, i.fam, i.s_fam, i.marca, a.cve_pro 
from       invsas v (nolock)
inner join invars s (nolock) 
        on s.cia = v.cia 
       and s.alm = v.alm 
       and s.sub_alm = v.cve
inner join inviar i (nolock)
        on i.art = s.cve_art
inner join invart a (nolock)
        on a.cia = s.cia 
       and a.alm = s.alm 
       and a.art = i.art
left join  invtol l (nolock)
        on l.cia = s.cia 
       and l.suc = v.cve_suc 
       and l.cve_art = i.art  
where      v.cia = 'DEM' 
       and v.cve_suc ='001'