MySQL嵌套查询详解

嵌套查询(子查询)是MySQL中一个强大的功能，它允许在一个SQL查询中嵌入另一个查询。下面我将详细介绍MySQL嵌套查询的各种用法和实例。

一、嵌套查询基本概念

嵌套查询是指在一个查询语句中嵌入另一个完整的SELECT语句。内层查询(子查询)的结果可以作为外层查询的条件或数据源。

嵌套查询分类

1. 按位置分类：

   • WHERE子句中的子查询
   • FROM子句中的子查询(派生表)
   • SELECT子句中的子查询(标量子查询)

2. 按返回结果分类：

   • 标量子查询(返回单个值)
   • 列子查询(返回一列)
   • 行子查询(返回一行)
   • 表子查询(返回多行多列)

二、WHERE子句中的嵌套查询

1. 比较运算符子查询

-- 查询工资高于平均工资的员工
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 查询与'Smith'在同一个部门的员工
SELECT * FROM employees 
WHERE department_id = (SELECT department_id FROM employees WHERE last_name = 'Smith');

2. IN/NOT IN子查询

-- 查询在销售部或市场部工作的员工
SELECT * FROM employees 
WHERE department_id IN (
    SELECT department_id FROM departments 
    WHERE department_name IN ('Sales', 'Marketing')
);

-- 查询没有订单的客户
SELECT * FROM customers 
WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM orders);

3. ANY/SOME/ALL子查询

-- 查询工资大于IT部门任意一个员工工资的员工
SELECT * FROM employees 
WHERE salary > ANY (
    SELECT salary FROM employees 
    WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT')
);

-- 查询工资大于IT部门所有员工工资的员工
SELECT * FROM employees 
WHERE salary > ALL (
    SELECT salary FROM employees 
    WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT')
);

4. EXISTS/NOT EXISTS子查询

-- 查询有订单的客户
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- 查询没有订单的客户
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

三、FROM子句中的嵌套查询(派生表)

-- 查询每个部门的平均工资，并筛选出高于公司平均工资的部门
SELECT d.department_name, dept_avg.avg_salary
FROM departments d
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_avg ON d.department_id = dept_avg.department_id
WHERE dept_avg.avg_salary > (SELECT AVG(salary) FROM employees);

-- 查询销售额排名前5的产品
SELECT p.product_name, sales.total_sales
FROM products p
JOIN (
    SELECT product_id, SUM(quantity * unit_price) AS total_sales
    FROM order_details
    GROUP BY product_id
    ORDER BY total_sales DESC
    LIMIT 5
) sales ON p.product_id = sales.product_id;

四、SELECT子句中的嵌套查询(标量子查询)

-- 查询每个员工及其所在部门的平均工资
SELECT 
    e.employee_id, 
    e.last_name, 
    e.salary,
    (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS dept_avg_salary
FROM employees e;

-- 查询产品及其类别名称
SELECT 
    p.product_name,
    (SELECT category_name FROM categories WHERE category_id = p.category_id) AS category_name
FROM products p;

五、相关子查询

相关子查询是指子查询中引用了外层查询的列。

-- 查询工资高于其所在部门平均工资的员工
SELECT e1.*
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

-- 查询每个部门工资最高的员工
SELECT e.*
FROM employees e
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department_id = e.department_id
);

六、嵌套查询性能优化

1. 使用JOIN替代IN子查询：

   -- 低效
   SELECT * FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name LIKE '%Electronics%');
   
   -- 高效
   SELECT p.* FROM products p JOIN categories c ON p.category_id = c.category_id 
   WHERE c.category_name LIKE '%Electronics%';
   

2. 使用EXISTS替代IN（当子查询结果集大时）：

   -- 当子查询结果集大时，EXISTS通常更高效
   SELECT * FROM customers c
   WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
   

3. 避免在SELECT子句中使用相关子查询：

   -- 低效
   SELECT 
      e.employee_id, 
      (SELECT COUNT(*) FROM orders WHERE employee_id = e.employee_id) AS order_count
   FROM employees e;
   
   -- 高效
   SELECT 
      e.employee_id, 
      COUNT(o.order_id) AS order_count
   FROM employees e
   LEFT JOIN orders o ON e.employee_id = o.employee_id
   GROUP BY e.employee_id;
   

七、复杂嵌套查询实例

-- 查询每个地区销售额前三的产品
SELECT region_name, product_name, sales_amount
FROM (
    SELECT 
        r.region_name,
        p.product_name,
        SUM(od.quantity * od.unit_price) AS sales_amount,
        RANK() OVER (PARTITION BY r.region_id ORDER BY SUM(od.quantity * od.unit_price) DESC) AS sales_rank
    FROM regions r
    JOIN countries c ON r.region_id = c.region_id
    JOIN customers cu ON c.country_id = cu.country_id
    JOIN orders o ON cu.customer_id = o.customer_id
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
    GROUP BY r.region_id, r.region_name, p.product_id, p.product_name
) ranked_sales
WHERE sales_rank <= 3;

八、注意事项

1. 子查询必须用括号括起来
2. 子查询通常不能包含ORDER BY子句(除非与LIMIT一起使用)
3. 相关子查询可能性能较差，应考虑重写
4. 子查询深度不宜过深，通常不超过3层

通过合理使用嵌套查询，可以解决许多复杂的业务问题，但也要注意性能优化，避免过度使用导致查询效率下降。