mysql多个连接执行顺序,mysql执行顺序与join连接

mysql加载顺序

手写顺序

SELECT DISTINCT

FROM

join JOIN ON

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

机读顺序1. FROM

2. ON

3. JOIN

4. WHERE

5. GROUP BY

6. HAVING

7. SELECT

8. DISTINCT

9. ORDER BY

10. LIMIT

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)