MySql Join理论以及SQL的解析步骤

关于连接查询能主要作了如下的总结:

由于mysql中不支持FULL连接,同时union有去重的作用,所以可以通过一下的操作代替Full OUTER JOIN:

select * from A left join B where A.key=B.key union select * from A right join B where A.key=B.key

select * from A left join B where A.key=B.key where 
where B.key is null union select * from A right join B where A.key=B.key where 
where A.key is null

在当我们编写sql的时候通常是按照下面的格式进行的:

SELECT DISTINCT
    < select_list >
FROM
    < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
    < where_condition >
GROUP BY
    < group_by_list >
HAVING
    < having_condition >
ORDER BY
    < order_by_condition >
LIMIT < limit_number >

然而在mysql内部,解析的步骤却和上边的顺序大不一样:

 1 FROM <left_table>
 2 ON <join_condition>
 3 <join_type> JOIN <right_table>
 4 WHERE <where_condition>
 5 GROUP BY <group_by_list>
 6 HAVING <having_condition>
 7 SELECT 
 8 DISTINCT <select_list>
 9 ORDER BY <order_by_condition>
10 LIMIT <limit_number>

下面上图: