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

游戏开发
2025-09-20 18:18:01

一、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;

结果：

nameorder_idamount张三1001299.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;

结果：

nameorder_idamount张三1001299.00李四NULLNULL

四、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;

结果：

nameorder_idamount张三1001299.00NULL1002599.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;

结果：

nameorder_idamount张三1001299.00李四NULLNULLNULL1002599.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关联上级信息

结果：

employeemanagerCEONULLCTOCEO工程师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;