How does JOIN & WHERE behave?

Question

MySQL

Hello
Let's say we have a query conditionally:

select ... from t0
inner join t1 on t1.col1=t1.col0
inner join t2 on t2.col2=t1.col1
inner join tN on tN.col2=t2.col1
//ну вы поняли, каскадно джойнится таблица А ДАЛЕЕ ИДЕТ
WHERE t2.colN IS TRUE

Now the question is, the
server will first join all the data of all tables t0 t1 t2 t...N OR will it go to join t2 and see the strict WHERE that is associated with this table and only rows that match both ON AND WHERE?

That is, the question is, are all JOIN-ONs processed first, and then WHERE,
or is WHERE taken into account at the JOIN stage?
MYSQL is the latest, but it's interesting how it works on other databases too. Thanks

Answer 1

He will only take out what he needs. And it can also change the order of the joins to rake out less / faster. But in general, the query execution plan will depend on many factors (including the number of data in tables, indexes and statistics on these indexes), so it's always better to look at the explain. But the nuances may already differ from vendor to vendor

Answer 2

General logic: first join everything, then filter by where.
You can specify conditions from where in the desired join, then the selection of values ​​will be before the join.
Something like this: on (t1.id=t2.id and <condition>)

Answer 3

good question, made me read the article: https://bookflow.ru/10-prostyh-shagov-k-polnomu-po...
there is a lexical order and there is a logical order
We write like this:
SELECT [ DISTINCT ]
FROM
WHERE
GROUP BY
HAVING
UNION
ORDER BY
and the logic is this:
FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
UNION
ORDER BY

Answer 4

if considered at the pro level, then other things are variant, including there is no fixed algorithm.
explain and manual to help.