mysql连接，常用的连接类型及使用方法
时间：2025-09-15 16:17
在 MySQL 中，“连接（JOIN）” 是用于将多个表的数据按指定条件关联查询的核心操作，通过关联不同表中相关联的字段（如主键与外键），可以一次性获取来自多个表的组合数据。以下是 MySQL 中常用的连接类型及使用方法：
一、连接的基本概念
连接的本质是通过 关联字段（如
user.id 和 order.user_id）将多个表 “拼接” 起来，关联字段通常是一个表的主键（唯一标识）和另一个表的外键（引用前者的字段）。
核心语法结构：
sql
SELECT 字段列表
FROM 表1
[连接类型] JOIN 表2
ON 表1.关联字段 = 表2.关联字段
[WHERE 过滤条件];
-
ON子句：指定连接条件（必选），定义两个表如何关联； - 连接类型：决定如何处理 “不满足连接条件” 的记录（不同连接类型结果不同）。
二、常用连接类型及示例
假设有以下两张表用于示例：
-
user表（用户信息）：id（主键）、name（姓名） -
order表（订单信息）：id（订单 ID）、user_id（外键，关联user.id）、amount（金额）
1. 内连接（INNER JOIN）：只保留匹配的记录
作用：仅返回两个表中 满足连接条件 的记录（即 “交集” 部分）。
语法：
sql
SELECT 表1.字段, 表2.字段
FROM 表1
INNER JOIN 表2
ON 表1.关联字段 = 表2.关联字段;
（
INNER 可省略，直接写 JOIN）
示例：查询 “有订单的用户” 及其订单信息（只显示有匹配的用户和订单）：
sql
SELECT
u.id AS 用户ID,
u.name AS 用户名,
o.id AS 订单ID,
o.amount AS 订单金额
FROM
user u -- 别名 u 简化写法
INNER JOIN
`order` o -- 别名 o
ON
u.id = o.user_id; -- 关联条件：用户ID = 订单的用户ID
结果特点：
- 只包含 “有订单的用户” 和 “属于这些用户的订单”；
-
没有订单的用户（
user表中无匹配order记录）不会显示； -
没有对应用户的订单（
order.user_id无效）也不会显示。
2. 左连接（LEFT JOIN）：保留左表所有记录
作用：返回 左表的所有记录，以及右表中满足连接条件的记录；若右表无匹配，右表字段显示
NULL。
语法：
sql
SELECT 字段列表
FROM 左表
LEFT JOIN 右表
ON 左表.关联字段 = 右表.关联字段;
（
LEFT JOIN 也可写为 LEFT OUTER JOIN，OUTER 可省略）
示例：查询 “所有用户” 及其订单信息（包括没有订单的用户）：
sql
SELECT
u.id AS 用户ID,
u.name AS 用户名,
o.id AS 订单ID,
o.amount AS 订单金额
FROM
user u -- 左表：user（所有用户都要显示）
LEFT JOIN
`order` o -- 右表：order
ON
u.id = o.user_id;
结果特点：
-
左表（
user）的所有用户都会显示； - 有订单的用户：显示对应的订单信息；
-
无订单的用户：订单相关字段（
o.id、o.amount）显示NULL。
3. 右连接（RIGHT JOIN）：保留右表所有记录
作用：与左连接相反，返回 右表的所有记录，以及左表中满足连接条件的记录；若左表无匹配，左表字段显示
NULL。
语法：
sql
SELECT 字段列表
FROM 左表
RIGHT JOIN 右表
ON 左表.关联字段 = 右表.关联字段;
（
RIGHT JOIN 也可写为 RIGHT OUTER JOIN）
示例：查询 “所有订单” 及对应的用户信息（包括无效用户的订单）：
sql
SELECT
u.id AS 用户ID,
u.name AS 用户名,
o.id AS 订单ID,
o.amount AS 订单金额
FROM
user u -- 左表
RIGHT JOIN
`order` o -- 右表：order（所有订单都要显示）
ON
u.id = o.user_id;
结果特点：
-
右表（
order）的所有订单都会显示； - 有对应用户的订单：显示用户信息；
-
无对应用户的订单（如
user_id不存在）：用户相关字段（u.id、u.name）显示NULL。
4. 全连接（FULL JOIN）：保留所有表的记录（MySQL 不直接支持）
作用：返回左表和右表的所有记录，匹配的记录合并，不匹配的部分用
NULL 填充（即 “左连接 + 右连接” 的并集）。
注意：MySQL 不直接支持
FULL JOIN，需用 UNION 组合左连接和右连接的结果实现：
sql
-- 左连接结果 + 右连接中左表无匹配的记录
SELECT u.id, u.name, o.id, o.amount
FROM user u
LEFT JOIN `order` o ON u.id = o.user_id
UNION -- 合并结果并去重
SELECT u.id, u.name, o.id, o.amount
FROM user u
RIGHT JOIN `order` o ON u.id = o.user_id
WHERE u.id IS NULL; -- 筛选右表中左表无匹配的记录
结果特点：包含所有用户和所有订单，无论是否匹配。
三、连接的高级用法
1. 多表连接（3 张及以上表）
通过连续使用
JOIN 可关联多张表，例如关联 user（用户）、order（订单）、product（商品）：
sql
SELECT
u.name AS 用户名,
o.id AS 订单ID,
p.name AS 商品名
FROM
user u
JOIN
`order` o ON u.id = o.user_id
JOIN
product p ON o.product_id = p.id; -- 订单关联商品
2. 连接 + 条件过滤（WHERE 与 ON 的区别）
-
ON：仅用于 指定连接条件，过滤 “表之间如何关联”，不影响表本身的记录数（左 / 右连接中，不满足ON条件的记录会保留，用NULL填充）； -
WHERE：用于 过滤连接后的结果，会直接排除不满足条件的记录。
示例对比：
sql
-- 1. ON 中加条件：左连接保留所有用户，仅关联“金额>100”的订单
SELECT u.name, o.amount
FROM user u
LEFT JOIN `order` o
ON u.id = o.user_id AND o.amount > 100; -- 连接时过滤订单
-- 2. WHERE 中加条件：连接后过滤，会排除“无订单”或“订单金额≤100”的用户
SELECT u.name, o.amount
FROM user u
LEFT JOIN `order` o ON u.id = o.user_id
WHERE o.amount > 100; -- 结果等同于内连接（因 NULL 不满足 >100）
3. 自连接：表与自身连接
将一张表当作两张表来关联（需用别名区分），常用于查询 “表中存在层级关系” 的数据（如员工表中 “员工” 与 “上级”）。
示例：
employee 表（id、name、manager_id（上级 ID，关联自身 id）），查询每个员工及其上级姓名：
sql
SELECT
e.name AS 员工姓名,
m.name AS 上级姓名
FROM
employee e -- 员工表（别名 e）
LEFT JOIN
employee m -- 当作上级表（别名 m）
ON
e.manager_id = m.id; -- 员工的上级ID = 上级的ID
四、注意事项
-
关联字段索引：连接的关联字段（如
user.id、order.user_id）建议创建索引，否则大表连接会导致全表扫描，性能极差。 -
表别名：多表连接时务必使用别名（如
u、o），简化 SQL 并避免字段名冲突（如user.id和order.id）。 -
避免笛卡尔积：若忘记写
ON条件，会产生 “笛卡尔积”（左表每行与右表每行都匹配），导致结果集过大（如 1000 行 × 1000 行 = 100 万行），务必注意。 - 优先内连接：内连接性能通常优于外连接（因需处理的记录更少），非必要不使用外连接。
通过合理使用连接，可以高效地从多个表中整合数据，是 MySQL 查询中不可或缺的技能。实际开发中，需根据业务需求选择合适的连接类型，并注意索引优化以提升性能。
