Is Join or Where clause approach more efficient in

问题:

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

回答1:

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

回答2:

It's not an issue of efficiency. Your restrictions limit which rows will be returned; therefore they logically belong in the WHERE clause.

回答3:

A pre-scrubbed WHERE (subquery), but check the query plan for the difference:

SELECT ...
FROM   ...
JOIN   (select ... from table1 where col1 in (1,2,3) ) as Table1
ON     Table1.ColA = Table2.ColA

回答4:

Your description says "1, 2 or 3", so you'll want

Where (Col1=1 Or Col1=2 Or Col1=3)

or you could do this

Where Col1 in (1, 2, 3)

回答5:

It doesn't matter, the optimizer will produce the same query in the end. I think filtering in the WHERE clause is more readable though, especially for inner joins.