T-SQL如何编写条件连接

Question

我有一个存储过程和一些参数。我想编写查询语句，使其与某些表联接，但仅在特定参数具有值时进行。以以下示例为例：我有一个人员表。还有一个地址表，其中保存人员地址，以及一个保存人员分组的组表。两者都是与人员表的一对多关系。我的存储过程具有@ AddressID参数和@ GroupID参数。

查询始终只返回Person表中的字段。如果没有任何参数具有值，则查询应从Person表返回所有记录。如果提供@ AddressID参数，则它应仅返回在Address表中具有匹配记录的记录，并忽略Groups表。如果提供了@ GroupID参数，则它应仅返回在Groups表中具有匹配记录的记录，并忽略Addresses表。如果两个参数都提供了，则它只应显示在两个表中具有匹配记录的记录。明白了吗？

有没有我错过的简单方法？

谢谢， Corey

- Corey Burnett

---

谢谢您的提问，没有其他人问过这个问题。例如，在使用LEFT JOIN时，这非常有用。 - Jorge

Answer 1

如果我理解正确，您的连接条件应该等同于以下代码：

ON ((@AddressID不为空) AND (alias.column = @AddressID))

对于组连接也是如此。

我有时会使用这种条件连接方式。

- Quintin Robinson

---

这样正确吗？如果@AddressID为空，内连接将不返回任何内容。 - hidarikani

@hidarikani，也许你可以试试我的解决方案？https://dev59.com/rHI-5IYBdhLWcg3wcn7m#21694802 - Irawan Soetomo

如果 @AddressID 为空，则不正确。将不返回任何内容。 - RayLoveless

运行得很好，比我之前使用的动态语句要好得多。 - Todd Skelton

当 @AddressID 为空时，你应该使用“左外连接（LEFT OUTER JOIN）”而不是“内连接（INNER JOIN）”来返回值。 - Mahdi Ataollahi

Answer 2

简单的方法实际上并不是好的解决方案。尽管听起来很糟糕，但最佳解决方案是在代码中使用明确的IF并分离查询：

IF (condition) 
  SELECT ... FROM Person WHERE ...
ELSE IF (otherCondition)
  SELECT ... FROM Person JOIN ... ON ... WHERE ...
ELSE IF (moreCondition)
  SELECT ... FROM Persons JOIN ... JOIN ... WHERE ...

这是因为如果你试图构建一个匹配所有三个（或更多）条件的单一查询，那么引擎必须产生一个适用于所有条件的单一查询计划。在T-SQL中，一个语句等同于一个计划。请记住，计划是为通用情况，任何变量值而创建的，因此结果总是非常糟糕的计划。

虽然这看起来违反直觉，对于任何程序员来说都是一个可怕的解决方案，但这就是数据库的工作方式。这不是问题的原因是99.99%的时间开发人员会根据实际需求修订他们的要求，以便永远不必根据运行时变量值进行可选连接查询。

- Remus Rusanu

---

或许令人惊讶的是，LINQ2SQL 更适合这样的情况，因为查询表达式的本质是完整的对象，可以被操纵和精细化。请参见 http://stackoverflow.com/questions/1849005/converting-conditionally-built-sql-where-clause-into-linq/1849041#1849041。 - Remus Rusanu

如果您有互斥的标准，那就没问题了，但大多数搜索屏幕并非如此。这会增加维护开销 - 如果JOIN更改，则必须对所有副本进行更改。在这种情况下，动态SQL是更现实的解决方案。 - OMG Ponies

@Pony：是的，对于复杂条件来说，动态SQL更好。 - Remus Rusanu

然而，有些情况下只使用单个SELECT语句会更快，例如当您使用内联表值函数SELECT（更快）与带有IF的多语句表值函数（较慢）时。 - CGodo

Answer 3

是的，非常简单。在地址和组上执行左连接，然后在where子句中...

(@group_id is null or g.group_id = @group_id)
and (@address_id is null or a.address_id = @address_id)

- dotjoe

---

这将会给出正确的答案，但是在连接中使用OR条件会导致性能变慢。 - Eric Ness

Answer 4

你应该能够在此基础上进行扩展...

DECLARE @SQL varchar(max)

    SET @SQL = 'SELECT * FROM PERSON P'

    IF NULLIF(@ADDRESSID,"") IS NULL SET @SQL = @SQL + " INNER JOIN ADDRESSES A ON P.AddressID = A.AddressID"

    EXEC sp_executesql @SQL, N'@ADDRESSID int', @ADDRESSID

- Paul Creasey

---

