MySQL中的嵌套join如果使用With替代会怎样

MySQL 中的 WITH 关键字用于定义 公用表表达式（CTE，Common Table Expressions），它可以在查询中创建临时的结果集，并在 SELECT、INSERT、UPDATE 或 DELETE 查询中重复使用。

这使得查询变得更加简洁、可读，并且易于维护。WITH 语句在 MySQL 8.0 版本及以上可用。

本文涉及到的脚本测试请在个人测试库进行。

使用场景

1. 简化复杂查询：当查询中需要多次引用某些相同的子查询时，使用 WITH 语句定义公用表表达式，可以避免重复编写相同的查询逻辑，使查询更加清晰。

2. 提高可读性：WITH 使得查询结构更加直观，尤其是涉及多个联合（JOIN）、聚合（GROUP BY）等操作时。

3. 递归查询：WITH RECURSIVE 允许执行递归查询，适用于树形结构数据，如组织结构、产品分类等。

4. 多层嵌套查询：当查询包含多层嵌套子查询时，WITH 使得每个子查询都能被命名并独立出来，提高查询的可理解性和维护性。

基本语法

WITH cte_name AS (
    -- 子查询
    SELECT ...
    FROM ...
    WHERE ...
)
-- 使用 CTE 的查询
SELECT ...
FROM cte_name;

对比下JOIN

这个例子展示了如何通过 WITH 语句优化查询，以减少重复计算，提升可读性和性能。

将以一个简单的电商数据库为例，包含 customers、orders 和 order_items 三个表。的目标是获取每个客户的总订单金额。

-- 创建 customers 表
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100)
);

-- 创建 orders 表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 创建 order_items 表
CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),
    quantity INT,
    unit_price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- 插入测试数据
INSERT INTO customers (customer_name) VALUES
('Alice'),
('Bob'),
('Charlie');

INSERT INTO orders (customer_id, order_date) VALUES
(1, '2024-10-01'),
(1, '2024-10-05'),
(2, '2024-10-03'),
(3, '2024-10-07');

INSERT INTO order_items (order_id, product_name, quantity, unit_price) VALUES
(1, 'Laptop', 1, 1000.00),
(1, 'Phone', 2, 500.00),
(2, 'Tablet', 1, 300.00),
(3, 'Headphones', 2, 100.00),
(3, 'Mouse', 1, 50.00);

使用JOIN

首先，用 嵌套 JOIN 来查询每个客户的总订单金额。这个查询通过多次连接三个表来计算每个客户的总支出。

SELECT c.customer_name, SUM(oi.quantity * oi.unit_price) AS total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id;

查询执行的步骤：

• 首先，customers 和 orders 表通过 customer_id 连接。
• 然后，orders 和 order_items 表通过 order_id 连接。
• 最后，计算每个客户的订单总金额。

查询结果：

customer_name	total_amount
Alice	2500.00
Bob	300.00
Charlie	250.00

使用 WITH (公用表表达式) 提升查询效率

通过使用 WITH 语句，可以将中间步骤提取出来，以避免重复计算，从而提高查询效率和可读性。

WITH order_summary AS (
    SELECT o.customer_id, oi.order_id, SUM(oi.quantity * oi.unit_price) AS total_order_amount
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.customer_id, oi.order_id
)
SELECT c.customer_name, SUM(os.total_order_amount) AS total_amount
FROM customers c
JOIN order_summary os ON c.customer_id = os.customer_id
GROUP BY c.customer_id;

查询执行的步骤：

• 使用 WITH 语句先计算每个订单的总金额：order_summary 临时表保存了每个订单的总金额。
• 然后，通过 customer_id 将 customers 和 order_summary 表连接，计算每个客户的总支出。

查询结果：

customer_name	total_amount
Alice	2500.00
Bob	300.00
Charlie	250.00

性能对比分析

嵌套 JOIN 的性能：

• 每次执行查询时，MySQL 必须从头开始连接多个表，尤其是在较大的数据集上，可能导致多次计算。
• 每个连接操作都必须在查询时进行，因此可能会增加执行的复杂性和计算量。

使用 WITH 的性能优势：

• 通过将中间结果存储在 WITH 子句中，可以避免在多个 JOIN 中重复计算。
• 这通常会减少数据库的负载，尤其是当中间计算结果较大时，可以提高查询效率。
• WITH 表达式使查询更加清晰，且更易于调试和维护。

对于这个简单的示例来说，性能差异可能不会非常显著，但在数据量非常大的情况下，使用 WITH 会显著减少重复计算，提高查询效率。

示例 1：简化复杂查询

1. 创建表并插入测试数据

假设有一个订单表 orders，希望查询出每个客户的订单总额，并且显示出客户名称、订单数量和总金额。

-- 创建 orders 表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2)
);

-- 创建 customers 表
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100)
);

-- 插入测试数据
INSERT INTO customers (customer_name) VALUES
('Alice'),
('Bob'),
('Charlie');

INSERT INTO orders (customer_id, amount) VALUES
(1, 100.00),
(1, 150.00),
(2, 200.00),
(3, 50.00),
(3, 120.00);

2. 查询语句

