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, '张三', '[email protected]', '北京'),
(2, '李四', '[email protected]', '上海'),
(3, '王五', '[email protected]', '广州'),
(4, '赵六', '[email protected]', '深圳'),
(5, '钱七', '[email protected]', '杭州');

-- 插入订单数据
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
张三	[email protected]	101	笔记本电脑	8000.00
李四	[email protected]	102	智能手机	3000.00
张三	[email protected]	103	平板电脑	2500.00
王五	[email protected]	104	笔记本电脑	8500.00
李四	[email protected]	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
张三	[email protected]	101	笔记本电脑	8000.00
张三	[email protected]	103	平板电脑	2500.00
李四	[email protected]	102	智能手机	3000.00
李四	[email protected]	105	智能手机	3200.00
王五	[email protected]	104	笔记本电脑	8500.00
赵六	[email protected]	NULL	NULL	NULL
钱七	[email protected]	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
张三	[email protected]	101	笔记本电脑	8000.00
李四	[email protected]	102	智能手机	3000.00
张三	[email protected]	103	平板电脑	2500.00
王五	[email protected]	104	笔记本电脑	8500.00
李四	[email protected]	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;