mysql执行顺序与join连接

mysql加载顺序#

手写顺序

Copy
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>

机读顺序

Copy
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语句。

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

Copy
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);

内连接(等值连接)

Copy
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补齐)

Copy
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补齐)

Copy
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)

左独占连接

Copy
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)

右独占连接

Copy
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来进行模拟。

Copy
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连接来进行模拟

Copy
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)
posted @   yscl  阅读(1401)  评论(0编辑  收藏  举报
编辑推荐:
· Linux系列:如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
阅读排行:
· 没有Manus邀请码?试试免邀请码的MGX或者开源的OpenManus吧
· 无需6万激活码!GitHub神秘组织3小时极速复刻Manus,手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· DeepSeek在M芯片Mac上本地化部署
· 葡萄城 AI 搜索升级:DeepSeek 加持,客户体验更智能
点击右上角即可分享
微信分享提示
CONTENTS