MySQL连表查询
参考:https://blog.csdn.net/lj1994104/article/details/79671807
一,MySQL连接查询类型
A)内连接:join,inner join
B)外连接:left join,left outer join,right join,right outer join,union
C)交叉连接:cross join
二,下面以示例进行分析
创建两张表,把表当成一个集合那么表中的元素就是集合的一个元素
创建a表
1 2 3 4 5 | CREATE TABLE `a` ( `id` int (10) NOT NULL AUTO_INCREMENT, `age` int (2) DEFAULT NULL, PRIMARY KEY (`id`) ) ; |
创建b表
1 2 3 4 5 | CREATE TABLE `b` ( `id` int (10) NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ; |
插入测试数据
2.1内连接inner join或者join(等同与inner join)
1 2 3 4 5 6 7 8 | mysql> select * from a inner join b on a.id=b.id; +----+------+----+-------+ | id | age | id | name | +----+------+----+-------+ | 2 | 11 | 2 | zhang | | 3 | 12 | 3 | li | | 4 | 13 | 4 | zhou | +----+------+----+-------+ |
应用场景
这种场景下得到的是满足某一条件的A,B内部的数据;本次需要满足的条件是a.id等于b。id
正因为得到的是内部共有数据,所以连接方式称为内连接。
2.2外连接(6种场景)
2.2.1 left join 或者left outer join(等同于left join)
1 2 3 4 5 6 7 8 9 | mysql> select * from a left join b on a.id=b.id; +----+------+------+-------+ | id | age | id | name | +----+------+------+-------+ | 2 | 11 | 2 | zhang | | 3 | 12 | 3 | li | | 4 | 13 | 4 | zhou | | 1 | 10 | NULL | NULL | +----+------+------+-------+ |
Table B中不存在的记录填充NULL
应用场景
2.2.2 [left join 或者left outer join(等同于left join)] + [where B.column is null]
1 2 3 4 5 6 | mysql> select * from a left join b on a.id=b.id where b.id is null ; +----+------+------+------+ | id | age | id | name | +----+------+------+------+ | 1 | 10 | NULL | NULL | +----+------+------+------+ |
应用场景
这种场景下得到的是A中的所有数据减去"与B满足同一条件 的数据",然后得到的A剩余数据;
2.2.3 right join 或者fight outer join(等同于right join)
1 2 3 4 5 6 7 8 9 | mysql> select * from a right join b on a.id=b.id; +------+------+----+-------+ | id | age | id | name | +------+------+----+-------+ | 2 | 11 | 2 | zhang | | 3 | 12 | 3 | li | | 4 | 13 | 4 | zhou | | NULL | NULL | 5 | chen | +------+------+----+-------+ |
应用场景
这种场景下得到的是B的所有数据,和满足某一条件的A的数据;
2.2.4 [left join 或者left outer join(等同于left join)] + [where A.column is null]
1 2 3 4 5 6 | mysql> select * from a right join b on a.id=b.id where a.id is null ; +------+------+----+------+ | id | age | id | name | +------+------+----+------+ | NULL | NULL | 5 | chen | +------+------+----+------+ |
应用场景
这种场景下得到的是B中的所有数据减去 "与A满足同一条件 的数据“,然后得到的B剩余数据;
2.2.5 full join (mysql不支持,但是可以用 left join union right join代替)
1 2 3 4 5 6 7 8 9 10 | mysql> select * from a left join b on a.id=b.id union select * from a right join b on a.id=b.id; +------+------+------+-------+ | id | age | id | name | +------+------+------+-------+ | 2 | 11 | 2 | zhang | | 3 | 12 | 3 | li | | 4 | 13 | 4 | zhou | | 1 | 10 | NULL | NULL | | NULL | NULL | 5 | chen | +------+------+------+-------+ |
union过后,重复的记录会合并(id为2,3,4的三条记录),
应用场景
这种场景下得到的是满足某一条件的公共记录,和独有的记录
2.2.6 full join + is null(mysql不支持,但是可以用 (left join + is null) union (right join+isnull代替)
1 2 3 4 5 6 7 | mysql> select * from a left join b on a.id=b.id where b.id is null union select * from a right join b on a.id=b.id where a.id is null ; +------+------+------+------+ | id | age | id | name | +------+------+------+------+ | 1 | 10 | NULL | NULL | | NULL | NULL | 5 | chen | +------+------+------+------+ |
应用场景
这种场景下得到的是A,B中不满足某一条件的记录之和
2.3交叉连接(cross join)
2.3.1 实际应用中还有这样一种情形,想得到A,B记录的排列组合,即笛卡儿积,这个就不好用集合和元素来表示了。需要用到cross join:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | mysql> select * from a cross join b; +----+------+----+-------+ | id | age | id | name | +----+------+----+-------+ | 1 | 10 | 2 | zhang | | 2 | 11 | 2 | zhang | | 3 | 12 | 2 | zhang | | 4 | 13 | 2 | zhang | | 1 | 10 | 3 | li | | 2 | 11 | 3 | li | | 3 | 12 | 3 | li | | 4 | 13 | 3 | li | | 1 | 10 | 4 | zhou | | 2 | 11 | 4 | zhou | | 3 | 12 | 4 | zhou | | 4 | 13 | 4 | zhou | | 1 | 10 | 5 | chen | | 2 | 11 | 5 | chen | | 3 | 12 | 5 | chen | | 4 | 13 | 5 | chen | +----+------+----+-------+ |
2.3.2 还可以为cross join指定条件 (where):
1 2 3 4 5 6 7 8 | mysql> select * from a cross join b where a.id=b.id; +----+------+----+-------+ | id | age | id | name | +----+------+----+-------+ | 2 | 11 | 2 | zhang | | 3 | 12 | 3 | li | | 4 | 13 | 4 | zhou | +----+------+----+-------+ |
注:这种情况实际上实现了内连接
三 注意事项
上面仍然存在遗漏,那就是mysql对sql语句的容错问题,即在sql语句不完全符合书写建议的情况,mysql会允许这种情况,尽可能地解释它:
3.1 一般cross join后面加上where条件,但是用cross join+on也是被解释为cross join+where;
3.2 一般内连接都需要加上on限定条件,如上面场景2.1;如果不加会被解释为交叉连接;
3.3 如果连接表格使用的是逗号,会被解释为交叉连接;
注:sql标准中还有union join和natural inner join,mysql不支持,而且本身也没有多大意义,其结果可以用上面的几种连接方式得到
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· AI编程工具终极对决:字节Trae VS Cursor,谁才是开发者新宠?
· 开源Multi-agent AI智能体框架aevatar.ai,欢迎大家贡献代码
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!