我要评论一、准备工作：创建真实业务表并插入测试数据
我们以电商场景的用户表（user）、订单表（order）、 商品表（product） 为例，先创建表并插入测试数据（注意：order是 mysql 关键字，需用反引号``包裹）。
1. 创建表结构
-- 1. 用户表（存储用户基本信息）
create table `user` (
user_id int primary key auto_increment comment '用户id（主键）',
user_name varchar(50) not null comment '用户名',
user_phone varchar(20) comment '用户手机号',
create_time datetime default current_timestamp comment '创建时间'
) engine=innodb default charset=utf8mb4;
-- 2. 商品表（存储商品信息）
create table `product` (
product_id int primary key auto_increment comment '商品id（主键）',
product_name varchar(100) not null comment '商品名称',
price decimal(10,2) not null comment '商品价格',
stock int default 0 comment '商品库存'
) engine=innodb default charset=utf8mb4;
-- 3. 订单表（存储用户订单，关联用户表和商品表）
create table `order` (
order_id int primary key auto_increment comment '订单id（主键）',
user_id int not null comment '用户id（关联user表的user_id）',
product_id int not null comment '商品id（关联product表的product_id）',
order_amount decimal(10,2) not null comment '订单金额',
order_time datetime default current_timestamp comment '下单时间',
-- 外键约束（可选，用于强制数据完整性）
foreign key (user_id) references `user`(user_id),
foreign key (product_id) references `product`(product_id)
) engine=innodb default charset=utf8mb4;
2. 插入测试数据
-- 插入用户数据（3个用户：张三、李四、王五，其中王五暂无订单）
insert into `user` (user_name, user_phone) values
('张三', '13800138000'),
('李四', '13900139000'),
('王五', '13700137000');
-- 插入商品数据（3个商品：手机、电脑、耳机）
insert into `product` (product_name, price, stock) values
('华为mate60', 6999.00, 100),
('苹果macbook pro', 12999.00, 50),
('索尼wh-1000xm5', 2499.00, 200);
-- 插入订单数据（4个订单：张三买了手机和耳机，李四买了电脑，无王五的订单，且有一个订单关联的商品id为4（不存在的商品））
insert into `order` (user_id, product_id, order_amount) values
(1, 1, 6999.00), -- 张三买华为mate60
(1, 3, 2499.00), -- 张三买索尼耳机
(2, 2, 12999.00), -- 李四买苹果电脑
(2, 4, 0.00); -- 李四买了一个不存在的商品（product_id=4）
3. 表数据预览
| 表名 | 数据内容 |
|---|---|
| user | user_id:1（张三）、2（李四）、3（王五） |
| product | product_id:1（华为 mate60）、2（苹果 macbook pro）、3（索尼耳机） |
| order | order_id:1（1-1-6999）、2（1-3-2499）、3（2-2-12999）、4（2-4-0） |
二、各类连表查询详解（附语法、结果、作用）
1. 交叉连接（cross join）：笛卡尔积连接
作用
返回两个表的笛卡尔积（表 a 的每一行与表 b 的每一行组合），无任何条件匹配，实际业务中极少直接使用，通常需配合where过滤。
语法
-- 显式交叉连接（用户表 × 商品表） select u.user_name, p.product_name from `user` u cross join `product` p;
执行结果（共 3×3=9 行）
| user_name | product_name |
|---|---|
| 张三 | 华为 mate60 |
| 张三 | 苹果 macbook pro |
| 张三 | 索尼 wh-1000xm5 |
| 李四 | 华为 mate60 |
| 李四 | 苹果 macbook pro |
| 李四 | 索尼 wh-1000xm5 |
| 王五 | 华为 mate60 |
| 王五 | 苹果 macbook pro |
| 王五 | 索尼 wh-1000xm5 |
特点
- 结果行数 = 表 a 行数 × 表 b 行数；
- 无业务意义，仅用于测试或特殊数据生成。
2. 内连接（inner join）：匹配连接（最常用）
作用
只返回两个 / 多个表中满足关联条件的行（即交集），是业务中使用频率最高的连表查询。
语法（查询用户的订单及对应商品信息）
-- 显式内连接（推荐，可读性高）
select
u.user_name,
o.order_id,
p.product_name,
o.order_amount
from `user` u
inner join `order` o on u.user_id = o.user_id
inner join `product` p on o.product_id = p.product_id;
-- 隐式内连接（省略inner join，用where指定条件，功能一致）
select
u.user_name,
o.order_id,
p.product_name,
o.order_amount
from `user` u, `order` o, `product` p
where u.user_id = o.user_id and o.product_id = p.product_id;
执行结果（仅保留匹配的行，共 3 行）
| user_name | order_id | product_name | order_amount |
|---|---|---|---|
| 张三 | 1 | 华为 mate60 | 6999.00 |
| 张三 | 2 | 索尼 wh-1000xm5 | 2499.00 |
| 李四 | 3 | 苹果 macbook pro | 12999.00 |
关键说明
- 过滤掉了：王五（无订单）、李四的 order_id=4（product_id=4 无匹配商品）；
- 可关联多个表（上述示例关联了 3 个表）；
- 显式内连接更符合 sql 标准，推荐使用。
3. 外连接（outer join）：保留单侧 / 双侧未匹配行
外连接分为左外连接、右外连接、全外连接（mysql 不直接支持全外连接，需用 union 实现），核心是保留一侧 / 双侧表的所有行，无匹配时显示null。
（1）左外连接（left join / left outer join）
作用：保留左表（join左侧的表）的所有行，右表仅匹配满足条件的行；若右表无匹配，右表字段显示null。
语法（查询所有用户的订单，包括无订单的用户）
-- 左外连接：保留用户表的所有行
select
u.user_name,
o.order_id,
p.product_name,
o.order_amount
from `user` u
left join `order` o on u.user_id = o.user_id
left join `product` p on o.product_id = p.product_id;
执行结果（共 5 行，包含王五和李四的无效订单）
| user_name | order_id | product_name | order_amount |
|---|---|---|---|
| 张三 | 1 | 华为 mate60 | 6999.00 |
| 张三 | 2 | 索尼 wh-1000xm5 | 2499.00 |
| 李四 | 3 | 苹果 macbook pro | 12999.00 |
| 李四 | 4 | null | 0.00 |
| 王五 | null | null | null |
关键说明
- 保留了左表（
user）的所有用户：王五（无订单，字段为 null）； - 保留了李四的 order_id=4（product_id=4 无匹配商品，product_name 为 null）。
在这段左外连接 sql 中，第一个left join的左表是from后的user表（别名u），而后续的left join的左表是前一个连接的结果集（即user和order连接后的临时表）。
分步拆解说明
我们把 sql 拆成两步，你会更清晰：
第一步：user left join order
-- 左表：user（u），右表：order（o） select u.user_name, o.order_id from `user` u left join `order` o on u.user_id = o.user_id;
- 左表：
user（from后的主表），保留其所有行； - 右表：
order，仅匹配满足u.user_id = o.user_id的行，无匹配则显示null。
第二步：再 left join product
-- 左表：第一步得到的临时表（user+order），右表：product（p） select ... from (user u left join order o on ...) -- 这是新的左表 left join `product` p on o.product_id = p.product_id;
- 左表：前一次连接的结果集（
user和order连接后的临时表），保留其所有行； - 右表：
product，仅匹配满足o.product_id = p.product_id的行，无匹配则显示null。
核心规律（通用）
在 mysql 的多表连接中：
left join的左表 =left join关键字左侧的表 / 结果集；left join的右表 =left join关键字右侧的表 / 结果集；- 多表连续
left join时，左表会依次继承前一次连接的结果，这也是为什么你的 sql 能保留user表所有行的原因（因为每一步都是左连接，最终不会过滤掉user的行）。
比如你的 sql 中，即使后续连接product，也不会丢失user表的行（比如王五无订单的行、李四的无效订单行都会保留）。
（2）右外连接（right join / right outer join）
作用：保留右表（join右侧的表）的所有行，左表仅匹配满足条件的行；若左表无匹配，左表字段显示null。
语法（查询所有商品的订单，包括无订单的商品）
-- 右外连接：保留商品表的所有行
select
u.user_name,
o.order_id,
p.product_name,
o.order_amount
from `user` u
inner join `order` o on u.user_id = o.user_id
right join `product` p on o.product_id = p.product_id;
执行结果（共 3 行，无订单的商品显示 null）
| user_name | order_id | product_name | order_amount |
|---|---|---|---|
| 张三 | 1 | 华为 mate60 | 6999.00 |
| 李四 | 3 | 苹果 macbook pro | 12999.00 |
| 张三 | 2 | 索尼 wh-1000xm5 | 2499.00 |
扩展：若要保留商品表全量（包括无订单的商品，需调整连接顺序）
select
u.user_name,
o.order_id,
p.product_name,
o.order_amount
from `product` p
right join `order` o on p.product_id = o.product_id
left join `user` u on o.user_id = u.user_id;
-- 结果会包含product_id=4的无效订单，以及所有商品（若有商品无订单则显示null）
（3）全外连接（full join / full outer join）
作用：保留左表和右表的所有行，无匹配的一侧字段显示null（即左外连接 + 右外连接的并集）。
注意：mysql 不直接支持 full join，需通过left join union right join实现
语法（查询所有用户和所有商品的订单，无匹配则显示 null）
-- 左外连接部分：所有用户 + 匹配的订单/商品
select
u.user_name,
o.order_id,
p.product_name,
o.order_amount
from `user` u
left join `order` o on u.user_id = o.user_id
left join `product` p on o.product_id = p.product_id
union -- union去重，union all保留重复行
-- 右外连接部分：所有商品 + 匹配的订单/用户
select
u.user_name,
o.order_id,
p.product_name,
o.order_amount
from `user` u
right join `order` o on u.user_id = o.user_id
right join `product` p on o.product_id = p.product_id;
执行结果（包含所有用户、所有商品、所有订单，无匹配则 null）
| user_name | order_id | product_name | order_amount |
|---|---|---|---|
| 张三 | 1 | 华为 mate60 | 6999.00 |
| 张三 | 2 | 索尼 wh-1000xm5 | 2499.00 |
| 李四 | 3 | 苹果 macbook pro | 12999.00 |
| 李四 | 4 | null | 0.00 |
| 王五 | null | null | null |
4. 自然连接（natural join）：自动匹配同名字段
作用
自动根据两个表中名称相同的字段进行连接（无需手动写on条件），分为自然内连接、自然左 / 右外连接。
语法（自动匹配user_id字段，查询用户和订单）
-- 自然内连接（自动匹配user_id字段）
select
user_name,
order_id,
order_amount
from `user` u
natural join `order` o;
执行结果（等同于 inner join on u.user_id = o.user_id，共 4 行）
| user_name | order_id | order_amount |
|---|---|---|
| 张三 | 1 | 6999.00 |
| 张三 | 2 | 2499.00 |
| 李四 | 3 | 12999.00 |
| 李四 | 4 | 0.00 |
特点
- 自动匹配同名字段，无需写
on条件； - 风险高：若表中有多个同名字段（如
create_time），会同时匹配所有同名字段，导致非预期结果； - 生产环境极少使用，推荐显式指定
on条件。
5. 自连接（self join）：表自身连接
作用
将表与自身进行连接（把一个表当作两个表使用），用于查询表内的层级关系或关联数据（如员工与上级、分类与子分类）。
扩展：新增员工表并演示自连接
-- 创建员工表（包含员工id和上级id，上级id关联自身的员工id）
create table `employee` (
emp_id int primary key auto_increment comment '员工id',
emp_name varchar(50) not null comment '员工姓名',
manager_id int comment '上级id（关联emp_id）',
department varchar(50) comment '部门'
) engine=innodb default charset=utf8mb4;
-- 插入测试数据
insert into `employee` (emp_name, manager_id, department) values
('马云', null, '董事会'), -- 马云无上级
('张勇', 1, 'ceo办公室'), -- 张勇的上级是马云
('王坚', 2, '阿里云'), -- 王坚的上级是张勇
('蒋芳', 2, '廉政部'); -- 蒋芳的上级是张勇
-- 自连接查询：每个员工的姓名及对应的上级姓名
select
e.emp_name as 员工姓名,
m.emp_name as 上级姓名,
e.department as 部门
from `employee` e
left join `employee` m on e.manager_id = m.emp_id;
执行结果
| 员工姓名 | 上级姓名 | 部门 |
|---|---|---|
| 马云 | null | 董事会 |
| 张勇 | 马云 | ceo 办公室 |
| 王坚 | 张勇 | 阿里云 |
| 蒋芳 | 张勇 | 廉政部 |
特点
- 本质是内连接 / 外连接的特殊形式（表自身关联）；
- 必须为表取不同的别名（如
e代表员工，m代表上级）； - 常用于处理层级数据（如组织架构、评论回复）。
三、各类连表查询的核心差异对比表
| 连接类型 | 核心逻辑 | 结果集特点 | 匹配条件指定方式 | 常用场景 |
|---|---|---|---|---|
| 交叉连接（cross） | 笛卡尔积，无匹配 | 行数 = 表 a× 表 b，无意义组合多 | 无（或 where 过滤） | 测试数据、特殊数据生成 |
| 内连接（inner） | 仅匹配满足条件的行（交集） | 只保留匹配行，无 null | on/where | 业务核心查询（如用户 + 订单） |
| 左外连接（left） | 保留左表所有行，右表匹配 | 左表全量，右表无匹配则 null | on | 查主表全量 + 关联表数据（所有用户 + 订单） |
| 右外连接（right） | 保留右表所有行，左表匹配 | 右表全量，左表无匹配则 null | on | 查关联表全量 + 主表数据（所有订单 + 用户） |
| 全外连接（full） | 保留左右表所有行（并集） | 左右表全量，无匹配则 null | on（mysql 需 union 实现） | 查两个表的所有数据（极少用） |
| 自然连接（natural） | 自动匹配同名字段 | 同内 / 外连接，但依赖字段名 | 自动（无需 on） | 简单场景（不推荐生产使用） |
| 自连接（self） | 表自身关联 | 同内 / 外连接，处理表内层级 | on（表别名区分） | 层级数据查询（员工 - 上级、分类 - 子分类） |
四、关键注意事项
关联键索引：连表查询的关联键（如user.user_id、order.user_id）需建立索引，否则会触发全表扫描，性能极差；
null 值处理：外连接的 null 字段需用ifnull()/coalesce()处理（如ifnull(o.order_amount, 0.00)），避免业务逻辑异常；
表别名：多表连接时建议使用表别名（如u代表user，o代表order），简化 sql 并提高可读性；
优先级：join的执行优先级高于where，因此关联条件写在on中比where中更高效（尤其是外连接）。
到此这篇关于一文带你掌握mysql中的连表查询的文章就介绍到这了,更多相关mysql连表查询内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论