Join或Where子句方法在限制结果集中是否更有效? [英] Is Join or Where clause approach more efficient in restricting result set?

问题描述

我需要限制基于Col1的一对多潜在值的SELECT语句的结果集.例如，我想返回Col1等于1、2和3的所有行.

到目前为止，我有两种不同的方法来限制结果集:

方法1

Inner Join Table1 On (Table2.ColA=Table1.ColA) And (Col1=1 And Col1=2 And Col1=3)

方法2

Inner Join Table1 On Table2.ColA=Table1.ColA
Where (Col1=1 And Col1=2 And Col1=3)

是这些方法中的一种还是更有效的替代方法?这些值是动态的，并在每次调用时传递给存储过程.

谢谢， 克里斯

解决方案

内部联接

在处理INNER JOIN时，对要联接的表的过滤是遵循ON子句还是发生在WHERE子句中都没有关系-它将产生相同的结果集. /p>

外部联接

但这不是外部联接的情况...
在OUTER JOIN中，如果您在ON子句中指定了过滤条件-则在创建JOIN之前 应用该条件.这是一个示例:

     FROM TABLE_1 a
LEFT JOIN TABLE_2 b ON b.cola = a.cola
                   AND b.col1 IN (1,2,3)

与在WHERE中指定标准相比，这可能会严重影响结果集:

     FROM TABLE_1 a
LEFT JOIN TABLE_2 b ON b.cola = a.cola
    WHERE b.col1 IN (1,2,3)

结论

重要的是你:

1. 知道区别
2. 查询结构一致

I need to restrict a result set for a SELECT statement based upon Col1 have 1-to-many potential values. For example, I want to return all rows where Col1 equals a value of 1, 2, and 3.

So far I have two different approaches to restricting the result set:

Approach #1

Inner Join Table1 On (Table2.ColA=Table1.ColA) And (Col1=1 And Col1=2 And Col1=3)

Approach #2

Inner Join Table1 On Table2.ColA=Table1.ColA
Where (Col1=1 And Col1=2 And Col1=3)

Is one of these approaches preferred or is there an alternate approach that would be more efficient? The values are dynamic and passed to the stored procedure each time it is called.

Thanks, Chris

解决方案

INNER JOINs

When dealing with an INNER JOIN, it doesn't matter if the filtration on the table being joined to follows the ON clause, or occurs in the WHERE clause -- it will produce the same result set.

OUTER JOINs

But that's not the case for OUTER JOINs...
In an OUTER JOIN, if you specify filtration criteria in the ON clause -- the criteria are applied before the JOIN is made. Here's an example:

     FROM TABLE_1 a
LEFT JOIN TABLE_2 b ON b.cola = a.cola
                   AND b.col1 IN (1,2,3)

This can affect the result set drastically, compared to if the criteria had been specified in the WHERE:

     FROM TABLE_1 a
LEFT JOIN TABLE_2 b ON b.cola = a.cola
    WHERE b.col1 IN (1,2,3)

Conclusion

All that matters is that you:

1. Know the difference
2. Are consistent in your query structure

这篇关于Join或Where子句方法在限制结果集中是否更有效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！