不，大多数情况下它是魔鬼的孩子，只有在没有任何真正替代品的情况下才应该使用。仅仅为了节省一行SQL代码并不算。你的动态SQL将生成一个效率较低的查询计划，极大地复杂化了任何升级或审计，并且最重要的是使你容易受到SQL注入攻击。大多数时候这是因为懒惰。 - MartW

如果你使用sp_executesql并利用查询计划缓存，动态SQL并不慢。 - Paul Creasey

Paul，它不好的原因还包括可读性差、使用exec以及总是存在SQL注入的风险。 - JonH

叹气，祝你好运将 SQL 注入到那个整数参数中，我很想看看... - Paul Creasey

大多数情况下，它是魔鬼的产物。使用它的999/1000次，都是错误和危险的。而且查询计划不仅会因为参数包含实际SQL而改变 - 如果参数的性质（如上所述）影响到SQL运行的本质，它也会改变。在这种有限的情况下，它不太可能成为问题。虽然以上方法可能安全防止注入攻击，但我仍然不喜欢它干扰审计和权限复杂性。但我认为让我反感的是短语“动态SQL很棒！”- “动态SQL非常强大！”会更好。 - MartW

Answer 5

这是我为我的案例所做的事情。

DECLARE
    @ColorParam varchar(500)

SET
    @ColorParam = 'red, green, blue'

declare @Colors table
(
    Color NVARCHAR(50) PRIMARY KEY
)

-- populate @Colors table by parsing the input param, 
-- table can be empty if there is nothing to parse, i.e.: no condition
INSERT @Colors SELECT Value FROM dbo.Splitter(@ColorParam, ',')

SELECT
    m.Col1,
    c.Color
FROM
    MainTable AS m
FULL JOIN -- instead of using CROSS JOIN which won't work if @Colors is empty
    @Colors AS c
ON
    1 = 1 -- the trick
WHERE
    (@ColorParam IS NULL OR c.Color = m.Color)
    

- Irawan Soetomo

---

完全连接会在主表列中返回一堆空值，其中ID不匹配。您需要使用左连接。 - RayLoveless

Answer 6

嗯，可能你们都已经解决了这个问题。

就我理解的来说，你想要一个“动态”的查询，如果参数存在，则连接表，如果参数为空，则省略连接。秘密在于使用左外连接。像这样：

SELECT p.*
FROM Parent AS p
LEFT OUTER JOIN Child AS c ON p.Id = c.ParentId
WHERE
        (@ConditionId IS NULL OR c.ConditionId = @ConditionId)

这是如何工作的？

• 如果过滤参数@ConditionId为空，则外连接没有子项，结果将具有所有父项。
• 如果过滤参数@ConditionId不为空，则外连接将使用此父项连接子项，并使用条件(@ConditionId IS NULL OR c.ConditionId = @ConditionId)排除未使用条件c.ConditionId = @ConditionId连接子项的父项。

左外连接肯定存在性能问题，但只要它可以快速工作，我就不想连接字符串来创建查询。

- vllado2

Answer 7

左连接和where子句应该能解决问题：

SELECT Customers.CustomerName, Customers.Country, Orders.OrderID
    FROM Customers
    LEFT JOIN Orders
    ON Customers.CustomerID=Orders.CustomerID
    WHERE Country= @MyOptionalCountryArg or @MyOptionalCountryArg is null;

- RayLoveless

Answer 8

Quntin发表的内容很好，但是它存在一些性能问题。信不信由你，更快的方法是检查每个参数并根据情况编写SQL Join。

此外：

IF @AddressParameter IS NOT NULL
BEGIN
SELECT blah1, blah2 FROM OneTable INNER JOIN AddressTable WHERE ....
-more code
END
ELSE...
BEGIN
END
...

你可以做的另一件事是在查询过滤器（where子句）中执行连接，例如：

WHERE
(Address = @Address OR @Address IS NULL)

这里的性能也不太可靠。

- JonH

---

这在这种情况下并不会真正起作用。我有大约9个参数，它们可能为空，也可能不为空。所有这些不同参数的可能组合将像9的阶乘或某个巨大的数字一样。 - Corey Burnett

我不确定我理解你的意思，Corey。你要做的就是在n个参数的WHERE子句中处理它。此外， WHERE ((Address = @Address OR @Address IS NULL) AND (Group = @Group OR @Group IS NULL) AND (...)) - JonH

一个简单的连接操作会排除记录。它需要使用左连接。 - RayLoveless

Answer 9

将三个表连接在一起，并在WHERE子句中使用类似以下的语句：

WHERE Addresses.ID = COALESCE(@AddressID, Addresses.ID)
AND  Groups.ID = COALESCE(@GroupID, Groups.ID)

- JML

---

一个简单的连接会排除记录。我相信它需要进行左连接。 - RayLoveless