七、JOIN 语法详解与实战示例

一、JOIN 的作用与分类

JOIN 操作用于合并两个或多个表的行，基于表之间的关联字段。以下是常见的 JOIN 类型：

JOIN 类型	描述
INNER JOIN	返回两个表匹配的记录
LEFT JOIN	返回左表所有记录 + 右表匹配记录（右表无匹配则为NULL）
RIGHT JOIN	返回右表所有记录 + 左表匹配记录（左表无匹配则为NULL）
FULL JOIN	返回所有记录（MySQL不支持，可用UNION模拟）
CROSS JOIN	返回笛卡尔积（所有可能的组合）

---

二、INNER JOIN（内连接）

语法与作用

SELECT 字段
FROM 表A
INNER JOIN 表B ON 表A.字段 = 表B.字段;

• 作用：仅返回两表中匹配的行
• 使用场景：需要精确关联数据的场景（如订单与用户信息关联）

示例

表结构：

-- 用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(50)
);
INSERT INTO users VALUES (1, '张三'), (2, '李四');

-- 订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2)
);
INSERT INTO orders VALUES (1001, 1, 299.00), (1002, 3, 599.00);

查询：获取有订单的用户信息

SELECT u.name, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;

结果：

name	order_id	amount
张三	1001	299.00

---

三、LEFT JOIN（左连接）

语法与作用

SELECT 字段
FROM 表A
LEFT JOIN 表B ON 表A.字段 = 表B.字段;

• 作用：返回左表所有记录，右表无匹配则显示NULL
• 使用场景：统计所有用户的订单情况（包括未下单用户）

示例

查询：统计所有用户的订单（含未下单用户）

SELECT u.name, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

结果：

name	order_id	amount
张三	1001	299.00
李四	NULL	NULL

---

四、RIGHT JOIN（右连接）

语法与作用

SELECT 字段
FROM 表A
RIGHT JOIN 表B ON 表A.字段 = 表B.字段;

• 作用：返回右表所有记录，左表无匹配则显示NULL
• 使用场景：查找所有订单对应的用户（包括无效用户订单）

示例

查询：显示所有订单及用户信息

SELECT u.name, o.order_id, o.amount
FROM users u
RIGHT JOIN orders o ON u.user_id = o.user_id;

结果：

name	order_id	amount
张三	1001	299.00
NULL	1002	599.00

---

五、FULL JOIN（全连接）

语法与作用（MySQL实现方式）

SELECT 字段
FROM 表A
LEFT JOIN 表B ON 表A.字段 = 表B.字段
UNION
SELECT 字段
FROM 表A
RIGHT JOIN 表B ON 表A.字段 = 表B.字段;

• 作用：返回所有记录（类似LEFT JOIN + RIGHT JOIN去重）
• 使用场景：需要同时保留两个表所有记录的统计

示例

查询：合并用户和订单的所有记录

SELECT u.name, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
UNION
SELECT u.name, o.order_id, o.amount
FROM users u
RIGHT JOIN orders o ON u.user_id = o.user_id;

结果：

name	order_id	amount
张三	1001	299.00
李四	NULL	NULL
NULL	1002	599.00

---

六、复合条件 JOIN

多表关联

-- 三表关联示例
SELECT 
    u.name,
    o.order_id,
    p.product_name
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
INNER JOIN products p ON o.product_id = p.product_id;

多条件关联

-- 日期范围关联
SELECT 
    e.emp_name,
    d.dept_name,
    s.salary
FROM employees e
LEFT JOIN salaries s 
  ON e.emp_id = s.emp_id 
  AND s.effective_date BETWEEN '2023-01-01' AND '2023-12-31';

---

七、自连接（Self Join）

语法与作用

SELECT A.字段, B.字段
FROM 表 AS A
JOIN 表 AS B ON A.关联字段 = B.关联字段;

• 使用场景：层级数据查询（如员工与上级经理）

示例

表结构：

-- 创建员工表（包含员工ID、姓名和直属上级ID）
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,       -- 员工ID（主键）
    name VARCHAR(50),            -- 员工姓名
    manager_id INT               -- 直属上级的员工ID（引用emp_id）
);

-- 插入示例数据（构建管理层级关系）
INSERT INTO employees VALUES
(1, 'CEO', NULL),   -- CEO没有上级（manager_id为NULL）
(2, 'CTO', 1),      -- CTO的上级是CEO（emp_id=1）
(3, '工程师', 2);    -- 工程师的上级是CTO（emp_id=2）

查询：显示员工及其上级

-- 查询员工及其对应上级姓名（包含无上级的员工）
SELECT 
    e.name AS employee,   -- 员工姓名
    m.name AS manager     -- 上级姓名
FROM employees e
LEFT JOIN employees m     -- 自连接：将员工表同时作为员工和上级表使用
  ON e.manager_id = m.emp_id;  -- 通过manager_id关联上级信息

结果：

employee	manager
CEO	NULL
CTO	CEO
工程师	CTO

---

八、常见错误与解决方法

1. 笛卡尔积问题

错误示例：

SELECT * FROM users, orders;  -- 未指定关联条件

结果：用户数 × 订单数 条记录（如2用户×2订单=4条）

正确写法：

SELECT * FROM users
JOIN orders ON users.user_id = orders.user_id;

2. 别名使用不当

错误示例：

SELECT user_id FROM users u
JOIN orders o ON users.user_id = o.user_id;  -- 错误：未使用别名

正确写法：

SELECT u.user_id FROM users u
JOIN orders o ON u.user_id = o.user_id;

---

九、最佳实践建议

1. 优先使用 INNER JOIN：明确需要关联数据时使用
2. 慎用 RIGHT JOIN：可通过调换表顺序改用 LEFT JOIN
3. 使用表别名：提高可读性（如 users u）
4. 关联字段加索引：显著提升 JOIN 性能
5. 避免 SELECT：明确列出需要字段