MySQL 连接的使用 - MySQL 教程

JOIN 简介

JOIN 用于根据两个或多个表中的相关列之间的关系，从这些表中查询数据。MySQL 支持多种类型的连接，每种连接都有其特定的用途和行为。

                    JOIN 的类型：
                    INNER JOIN：内连接，返回两表中匹配的记录
LEFT JOIN：左外连接，返回左表所有记录和右表匹配记录
RIGHT JOIN：右外连接，返回右表所有记录和左表匹配记录
FULL OUTER JOIN：全外连接（MySQL 不直接支持）
CROSS JOIN：交叉连接，返回笛卡尔积
SELF JOIN：自连接，表与自身连接

                

示例数据准备

-- 创建用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    city VARCHAR(50)
);

-- 创建订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    product_name VARCHAR(100),
    amount DECIMAL(10,2),
    order_date DATE
);

-- 创建产品表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
);

-- 插入用户数据
INSERT INTO users VALUES
(1, '张三', 'zhangsan@email.com', '北京'),
(2, '李四', 'lisi@email.com', '上海'),
(3, '王五', 'wangwu@email.com', '广州'),
(4, '赵六', 'zhaoliu@email.com', '深圳'),
(5, '钱七', 'qianqi@email.com', '杭州');

-- 插入订单数据
INSERT INTO orders VALUES
(101, 1, '笔记本电脑', 8000.00, '2023-01-15'),
(102, 2, '智能手机', 3000.00, '2023-01-20'),
(103, 1, '平板电脑', 2500.00, '2023-02-10'),
(104, 3, '笔记本电脑', 8500.00, '2023-02-15'),
(105, 2, '智能手机', 3200.00, '2023-03-05'),
(106, 6, '游戏机', 2800.00, '2023-03-10');  -- user_id=6 在users表中不存在

-- 插入产品数据
INSERT INTO products VALUES
(1, '笔记本电脑', '电脑', 8000.00),
(2, '智能手机', '手机', 3000.00),
(3, '平板电脑', '电脑', 2500.00),
(4, '智能手表', '穿戴设备', 1500.00);  -- 没有对应的订单
                

INNER JOIN（内连接）

INNER JOIN 返回两个表中都有匹配记录的行。这是最常用的连接类型。

INNER JOIN 示意图：
表A ∩ 表B（交集）

基本语法

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
                

实际示例

-- 查询有订单的用户信息
SELECT u.username, u.email, o.order_id, o.product_name, o.amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;
                

username	email	order_id	product_name	amount
张三	zhangsan@email.com	101	笔记本电脑	8000.00
李四	lisi@email.com	102	智能手机	3000.00
张三	zhangsan@email.com	103	平板电脑	2500.00
王五	wangwu@email.com	104	笔记本电脑	8500.00
李四	lisi@email.com	105	智能手机	3200.00

LEFT JOIN（左外连接）

LEFT JOIN 返回左表的所有记录，以及右表中匹配的记录。如果右表没有匹配的记录，则显示 NULL。

LEFT JOIN 示意图：
表A + (表A ∩ 表B)

基本语法

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
                

实际示例

-- 查询所有用户及其订单信息（包括没有订单的用户）
SELECT u.username, u.email, o.order_id, o.product_name, o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
ORDER BY u.user_id;
                

username	email	order_id	product_name	amount
张三	zhangsan@email.com	101	笔记本电脑	8000.00
张三	zhangsan@email.com	103	平板电脑	2500.00
李四	lisi@email.com	102	智能手机	3000.00
李四	lisi@email.com	105	智能手机	3200.00
王五	wangwu@email.com	104	笔记本电脑	8500.00
赵六	zhaoliu@email.com	NULL	NULL	NULL
钱七	qianqi@email.com	NULL	NULL	NULL

查找没有订单的用户

-- 查询没有下过订单的用户
SELECT u.username, u.email
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.user_id IS NULL;
                

RIGHT JOIN（右外连接）

RIGHT JOIN 返回右表的所有记录，以及左表中匹配的记录。如果左表没有匹配的记录，则显示 NULL。

RIGHT JOIN 示意图：
(表A ∩ 表B) + 表B

实际示例

-- 查询所有订单及其用户信息（包括无效用户的订单）
SELECT u.username, u.email, o.order_id, o.product_name, o.amount
FROM users u
RIGHT JOIN orders o ON u.user_id = o.user_id
ORDER BY o.order_id;
                

username	email	order_id	product_name	amount
张三	zhangsan@email.com	101	笔记本电脑	8000.00
李四	lisi@email.com	102	智能手机	3000.00
张三	zhangsan@email.com	103	平板电脑	2500.00
王五	wangwu@email.com	104	笔记本电脑	8500.00
李四	lisi@email.com	105	智能手机	3200.00
NULL	NULL	106	游戏机	2800.00

FULL OUTER JOIN（全外连接）

MySQL 不直接支持 FULL OUTER JOIN，但可以通过 UNION 组合 LEFT JOIN 和 RIGHT JOIN 来实现。

