mysql执行顺序与join连接

mysql加载顺序

手写顺序

SELECT DISTINCT
    <select list>
FROM 
    <left_table> join <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>    

机读顺序

 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>

sql语句的执行顺序可以用这张鱼骨图来表示

join连表

mysql中的连表基本可以分为以下几种。

接下来对这几种写出相应的sql语句。

首先是创建相应的表来进行实践。

create table if not exists tbl_dept(
    id int not null auto_increment primary key,
    deptName varchar(30),
    locAdd varchar(40)
);

create table if not exists tbl_emp(
        id int auto_increment primary key,
        name varchar(20),
        depid int
);

insert into tbl_dept(deptName, locAdd) values('RD', 11);
insert into tbl_dept(deptName, locAdd) values('HR', 12);
insert into tbl_dept(deptName, locAdd) values('MK', 13);
insert into tbl_dept(deptName, locAdd) values('MIS', 14);
insert into tbl_dept(deptName, locAdd) values('FD', 15);

insert into tbl_emp(name, depid) values('z3', 1);
insert into tbl_emp(name, depid) values('z4', 1);
insert into tbl_emp(name, depid) values('z5', 1);
insert into tbl_emp(name, depid) values('w5', 2);
insert into tbl_emp(name, depid) values('w6', 2);
insert into tbl_emp(name, depid) values('s7', 3);
insert into tbl_emp(name, depid) values('s8', 4);
insert into tbl_emp(name, depid) values('s9', 51);

内连接(等值连接)

mysql> select * from tbl_emp as e inner join tbl_dept as d on e.depid=d.id;
 ---- ------ ------- ---- ---------- -------- 
| id | name | depid | id | deptName | locAdd |
 ---- ------ ------- ---- ---------- -------- 
|  1 | z3   |     1 |  1 | RD       | 11     |
|  2 | z4   |     1 |  1 | RD       | 11     |
|  3 | z5   |     1 |  1 | RD       | 11     |
|  4 | w5   |     2 |  2 | HR       | 12     |
|  5 | w6   |     2 |  2 | HR       | 12     |
|  6 | s7   |     3 |  3 | MK       | 13     |
|  7 | s8   |     4 |  4 | MIS      | 14     |
 ---- ------ ------- ---- ---------- -------- 
7 rows in set (0.01 sec)

左连接(连接左表的全部，右表缺失的字段以null补齐)

mysql> select * from tbl_emp as e left join tbl_dept as d on e.depid=d.id;
 ---- ------ ------- ------ ---------- -------- 
| id | name | depid | id   | deptName | locAdd |
 ---- ------ ------- ------ ---------- -------- 
|  1 | z3   |     1 |    1 | RD       | 11     |
|  2 | z4   |     1 |    1 | RD       | 11     |
|  3 | z5   |     1 |    1 | RD       | 11     |
|  4 | w5   |     2 |    2 | HR       | 12     |
|  5 | w6   |     2 |    2 | HR       | 12     |
|  6 | s7   |     3 |    3 | MK       | 13     |
|  7 | s8   |     4 |    4 | MIS      | 14     |
|  8 | s9   |    51 | NULL | NULL     | NULL   |
 ---- ------ ------- ------ ---------- -------- 
8 rows in set (0.03 sec)

右连接(连接右表的全部，左表缺失的字段以null补齐)

mysql> select * from tbl_emp as e right join tbl_dept as d on e.depid=d.id;
 ------ ------ ------- ---- ---------- -------- 
| id   | name | depid | id | deptName | locAdd |
 ------ ------ ------- ---- ---------- -------- 
|    1 | z3   |     1 |  1 | RD       | 11     |
|    2 | z4   |     1 |  1 | RD       | 11     |
|    3 | z5   |     1 |  1 | RD       | 11     |
|    4 | w5   |     2 |  2 | HR       | 12     |
|    5 | w6   |     2 |  2 | HR       | 12     |
|    6 | s7   |     3 |  3 | MK       | 13     |
|    7 | s8   |     4 |  4 | MIS      | 14     |
| NULL | NULL | NULL  |  5 | FD       | 15     |
 ------ ------ ------- ---- ---------- -------- 
8 rows in set (0.03 sec)

左独占连接

mysql> select * from tbl_emp as e left join tbl_dept as d on e.depid=d.id where d.id is null;
 ---- ------ ------- ------ ---------- -------- 
| id | name | depid | id   | deptName | locAdd |
 ---- ------ ------- ------ ---------- -------- 
|  8 | s9   |    51 | NULL | NULL     | NULL   |
 ---- ------ ------- ------ ---------- -------- 
1 row in set (0.04 sec)

右独占连接

mysql> select * from tbl_emp as e right join tbl_dept as d on e.depid=d.id where e.id is null;
 ------ ------ ------- ---- ---------- -------- 
| id   | name | depid | id | deptName | locAdd |
 ------ ------ ------- ---- ---------- -------- 
| NULL | NULL | NULL  |  5 | FD       | 15     |
 ------ ------ ------- ---- ---------- -------- 
1 row in set (0.04 sec)

全连接

由于 mysql中不支持全连接，所以需要使用union来进行模拟。

mysql> select * from tbl_emp as e left join tbl_dept as d on e.depid=d.id
union
select * from tbl_emp as e right join tbl_dept as d on e.depid=d.id;
 ------ ------ ------- ------ ---------- -------- 
| id   | name | depid | id   | deptName | locAdd |
 ------ ------ ------- ------ ---------- -------- 
|    1 | z3   |     1 |    1 | RD       | 11     |
|    2 | z4   |     1 |    1 | RD       | 11     |
|    3 | z5   |     1 |    1 | RD       | 11     |
|    4 | w5   |     2 |    2 | HR       | 12     |
|    5 | w6   |     2 |    2 | HR       | 12     |
|    6 | s7   |     3 |    3 | MK       | 13     |
|    7 | s8   |     4 |    4 | MIS      | 14     |
|    8 | s9   |    51 | NULL | NULL     | NULL   |
| NULL | NULL | NULL  |    5 | FD       | 15     |
 ------ ------ ------- ------ ---------- -------- 
9 rows in set (0.04 sec)

左独占连接 右独占连接

同理使用union连接来进行模拟

mysql> select * from tbl_emp as e left join tbl_dept as d on e.depid=d.id where d.id is null
union
select * from tbl_emp as e right join tbl_dept as d on e.depid=d.id where e.id is null;
 ------ ------ ------- ------ ---------- -------- 
| id   | name | depid | id   | deptName | locAdd |
 ------ ------ ------- ------ ---------- -------- 
|    8 | s9   |    51 | NULL | NULL     | NULL   |
| NULL | NULL | NULL  |    5 | FD       | 15     |
 ------ ------ ------- ------ ---------- -------- 
2 rows in set (0.04 sec)