浅谈 MySQL 连表查询
连表查询是一把双刃剑, 优点是适应范式, 减少数据冗余; 缺点是连表查询特别是多张表的连表会增加数据库的负担, 降低查询效率.
简介
连表查询就是 2 张表或者多张表的联合查询, 联合查询的结果称之为 "笛卡尔积", 假设 A 表中有 n 条记录, B 表中有 m 条记录, "笛卡尔积" 就是 n*m
各种连表查询的本质就是对笛卡尔积的过滤
- 全查询: 全量查询笛卡尔积,
n*m种结果, 不加关键字过滤 - 内连接: 关键字是 INNER JOIN, JOIN, WHERE, 或者自然匹配(省略连表条件, 不建议使用!!!)
- 外连接:
- 左外连接: 关键字是 LEFT JOIN, LEFT OUTER JOIN
- 右外连接: 关键字是 RIGHT JOIN, RIGHT OUTER JOIN
这里有一张神图
连表查询总结
数据准备
create table `user_a` (
`aid` int(11) NOT NULL AUTO_INCREMENT,
`a_name` varchar(255) NOT NULL,
`age` smallint NOT NULL,
PRIMARY KEY(`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表A';
create table `user_b` (
`bid` int(11) NOT NULL AUTO_INCREMENT,
`b_name` varchar(255) NOT NULL,
`age` smallint NOT NULL,
PRIMARY KEY(`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表B';
# 插入测试数据
INSERT INTO `user_a`(aid, a_name, age) VALUES(1, 'test1', 1),(2, 'test2', 2),(3, 'test3', 3);
INSERT INTO `user_b`(bid, b_name, age) VALUES(1, 'test2', 2),(2, 'test3', 3),(4, 'test4', 4);
全查询
全查询
# 全查询: 下面的结果都是等价的
# [推荐] INNER JOIN
SELECT * FROM user_a INNER JOIN user_b;
SELECT * FROM user_a, user_b;
SELECT * FROM user_a JOIN user_b;
SELECT * FROM user_a CROSS JOIN user_b;
结果: 全量查询 n*m 种可能性
全量查询结果
内连接
内连接
对 n*m 的笛卡尔积进行过滤, 用 WHERE 或者 ON 关键字都是等价的(内连接是等价的, 外连接不是等价的)
(建议使用 ON 关键字, 约定俗称)
# 内连接: 下面的结果都是等价的
# [推荐] INNER JOIN + ON
SELECT * FROM user_a a INNER JOIN user_b b ON a.a_name=b.b_name;
# INNER JOIN + WHERE
SELECT * FROM user_a a JOIN user_b b ON a.a_name=b.b_name;
# JOIN + ON
SELECT * FROM user_a a JOIN user_b b ON a.a_name=b.b_name;
# JOIN + WHERE
SELECT * FROM user_a a JOIN user_b b WHERE a.a_name=b.b_name;
# 多表 + WHERE
SELECT * FROM user_a a, user_b b WHERE a.a_name=b.b_name;
结果: 只有左边和右边同时存在才会返回再结果集里面
内连接结果
外连接
外连接包括左连接和右连接
左连接
左连接
# 左连接
# [唯一写法] LEFT JOIN + ON
SELECT * FROM user_a a LEFT JOIN user_b b ON a.a_name=b.b_name;
结果: 保证左边的数据完整, 如果右边没有该数据, 则右边的数据为 NULL
左连接结果
右连接
右连接
# 右连接
# [唯一写法] RIGHT JOIN + ON
SELECT * FROM user_a a RIGHT JOIN user_b b ON a.a_name=b.b_name;
结果: 保证右边的数据完整, 如果左边没有该数据, 则左边的数据为 NULL
右连接结果
ON 和 WHERE 的区别
ON 和 WHERE 在内连表的时候是没有区别的 (推荐使用 INNER JOIN + ON 的规范写法)
外连接只能用 ON, 外连接用 WHERE 直接语法错误
为什么要小表驱动大表
因为连接查询的本质是遍历左边表的记录去匹配右边表的记录, 左边表的复杂度是 O(n), 右边表能走索引 O(log n)
总结
- 全量查询: 推荐使用
INNER JOIN查询整个笛卡尔集, 不常用 - 内连接: 推荐使用
INNER JOIN + ON - 左连接: 推荐使用
LEFT JOIN + ON - 右连接: 推荐使用
RIGHT JOIN + ON - 使用小表驱动大表, 因为驱动表的复杂度是 O(n), 被驱动表的复杂度是 O(log n)
reference
https://juejin.cn/post/7043811976270577672
本文由mdnice多平台发布