-- 模拟 FULL OUTER JOIN
SELECT u.username, u.email, o.order_id, o.product_name, o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
UNION
SELECT u.username, u.email, o.order_id, o.product_name, o.amount
FROM users u
RIGHT JOIN orders o ON u.user_id = o.user_id;
                

CROSS JOIN（交叉连接）

CROSS JOIN 返回两个表的笛卡尔积，即第一个表的每一行与第二个表的每一行组合。

基本语法

SELECT columns
FROM table1
CROSS JOIN table2;

-- 或者
SELECT columns
FROM table1, table2;
                

实际示例

-- 生成用户和产品的所有组合
SELECT u.username, p.product_name, p.price
FROM users u
CROSS JOIN products p
WHERE u.user_id <= 2 AND p.product_id <= 2;  -- 限制结果数量
                

SELF JOIN（自连接）

自连接是表与自身的连接，通常用于处理层次结构数据或比较同一表中的不同行。

示例：员工管理结构

-- 创建员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    manager_id INT,
    department VARCHAR(50)
);

-- 插入数据
INSERT INTO employees VALUES
(1, '张总', NULL, '管理层'),
(2, '李经理', 1, '销售部'),
(3, '王经理', 1, '技术部'),
(4, '赵员工', 2, '销售部'),
(5, '钱员工', 2, '销售部'),
(6, '孙员工', 3, '技术部');

-- 查询员工及其直接上级
SELECT 
    e.emp_name as employee,
    m.emp_name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
                

多表连接

可以在一个查询中连接多个表，获取更复杂的数据关系。

三表连接示例

-- 查询用户、订单和产品的完整信息
SELECT 
    u.username,
    u.city,
    o.order_id,
    o.order_date,
    p.product_name,
    p.category,
    o.amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
INNER JOIN products p ON o.product_name = p.product_name
ORDER BY o.order_date;
                

复杂连接条件

-- 使用多个连接条件
SELECT 
    u.username,
    o.product_name,
    o.amount,
    p.price,
    (o.amount - p.price) as price_difference
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
INNER JOIN products p ON o.product_name = p.product_name 
    AND o.amount >= p.price  -- 额外的连接条件
WHERE u.city IN ('北京', '上海');
                

JOIN 性能优化

                    JOIN 性能优化技巧：
                    在连接列上创建索引
使用适当的连接类型
优化连接顺序
使用 WHERE 子句减少数据量
避免不必要的列选择

                

索引优化

-- 为连接列创建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_user_id ON users(user_id);

-- 复合索引
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
                

查看执行计划

-- 分析 JOIN 查询的执行计划
EXPLAIN SELECT u.username, o.product_name, o.amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;
                

常见错误和注意事项

常见错误：

忘记指定连接条件导致笛卡尔积
混淆不同类型的 JOIN
在大表上进行无索引连接
不理解 NULL 值在连接中的行为

避免笛卡尔积

-- 错误：缺少连接条件
SELECT u.username, o.product_name
FROM users u, orders o;  -- 产生笛卡尔积

-- 正确：指定连接条件
SELECT u.username, o.product_name
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;
                

处理 NULL 值

-- 注意 NULL 值的比较
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.user_id IS NULL;  -- 查找没有订单的用户

-- 错误的写法
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.user_id = NULL;  -- 这样写查不到结果
                

实际应用场景

1. 电商订单分析

-- 分析用户购买行为
SELECT 
    u.username,
    u.city,
    COUNT(o.order_id) as order_count,
    SUM(o.amount) as total_spent,
    AVG(o.amount) as avg_order_value
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username, u.city
ORDER BY total_spent DESC;
                

2. 库存管理

-- 查询产品销售情况
SELECT 
    p.product_name,
    p.category,
    p.price,
    COUNT(o.order_id) as sales_count,
    COALESCE(SUM(o.amount), 0) as total_revenue
FROM products p
LEFT JOIN orders o ON p.product_name = o.product_name
GROUP BY p.product_id, p.product_name, p.category, p.price
ORDER BY sales_count DESC;
                

3. 数据完整性检查

-- 查找孤立的订单（用户不存在）
SELECT o.order_id, o.user_id, o.product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE u.user_id IS NULL;
                

🔗 MySQL 连接的使用

JOIN 简介

示例数据准备

INNER JOIN（内连接）

基本语法

实际示例

LEFT JOIN（左外连接）

基本语法

实际示例

查找没有订单的用户

RIGHT JOIN（右外连接）

实际示例

FULL OUTER JOIN（全外连接）

CROSS JOIN（交叉连接）

基本语法

实际示例

SELF JOIN（自连接）

示例：员工管理结构

多表连接

三表连接示例

复杂连接条件

JOIN 性能优化

索引优化

查看执行计划

常见错误和注意事项

避免笛卡尔积

处理 NULL 值

实际应用场景

1. 电商订单分析

2. 库存管理

3. 数据完整性检查