在 SQL Server 中忽略没有任何结果的连接

我一直在阅读并重新阅读原始问题。我得到的是这样的：

从 tblProjecten 返回记录，其中已提供的参数存在匹配项（不为空）。如果未提供参数（或为空），则假定该子表的所有匹配项

实际上，您正在根据参数是否具有提供的值来动态连接。

为此，您可以使用：

declare @themaid int             = 1        ;
declare @studiegebiedid int      = null     ;
declare @opleidingdtypeid int    = null     ;
declare @doelgroepid int         = null     ;
declare @organisatorid int       = null     ;
declare @regioid int             = null     ;
declare @trefwoord nvarchar(max) = ''       ;

select p.* 
from tblProjecten p 
where p.naam like '%' + @trefwoord + '%'
and ( 
        @themaid is null
    or exists(select 1 from tblProjectenThema pt where p.projectId = pt.projectId and pt.themaId = @themaid)
)
and (
        @studiegebiedid is null
    or exists(select 1 from tblProjectenStudiegebieden ps where p.projectId = ps.projectid and ps.studiegebiedid = @studiegebiedid
)
and (
        @studiegebiedid is null
    or exists(select 1 from tblProjectenOpleidingsType pot where p.projectId = pot.projectID and ps.studiegebiedid = @studiegebiedid
)
and (
        @opleidingdtypeid is null
    or exists(select 1 from tblProjectendoelgroep pd where p.projectId = pd.projectId and pd.doelgroepId = @doelgroepid
)
and (
        @organisatorid is null
    or exists(select 1 from tblProjectenOrganisator po where p.projectId = po.projectId and po.organisatorId  = @organisatorid
)
and (
        @regioid is null
    or exists(select 1 from tblProjectenRegio pr where p.projectId = pr.projectId and pr.regioId = @regioid
)

我已经改变了一些事情。我们不是在做连接，而是在做EXISTS一个相关的子查询。

每个评价如下：

• 如果 @parameter 为 null 返回 true
• 或者...如果@parameter 不为空，则检查子表上是否存在该@parameter 的匹配记录。如果有，返回true

通过这样做，我们的连接实际上变成了可选的（取决于我们是否有参数值）。

如果我的初衷是正确的，那么一个简单的假设是您可能想要使用内部连接，例如：

select distinct p.* 
from tblProjecten p
inner join tblProjectenThema pt on p.projectId = pt.projectId
...etc...
where p.naam like '%' + @trefwoord + '%'
and (pt.themaId = coalesce(@themaid))

您不能这样做的原因是 tblProjectenThema 可能为空，因此无法匹配任何行。你总是会得到一个空的结果。

替代方法

由于需要考虑更多表，因此上述查询将开始受到性能影响。您可以OPTION(RECOMPILE)针对查询使用。因为您需要可选的动态参数，所以也可以使用动态 SQL 来提供服务。

declare @themaid int             = 1        ;
declare @studiegebiedid int      = null     ;
declare @opleidingdtypeid int    = null     ;
declare @doelgroepid int         = null     ;
declare @organisatorid int       = null     ;
declare @regioid int             = null     ;
declare @trefwoord nvarchar(max) = ''       ;

select @trefwoord = '%' + @trefwoord + '%';
declare @sql nvarchar(max);

select @sql = '
select p.* 
from tblProjecten p 
where p.naam like @trefwoord
'

if @themaid is not null
select @sql += 'and exists(select 1 from tblProjectenThema pt where p.projectId = pt.projectId and pt.themaId = @themaid)'

if @studiegebiedid is not null 
select @sql += 'and exists(select 1 from tblProjectenStudiegebieden ps where p.projectId = ps.projectid and ps.studiegebiedid = @studiegebiedid)'

if @opleidingdtypeid is not null
select @sql += 'and exists(select 1 from tblProjectendoelgroep pd where p.projectId = pd.projectId and pd.doelgroepId = @doelgroepid)'

if @organisatorid is not null
select @sql += 'and exists(select 1 from tblProjectenOrganisator po where p.projectId = po.projectId and po.organisatorId  = @organisatorid)'

if @regioid is not null
select @sql = +='and exists(select 1 from tblProjectenRegio pr where p.projectId = pr.projectId and pr.regioId = @regioid)'

exec sp_executesql 
    @stmt = @sql
    ,@params = N'@trefwoord nvarchar(max), @themaid int, @studiegebiedid int, @opleidingdtypeid int, @doelgroepid int, @organisatorid int, @regioid int'
    ,@trefwoord = @trefwoord
    ,@themaid = @themaid
    ,@studiegebiedid = @studiegebiedid
    ,@opleidingdtypeid = @opleidingdtypeid
    ,@doelgroepid = @doelgroepid
    ,@organisatorid = @organisatorid
    ,@regioid = @regioid
    ;

我认为您使用的LEFT连接不正确。现在查询的方式是，子句中涉及每个表的列WHERE，连接有效地充当INNER连接，而不是LEFT连接。正如@Kent 在评论中解释的那样：

当条件在连接语句（ON部分）中时，限制在连接之前应用。当它在 中时WHERE，它会在连接发生后应用（因此，如果您对正确的表设置条件，则连接有效地成为内部连接）。

现在，我不确定到底想要什么。

案例 A（可能不是想要的）。如果您还想从其他表中获取信息，那么您应该：

• 将条件从WHERE相应的ON条件移动
• 摆脱COALESCE()功能
• （奖金）摆脱DISTINCT

查询将类似于：

select p.*
    -- pt.*, ps.*, ... 
from tblProjecten p 
    left join tblProjectenThema pt
        on  p.projectId = pt.projectId
        and pt.themaId = @themaid 
    left join tblProjectenStudiegebieden ps
        on  p.projectId = ps.projectid
        and ps.studiegebiedid = @studiegebiedid
    left join tblProjectenOpleidingsType pot
        on  p.projectId = pot.projectID
        and pot.opleidingsID = @opleidingdtypeid
    left join tblProjectendoelgroep pd
        on  p.projectId = pd.projectId
        and pd.doelgroepId = @doelgroepid
    left join tblProjectenOrganisator po
        on  p.projectId = po.projectId
        and po.organisatorId = @organisatorid
    left join tblProjectenRegio pr
        on  p.projectId = pr.projectId
        and pr.regioId = @regioid
where  
      p.naam like '%' + @trefwoord + '%'
 ;

案例B（我认为是这样）。如果你想要“可选参数”，即当参数为空时不限制结果，那么你应该：

• 将连接和WHERE条件转换为EXISTS子查询
• 摆脱COALESCE()功能并使用OR @parameter IS NULL）
• （奖金）摆脱DISTINCT

查询将类似于：

select p.*
from tblProjecten p 
where 
      p.naam like '%' + @trefwoord + '%'
  and ( @themaid is null
      or exists
          ( select * from tblProjectenThema pt
            where p.projectId = pt.projectId
              and pt.themaId = @themaid
          )
      ) 
  and ( @studiegebiedid is null
      or exists
          ( select * from tblProjectenStudiegebieden ps
            where p.projectId = ps.projectId
              and ps.studiegebiedid = @studiegebiedid
          )
      ) 
  -- and ...
 ;