Is the Join or Where approach more effective at limiting the result set?

Question

I need to limit the result set for a SELECT statement based on Col1 to have 1-to-many potential values. For example, I want to return all rows where Col1 is 1, 2, and 3.

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

Approach No. 1

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

Approach No. 2

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

Is one of these approaches preferable or is there an alternative approach that would be more effective? The values ​​are dynamic and are passed to the stored procedure every time it is called.

Thank you, Chris

Answer 1

INNER JOINs

INNER JOIN , ON WHERE - .

...
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)

, , , :

Answer 2

. , ; WHERE.

Answer 3

Pre-cleared 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

Answer 4

Your description says "1, 2 or 3", so you need to

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

or you can do it

Where Col1 in (1, 2, 3)

Answer 5

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