inner join on condition or using where?

Question

i have this query

SELECT t1.col1
FROM table1 t1
INNER JOIN table2 t2 ON t2.col1 = t1.col1
WHERE t2.col IN (1, 2, 3)

and this query

SELECT t1.col1
FROM table1 t1
INNER JOIN table2 t2 ON t2.col1 = t1.col1 AND t2.col IN (1, 2, 3)

both game me the same execution plan and told me

Using where;

does that mean that my 2nd query is converted to the first form from the optimizer and that i should follow the first form?

and is there a way to check the optimizer new query?

The two are the same. Which to use is simply a matter of preference although some people prefer to only use the `on` clause for conditions between the two tables, with filtering going in the `where` clause. — Gordon Linoff, Jul 15 '19 at 18:51

score 2 · Accepted Answer · answered Jul 15 '19 at 18:53

SQL is not a procedural language. It is a descriptive language. A query describes the result set that you want to produce.

With an inner join, the two queries in your question are identical -- they produce the same result set under all circumstances. Which to prefer is a stylistic preference. MySQL should treat the two the same way from an optimization perspective.

One preference is that filters on a single table are more appropriate for WHERE and ON.

With an outer join, the two queries are not the same, and you should use the one that expresses your intent.

score 0 · Answer 2 · answered Jul 15 '19 at 18:51

0

The first one filters after joining the two tables; while the second one joins using the filtering of the values as a condition of joining the two tables. You may check this thread:

SQL join: where clause vs. on clause

answered Jul 15 '19 at 18:51

lije

420
2
15

score 0 · Answer 3 · answered Jul 15 '19 at 18:53

0

You can check how much does it take to do both queries, but anyway, you can write the query in both ways, the optimizer will try to optimize the query either if it is the first query or the second one.

answered Jul 15 '19 at 18:53

Emiliano Bilski

1
1

score 0 · Answer 4 · answered Jul 16 '19 at 20:29

I like this rule:

ON ... says how the tables are related.
WHERE ... filters the results.

This becomes important with LEFT JOIN because ON does not act as a filter.

This shows you that the Optimizer treats them the same:

EXPLAIN EXTENDED SELECT ...
SHOW WARNINGS;

inner join on condition or using where?

4 Answers4