sql语句执行顺序及流程

想成为一个有逼格的资深程序员，写每一行代码时都应该去思考，sql也是，要想写出一条效率高的sql，就需要熟练掌握sql语句的执行顺序，流程、执行计划。

准备测试实验

创建table1、table2，插入测试数据，如下：

 CREATE TABLE table1
 (
     user_id VARCHAR(10) NOT NULL,
     city VARCHAR(10) NOT NULL,
     PRIMARY KEY(user_id)
 )ENGINE=INNODB DEFAULT CHARSET=UTF8;
 

 CREATE TABLE table2
 (
     order_id INT NOT NULL AUTO_INCREMENT,
     user_id VARCHAR(10),
     PRIMARY KEY(order_id)
 )ENGINE=INNODB DEFAULT CHARSET=UTF8;


 INSERT INTO table1(user_id,city) VALUES('lisi','xian');
 INSERT INTO table1(user_id,city) VALUES('zhangsan','xian');
 INSERT INTO table1(user_id,city) VALUES('brayden','xian');
 INSERT INTO table1(user_id,city) VALUES('red','xian');
 
 INSERT INTO table2(user_id) VALUES('lisi');
 INSERT INTO table2(user_id) VALUES('lisi');
 INSERT INTO table2(user_id) VALUES('zhangsan');
 INSERT INTO table2(user_id) VALUES('zhangsan');
 INSERT INTO table2(user_id) VALUES('zhangsan');
 INSERT INTO table2(user_id) VALUES('brayden');
 INSERT INTO table2(user_id) VALUES(NULL);

测试sql，如下：

SELECT a.user_id, COUNT(b.order_id) AS total_orders
 FROM table1 AS a
 LEFT JOIN table2 AS b
 ON a.user_id = b.user_id
 WHERE a.city = 'xian'
 GROUP BY a.user_id
 HAVING COUNT(b.order_id) < 2
 ORDER BY total_orders DESC; 

SQ