MySQL中多表查询的方式总结
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、先明确：为什么必须掌握多表查询？</a></li><li><a href="#_label1">二、先铺垫：多表查询的基础前提</a></li><li><a href="#_label2">三、核心多表查询方式拆解</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_0">方式 1：JOIN 连接查询</a></li><ul class="third_class_ul"><li><a href="#_label3_2_0_0">1.1 内连接（INNER JOIN / JOIN）</a></li><li><a href="#_label3_2_0_1">1.2 左外连接（LEFT JOIN / LEFT OUTER JOIN）</a></li><li><a href="#_label3_2_0_2">1.3 右外连接（RIGHT JOIN / RIGHT OUTER JOIN）</a></li><li><a href="#_label3_2_0_3">1.4 交叉连接（CROSS JOIN，极少用）</a></li></ul><li><a href="#_lab2_2_1">方式 2：子查询（嵌套查询，适合简单场景）</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_2_2">方式 3：联合查询（UNION / UNION ALL，结果集合并）</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_2_3">加分项</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_2_4">举一反三</a></li><ul class="third_class_ul"></ul></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>一、先明确：为什么必须掌握多表查询？</h2><p>1.你是否能区分不同多表查询方式的差异？<br />2.能否根据业务场景选择合适的查询方式？<br />3.能否独立写出正确的多表查询 SQL？（避免语法错误）</p>
<p class="maodian"><a name="_label1"></a></p><h2>二、先铺垫：多表查询的基础前提</h2>
<p>先明确多表查询的核心：表与表之间必须有 “关联关系”（通常是主键 - 外键关联，比如用户表user的id（主键）对应订单表order的user_id（外键）），没有关联关系的多表查询会产生 “笛卡尔积”（数据冗余，无实际业务意义）。</p>
<p>先定义两个测试表：</p>
<div class="jb51code"><pre class="brush:sql;">-- 用户表（主表）
CREATE TABLE `user` (
`id` INT PRIMARY KEY AUTO_INCREMENT, -- 用户ID（主键）
`username` VARCHAR(50) NOT NULL, -- 用户名
`age` INT DEFAULT 0 -- 年龄
);
-- 订单表（从表）
CREATE TABLE `order` (
`id` INT PRIMARY KEY AUTO_INCREMENT, -- 订单ID（主键）
`order_no` VARCHAR(30) NOT NULL, -- 订单编号
`user_id` INT NOT NULL, -- 关联用户表的用户ID（外键）
`price` DECIMAL(10,2) NOT NULL, -- 订单金额
FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) -- 外键约束
);</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>三、核心多表查询方式拆解</h2>
<p class="maodian"><a name="_lab2_2_0"></a></p><h3>方式 1：JOIN 连接查询</h3>
<p>JOIN是多表查询的核心方式，通过关联字段将多个表 “拼接” 在一起查询，核心分为内连接、外连接（左外连接、右外连接）、交叉连接（极少用，仅作了解）。</p>
<p class="maodian"><a name="_label3_2_0_0"></a></p><h4>1.1 内连接（INNER JOIN / JOIN）</h4>
<p><strong>通俗定义</strong>：只查询 “两张表中满足关联条件” 的数据，不满足条件的记录会被过滤掉（相当于 “取两张表的交集”）。</p>
<p><strong>语法</strong>：SELECT 字段 FROM 表1 INNER JOIN 表2 ON 表1.关联字段 = 表2.关联字段 （INNER可省略，直接写JOIN）。</p>
<div class="jb51code"><pre class="brush:sql;">-- 简洁写法（省略INNER）
SELECT u.id AS user_id, u.username, o.id AS order_id, o.order_no, o.price
FROM `user` u
JOIN `order` o ON u.id = o.user_id; -- 关联条件：用户ID=订单的用户ID</pre></div>
<p><strong>考察点</strong>：你是否知道ON用于指定表关联条件，WHERE用于过滤查询结果（避免把关联条件写在WHERE中）。</p>
<p class="maodian"><a name="_label3_2_0_1"></a></p><h4>1.2 左外连接（LEFT JOIN / LEFT OUTER JOIN）</h4>
<p><strong>通俗定义</strong>：以 “左表” 为基准，查询左表的所有数据，右表中满足关联条件的数据会被匹配显示，不满足条件的右表字段显示NULL（相当于 “左表全量数据 + 右表交集数据”）。</p>
<p><strong>语法</strong>：SELECT 字段 FROM 左表 LEFT JOIN 右表 ON 关联条件 （OUTER可省略）。</p>
<div class="jb51code"><pre class="brush:sql;">SELECT u.id AS user_id, u.username, o.id AS order_id, o.order_no, o.price
FROM `user` u
LEFT JOIN `order` o ON u.id = o.user_id;</pre></div>
<p><strong>考察点</strong>：能否区分左表和右表，理解 “右表不满足条件显示 NULL” 的特性。</p>
<p class="maodian"><a name="_label3_2_0_2"></a></p><h4>1.3 右外连接（RIGHT JOIN / RIGHT OUTER JOIN）</h4>
<p><strong>通俗定义</strong>：以 “右表” 为基准，查询右表的所有数据，左表中满足关联条件的数据会被匹配显示，不满足条件的左表字段显示NULL（相当于 “右表全量数据 + 左表交集数据”），功能上可通过左连接互换表位置实现。</p>
<p><strong>语法</strong>：SELECT 字段 FROM 左表 RIGHT JOIN 右表 ON 关联条件 （OUTER可省略）。</p>
<div class="jb51code"><pre class="brush:sql;">SELECT u.id AS user_id, u.username, o.id AS order_id, o.order_no, o.price
FROM `user` u
RIGHT JOIN `order` o ON u.id = o.user_id;</pre></div>
<p><strong>考察点</strong>：知道右连接的存在，理解其与左连接的对称关系。</p>
<p class="maodian"><a name="_label3_2_0_3"></a></p><h4>1.4 交叉连接（CROSS JOIN，极少用）</h4>
<p><strong>通俗定义</strong>：无关联条件的连接，会产生两张表的 “笛卡尔积”（数据行数 = 表 1 行数 × 表 2 行数），通常无实际业务意义，仅在特殊场景（比如生成测试数据）使用。</p>
<p>SQL 示例：</p>
<div class="jb51code"><pre class="brush:sql;">-- 产生笛卡尔积，慎用
SELECT u.username, o.order_no
FROM `user` u
CROSS JOIN `order` o;</pre></div>
<p class="maodian"><a name="_lab2_2_1"></a></p><h3>方式 2：子查询（嵌套查询，适合简单场景）</h3>
<p><strong>通俗定义</strong>：将一个查询语句（子查询 / 内层查询）嵌套在另一个查询语句（主查询 / 外层查询）中，子查询的结果作为主查询的条件或数据源，分为 “关联子查询” 和 “非关联子查询”。</p>
<p><strong>非关联子查询</strong>：子查询可独立执行，结果不依赖主查询（适合简单条件过滤）</p>
<ul><li>示例：查询 “购买过订单金额大于 100 元的用户” 信息</li></ul>
<div class="jb51code"><pre class="brush:sql;">SELECT id, username, age
FROM `user`
WHERE id IN ( -- 子查询：获取订单金额>100的所有用户ID
SELECT DISTINCT user_id FROM `order` WHERE price > 100
);</pre></div>
<p><strong>关联子查询</strong>：子查询依赖主查询的字段，无法独立执行（适合 “按主表每条记录匹配子表” 的场景）</p>
<ul><li>示例：查询 “每个用户的最新订单”（按订单 ID 倒序取第一条）</li></ul>
<div class="jb51code"><pre class="brush:sql;">SELECT u.id AS user_id, u.username, o.id AS order_id, o.order_no, o.price
FROM `user` u
JOIN `order` o ON u.id = o.user_id
WHERE o.id = ( -- 子查询：关联主表的user_id，取该用户最大订单ID（最新订单）
SELECT MAX(id) FROM `order` WHERE user_id = u.id
);</pre></div>
<p><strong>考察点</strong>：能否区分关联 / 非关联子查询，知道子查询的适用边界（避免多层嵌套导致性能问题）。</p>
<p class="maodian"><a name="_lab2_2_2"></a></p><h3>方式 3：联合查询（UNION / UNION ALL，结果集合并）</h3>
<p><strong>通俗定义</strong>：将多个SELECT查询的结果集合并成一个结果集，要求所有查询的 “字段数量、字段类型、字段顺序” 一致，分为UNION（去重，会过滤重复记录）和UNION ALL（不去重，性能更高）。</p>
<p><strong>语法</strong>：SELECT 字段 FROM 表1 UNION SELECT 字段 FROM 表2 ;</p>
<div class="jb51code"><pre class="brush:sql;">-- UNION：去重（自动过滤重复的用户记录）
SELECT id, username, age FROM `user` WHERE age > 25
UNION
SELECT u.id, u.username, u.age FROM `user` u
JOIN `order` o ON u.id = o.user_id WHERE o.price > 200;
-- UNION ALL：不去重（性能更高，适合确定无重复数据的场景）
SELECT id, username, age FROM `user` WHERE age > 25
UNION ALL
SELECT u.id, u.username, u.age FROM `user` u
JOIN `order` o ON u.id = o.user_id WHERE o.price > 200;</pre></div>
<p><strong>考察点</strong>：知道UNION和UNION ALL的区别，明确联合查询的字段要求。</p>
<table><thead><tr><th>查询方式</th><th>核心特点</th><th>适用场景</th><th>性能优先级</th></tr></thead><tbody><tr><td>INNER JOIN</td><td>取两表交集，过滤无效数据</td><td>关联数据必须存在的业务（如已下单用户）</td><td>高（优先使用）</td></tr><tr><td>LEFT JOIN</td><td>保留左表全量数据，右表补 NULL</td><td>需显示主表所有数据的场景（如所有用户订单）</td><td>高</td></tr><tr><td>子查询</td><td>嵌套查询，逻辑直观</td><td>简单条件过滤（如根据子查询结果筛选主表）</td><td>中（复杂场景不如 JOIN）</td></tr><tr><td>UNION / UNION ALL</td><td>合并结果集</td><td>多查询结果合并（如不同条件的同类数据）</td><td>中（UNION ALL > UNION）</td></tr></tbody></table>
<p class="maodian"><a name="_lab2_2_3"></a></p><h3>加分项</h3>
<p>1、<strong>结合项目举例</strong>：“我在实训项目中，用LEFT JOIN查询所有用户及对应的订单列表，用INNER JOIN查询有支付记录的用户信息”；</p>
<p>2、<strong>性能意识</strong>：“复杂多表查询优先用JOIN，不用多层子查询；合并结果集时，确定无重复数据就用UNION ALL，比UNION快”；</p>
<p>3、<strong>细节把控</strong>：“JOIN的关联条件写在ON里，过滤条件写在WHERE里，避免笛卡尔积冗余”。</p>
<p class="maodian"><a name="_lab2_2_4"></a></p><h3>举一反三</h3>
<p>1、“如何查询‘没有订单记录的用户’？”（答案：LEFT JOIN后判断订单字段为NULL，SELECT u.* FROM user u LEFT JOIN order o ON u.id=o.user_id WHERE o.id IS NULL）；</p>
<p>2. “ON和WHERE在JOIN查询中有什么区别？”（答案：ON是表关联条件，先执行；WHERE是结果过滤条件，在表关联后执行；LEFT JOIN中，ON不过滤左表数据，WHERE会过滤）；</p>
<p>3. “多表查询时，如何提高性能？”（答案：给关联字段（主键 / 外键）建索引；避免SELECT *，只查需要的字段；减少笛卡尔积产生）。</p>
<p>以上就是MySQL中多表查询的方式总结的详细内容，更多关于MySQL多表查询方式的资料请关注琼殿技术社区其它相关文章！</p>
頁:
[1]