假设我有以下的T-SQL代码:
SELECT * FROM Foo f
INNER JOIN Bar b ON b.BarId = f.BarId;
WHERE b.IsApproved = 1;
以下代码也会返回相同的一组行:SELECT * FROM Foo f
INNER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId);
这可能不是最好的示例,但这两者之间有性能差异吗?
假设我有以下的T-SQL代码:
SELECT * FROM Foo f
INNER JOIN Bar b ON b.BarId = f.BarId;
WHERE b.IsApproved = 1;
以下代码也会返回相同的一组行:SELECT * FROM Foo f
INNER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId);
这可能不是最好的示例,但这两者之间有性能差异吗?
只需小心与外连接的差异。当在JOIN
的ON
条件中添加一个过滤器b.IsApproved
(在右表Bar上)的查询时:
SELECT *
FROM Foo f
LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId);
NOT的含义并不等同于将筛选器放在WHERE
子句中:
SELECT *
FROM Foo f
LEFT OUTER JOIN Bar b ON (b.BarId = f.BarId)
WHERE (b.IsApproved = 1);
对于'failed'的 outer join 到 Bar
(即没有b.BarId
与f.BarId
匹配的情况),所有这些失败连接的行中的 b.IsApproved
都将被置为NULL
,然后过滤掉这些行。
另一种看待这个问题的方式是,在第一个查询中,LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId)
总是会返回左表的每一行,因为LEFT OUTER JOIN
保证即使连接失败,也会返回左表的行。然而,在连接条件中添加 (b.IsApproved = 1)
的效果是,当 (b.IsApproved = 1)
为 false 时,将右表的任何列设置为 NULL
,即按照同样应用于 LEFT JOIN
条件的规则 (b.BarId = f.BarId)
。
更新:为了回答Conrad提出的问题,可选过滤器的等效LOJ如下所示:
SELECT *
FROM Foo f
LEFT OUTER JOIN Bar b ON (b.BarId = f.BarId)
WHERE (b.IsApproved IS NULL OR b.IsApproved = 1);
WHERE 子句中需要同时考虑联接失败的情况(NULL
),以及应该忽略筛选条件的情况,以及联接成功但必须应用筛选条件的情况。(b.IsApproved
或者 b.BarId
可能会被测试为空)
我已经准备好了一个SqlFiddle演示页面,其中演示了在JOIN
相对于b.IsApproved
筛选器的不同放置位置之间的差异。
WHERE b.IsApproved = 1 or b.BarId is Null
,那么它们就是相同的了。现在您打算使用哪一个? - Conrad FrixOR (b.BarId IS NULL)
,只有在 WHERE 版本中才需要,并且您希望使它相同。 - Conrad Frix不,查询优化器足够智能,会为这两个示例选择相同的执行计划。
您可以使用 SHOWPLAN
来检查执行计划。
尽管如此,您应该将所有连接放在ON
子句中,将所有限制放在WHERE
子句中。
SELECT * FROM Foo f
INNER JOIN Bar b ON b.BarId = f.BarId
WHERE b.IsApproved = 1;
这是更好的格式选择。它易于阅读和修改。在商业领域,这是您需要选择的格式。就性能而言,它们是相同的。
我刚刚对四个表进行了一个查询测试 - 一个主表与三个INNER JOIN,并且总共有四个参数,并比较了两种方法的执行计划(使用JOIN中的筛选条件,然后也在WHERE子句中使用)。
执行计划完全相同。我是在SQL Server 2008 R2上运行的。
我曾经看到过一些情况,即使在最新版本的MSSQL上,优化器也不够聪明 - 性能差异非常大。
但这是个例外,大多数情况下SQL Server优化器都会解决问题并得到正确的计划。
因此,在需要时使用WHERE子句上的过滤器并进行优化。
WHERE
子句中,将连接条件放在ON
子句中。 - KM.tableA.column = tableB.column
,过滤条件是:tableA.Column=5
。在执行外连接(LEFT/RIGHT JOIN)时,你必须将过滤条件放在ON
语句中,或者使用以下方式编写WHERE
语句:(tableA.Column=5 OR tableA.Column IS NULL)
。 - KM.