mysql多个join在一句先后执行顺序,SQL多个JOIN执行顺序

mysql> select * from a;

+------+------+

| id | name |

+------+------+

| 1 | a1 |

| 2 | a2 |

| 3 | a3 |

| 4 | a4 |

+------+------+

4 rows in set (0.00 sec)

mysql> select * from b;

+------+------+

| id | name |

+------+------+

| 1 | b1 |

| 2 | b2 |

| 5 | b5 |

+------+------+

3 rows in set (0.00 sec)

mysql> select * from c;

+------+------+

| id | name |

+------+------+

| 1 | c1 |

| 4 | c4 |

| 5 | c5 |

+------+------+

3 rows in set (0.00 sec)

mysql> select * from a left join b on a.id=b.id;

+------+------+------+------+

| id | name | id | name |

+------+------+------+------+

| 1 | a1 | 1 | b1 |

| 2 | a2 | 2 | b2 |

| 3 | a3 | NULL | NULL |

| 4 | a4 | NULL | NULL |

+------+------+------+------+

4 rows in set (0.00 sec)

mysql> select * from a left join b on a.id=b.id left join c on b.id=c.id;

+------+------+------+------+------+------+

| id | name | id | name | id | name |

+------+------+------+------+------+------+

| 1 | a1 | 1 | b1 | 1 | c1 |

| 2 | a2 | 2 | b2 | NULL | NULL |

| 3 | a3 | NULL | NULL | NULL | NULL |

| 4 | a4 | NULL | NULL | NULL | NULL |

+------+------+------+------+------+------+

4 rows in set (0.00 sec)

--- Block Nested Loop 是驱动表(左边表)逐条和被驱动表(右边表)比较

mysql> explain select * from a left join b on a.id=b.id left join c on b.id=c.id;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------

---------------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra

|

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------

---------------------------------------------+

| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL

|

| 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using

where; Using join buffer (Block Nested Loop) |

| 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using

where; Using join buffer (Block Nested Loop) |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------

---------------------------------------------+

3 rows in set, 1 warning (0.00 sec)