How does (My)SQL evaluate chained LEFT/INNER JOINs?

Question

We have 3 Tables given:

T1:               T2:              T3:

id | t3_id        id | name        id | t2_id
----------        ---------        ----------
1  | 2            1  | abc         1  | 1
2  | NULL         2  | 123         2  | 1
3  | 1            3  | ab1         3  | 3
4  | 4            4  | 32b         4  | 2

Now we had the following JOINs tested:

1.:

SELECT
  *
FROM T1
INNER JOIN T3 ON T1.t3_id = T3.id
INNER JOIN T2 ON T3.t2_id = T2.id
WHERE T2.name = '%'

This case is clear. Results:

T1.id | T1.t3_id | T3.id | T3.t2_id | T2.id | T2.name
-----------------------------------------------------
1     | 2        | 2     | 1        | 1     | abc
3     | 1        | 1     | 1        | 1     | abc
4     | 4        | 4     | 2        | 2     | 123

2.:

SELECT
  *
FROM T1
LEFT JOIN T3 ON T1.t3_id = T3.id
LEFT JOIN T2 ON T3.t2_id = T2.id
WHERE T2.name = '%'

This one is also clear:

T1.id | T1.t3_id | T3.id | T3.t2_id | T2.id | T2.name
-----------------------------------------------------
1     | 2        | 2     | 1        | 1     | abc
2     | NULL     | NULL  | NULL     | NULL  | NULL
3     | 1        | 1     | 1        | 1     | abc
4     | 4        | 4     | 2        | 2     | 123

3.:

SELECT
  *
FROM T1
LEFT JOIN T3 ON T1.t3_id = T3.id
INNER JOIN T2 ON T3.t2_id = T2.id
WHERE T2.name = '%'

This one is a bit strange. Result (same as the first one):

T1.id | T1.t3_id | T3.id | T3.t2_id | T2.id | T2.name
-----------------------------------------------------
1     | 2        | 2     | 1        | 1     | abc
3     | 1        | 1     | 1        | 1     | abc
4     | 4        | 4     | 2        | 2     | 123

I don't understand how MySQL evaluates this expression. Why it does ignore the LEFT JOIN and seems to prefer the INNER JOIN.

If I read this query it's like:

1. Take data from T1
2. Take data from T2 via LEFT JOIN (means: only if possible, else take NULL data)
3. Take data from T3 via INNER JOIN (means: remove all T2 data that cannot be joined via this INNER JOIN)

But it seem's like I have to read the query backwards?!

Could someone please explain this scenario to me?

Answer 1

INNER JOIN is applied to the result you already have (ie T1 LEFT JOIN T3), not only to T3.

You read it like :

(T1 LEFT JOIN (T3 INNER JOIN T2))

But it's like :

((T1 LEFT JOIN T3) INNER JOIN T2)