WITH customer_order_summary AS (
    SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT c.customer_name, cos.order_count, cos.total_amount
FROM customers c
JOIN customer_order_summary cos ON c.customer_id = cos.customer_id;

查询结果：

customer_name	order_count	total_amount
Alice	2	250.00
Bob	1	200.00
Charlie	2	170.00

在这个查询中：

• WITH 子句定义了一个公用表表达式 customer_order_summary，它包含了每个客户的订单数量和总金额。
• 然后在主查询中，将 customer_order_summary 与 customers 表连接，以便获取每个客户的详细信息。

这种方式避免了在主查询中重复编写相同的 COUNT 和 SUM 聚合逻辑，使得查询更简洁和易于维护。

示例 2：使用递归查询

递归查询在处理具有层级结构的数据时非常有用，比如处理组织结构、产品分类等。

假设有一个员工表 employees，包含 employee_id、manager_id 和 employee_name 字段，其中 manager_id 指向上级员工的 employee_id。想要查询某个员工的所有下属（直接和间接的）。

1. 创建表并插入测试数据

-- 创建 employees 表
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INT
);

-- 插入测试数据
INSERT INTO employees (employee_name, manager_id) VALUES
('CEO', NULL),
('Alice', 1),  -- Alice 是 CEO 的下属
('Bob', 1),    -- Bob 是 CEO 的下属
('Charlie', 2),  -- Charlie 是 Alice 的下属
('David', 2);  -- David 是 Alice 的下属

2. 查询语句

WITH RECURSIVE subordinates AS (
    -- 查询直接下属
    SELECT employee_id, employee_name, manager_id
    FROM employees
    WHERE manager_id = 1  -- 假设查询CEO的下属
    UNION ALL
    -- 递归查询所有下属
    SELECT e.employee_id, e.employee_name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT employee_id, employee_name
FROM subordinates;

查询结果：

employee_id	employee_name
2	Alice
3	Bob
4	Charlie
5	David

在这个递归查询中：

• WITH RECURSIVE 子句定义了一个递归的公用表表达式 subordinates，用于查询员工及其下属。
• 第一个 SELECT 从 employees 表中选择直接下属（manager_id = 1）。
• 第二个 SELECT 递归地查询所有下属，通过 JOIN 连接上一层的下属，逐步查找更深层次的下属。

最终，查询结果会返回指定员工（例如 ID 为 1）的所有直接和间接下属。

示例 3：多层次的公用表表达式

如果一个查询涉及多个子查询，可以通过 WITH 定义多个 CTE（公用表表达式），从而避免嵌套查询，使查询结构更加清晰。

假设有一个销售订单表 sales 和产品表 products，希望查找每个产品的销售数量和销售总额，并且对销售数量进行排序。

1. 创建表并插入测试数据

-- 创建 products 表
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100)
);

-- 创建 sales 表
CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    quantity INT,
    total_price DECIMAL(10, 2),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 插入测试数据
INSERT INTO products (product_name) VALUES
('Product A'),
('Product B'),
('Product C');

INSERT INTO sales (product_id, quantity, total_price) VALUES
(1, 10, 100.00),
(1, 5, 50.00),
(2, 8, 120.00),
(3, 3, 60.00),
(3, 7, 140.00);

2. 查询语句

WITH product_sales AS (
    SELECT product_id, SUM(quantity) AS total_quantity, SUM(total_price) AS total_sales
    FROM sales
    GROUP BY product_id
),
product_details AS (
    SELECT p.product_id, p.product_name, ps.total_quantity, ps.total_sales
    FROM products p
    JOIN product_sales ps ON p.product_id = ps.product_id
)
SELECT product_name, total_quantity, total_sales
FROM product_details
ORDER BY total_quantity DESC;

查询结果：

product_name	total_quantity	total_sales
Product A	15	150.00
Product C	10	200.00
Product B	8	120.00

在这个查询中：

• 第一个 CTE product_sales 聚合了每个产品的销售数量和总金额。
• 第二个 CTE product_details 将产品信息与销售数据结合起来。
• 最后的查询通过排序展示了按销售数量降序排列的产品及其销售情况。

示例 4：避免重复查询逻辑

如果查询中需要多次引用相同的子查询结果，使用 WITH 可以避免重复执行相同的子查询。

假设有一个 employees 表，想要查询每个部门的员工数量和平均工资：

1. 创建表并插入测试数据

-- 创建 employees 表
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2)
);

-- 创建 departments 表
CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100)
);

-- 插入测试数据
INSERT INTO departments (department_name) VALUES
('HR'),
('Engineering'),
('Sales');

INSERT INTO employees (employee_name, department_id, salary) VALUES
('Alice', 1, 5000.00),
('Bob', 1, 6000.00),
('Charlie', 2, 7000.00),
('David', 2, 7500.00),
('Eve', 3, 4000.00),
('Frank', 3, 4500.00);

2. 查询语句

WITH department_summary AS (
    SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name, ds.employee_count, ds.avg_salary
FROM departments d
JOIN department_summary ds ON d.department_id = ds.department_id;

查询结果：

department_name	employee_count	avg_salary
HR	2	5500.00
Engineering	2	7250.00
Sales	2	4250.00

在这个查询中：

• WITH 定义了一个 CTE department_summary，它包含每个部门的员工数量和平均工资。
• 在主查询中，将 department_summary 与 departments 表连接，从而查询每个部门的详细信息。

总结

MySQL 中的 WITH 语句（公用表表达式，CTE）可以大大提升查询的可读性、可维护性和性能，尤其是在处理复杂查询、递归查询和多次引用相同子查询时。通过合理地使用 WITH，可以使 SQL 查询更加清晰和简洁。

• 示例 1：简化复杂查询，避免重复的聚合逻辑。
• 示例 2：递归查询，查找某个员工的所有下属。
• 示例 3：多层次的公用表表达式，简化多次子查询。
• 示例 4：避免重复查询逻辑，提高查询可读性。

关于作者

来自全栈程序员nine的探索与实践，持续迭代中。（卫星codetrend）