在 SQL Server 中忽略沒有任何結果的連接

情況

我正在進行 SQL 伺服器查詢以過濾項目表*( tblProjecten)*中的數據。該表與其他表（如 region ( tblProjectenRegio)、 organization *( tblProjectenOrganisatie)*等）有很多多對多的關係。

我聲明的所有參數都是可選的。我發現用來coalesce做這個。如果我不使用我的查詢中的聯接，它工作正常，你得到了我首先使用的查詢：

declare @themaid int             = 1        ; -- themeID
declare @trefwoord nvarchar(max) = ''       ; -- search query

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

在我對不同聲明的結果下方*（另請參閱下面的數據）*：

這工作正常，但如果我添加其他條件參數，如下面的查詢。我得到了完全不同的結果。

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 distinct p.* 
from tblProjecten p left join tblProjectenThema pt                 on p.projectId = pt.projectId
                   left join tblProjectenStudiegebieden ps        on p.projectId = ps.projectid
                   left join tblProjectenOpleidingsType pot       on p.projectId = pot.projectID
                   left join tblProjectendoelgroep pd             on p.projectId = pd.projectId
                   left join tblProjectenOrganisator po           on p.projectId = po.projectId
                   left join tblProjectenRegio pr                 on p.projectId = pr.projectId
where pt.themaId        = coalesce(@themaid, pt.themaId)                 and
     ps.studiegebiedid = coalesce(@studiegebiedid, ps.studiegebiedid)   and
     pot.opleidingsID  = coalesce(@opleidingdtypeid, pot.opleidingsID)  and
     pd.doelgroepId    = coalesce(@doelgroepid, pd.doelgroepid)         and
     po.organisatorId  = coalesce(@organisatorid, po.organisatorId)     and
     pr.regioId        = coalesce(@regioid, pr.regioId)                 and
     p.naam like '%' + @trefwoord + '%'                                    ;

以下是結果*（其他聲明是null）*：

這是因為其他表中沒有任何數據。（見下面的數據）

---

問題

我現在的問題是我可以忽略沒有任何結果的連接以使最後一個查詢正常工作嗎？

我也嘗試使用內連接和右連接，但給出相同的結果。

---

數據

這裡有一些數據：

tblProjecten:

tblProjectenThema:

tblProjectendoelgroep:

我一直在閱讀並重新閱讀原始問題。我得到的是這樣的：

從 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
   ;

引用自：https://dba.stackexchange.com/questions/191568