当前位置：首页 > article >正文

MySQL之不相关子查询

article 2025/2/24 6:28:55

MySQL 不相关子查询详解

在MySQL中，子查询（Subquery）是指在一个查询语句中嵌套另一个查询语句。子查询可以分为相关子查询和不相关子查询。今天我们要详细讨论的是不相关子查询。

什么是不相关子查询？

不相关子查询是指子查询的执行不依赖于外部查询。也就是说，子查询可以独立运行，并且它的结果会被外部查询使用。不相关子查询通常用于从另一个表中获取数据，然后将这些数据用于外部查询的条件或计算。

不相关子查询的特点

独立性：子查询可以独立运行，不依赖于外部查询。
执行顺序：子查询先执行，外部查询后执行。
结果集：子查询的结果集通常是一个单一的值、一列值或一个表。

不相关子查询的使用场景

WHERE子句中的子查询：用于过滤外部查询的结果。
SELECT子句中的子查询：用于计算或获取额外的列。
FROM子句中的子查询：用于生成一个临时表，供外部查询使用。

不相关子查询的语法

SELECT column1, column2, ...
FROM table1
WHERE column_name OPERATOR (SELECT column_name FROM table2 WHERE condition);

OPERATOR 可以是 =, >, <, IN, EXISTS 等。
子查询 (SELECT column_name FROM table2 WHERE condition) 会先执行，然后将结果传递给外部查询。

代码示例

为了更好地理解不相关子查询，我们通过几个具体的例子来演示。

示例1：在WHERE子句中使用不相关子查询

假设我们有两个表：employees 和 departments。

employees 表结构：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

departments 表结构：

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

插入一些示例数据：

INSERT INTO departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Finance');

INSERT INTO employees (employee_id, first_name, last_name, department_id, salary) VALUES
(1, 'John', 'Doe', 1, 50000),
(2, 'Jane', 'Smith', 2, 60000),
(3, 'Alice', 'Johnson', 2, 70000),
(4, 'Bob', 'Brown', 3, 80000);

现在，我们想要查询所有在 IT 部门的员工。可以使用不相关子查询来实现：

SELECT first_name, last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');

运行结果：

first_name	last_name
Jane	Smith
Alice	Johnson

解释：

子查询 (SELECT department_id FROM departments WHERE department_name = 'IT') 先执行，返回 IT 部门的 department_id，即 2。
外部查询 SELECT first_name, last_name FROM employees WHERE department_id = 2 执行，返回所有 department_id 为 2 的员工。

示例2：在SELECT子句中使用不相关子查询

假设我们想要查询每个员工的姓名和他们的部门名称。可以使用不相关子查询来实现：

SELECT 
    first_name, 
    last_name, 
    (SELECT department_name FROM departments WHERE departments.department_id = employees.department_id) AS department_name
FROM employees;

运行结果：

first_name	last_name	department_name
John	Doe	HR
Jane	Smith	IT
Alice	Johnson	IT
Bob	Brown	Finance

解释：

子查询 (SELECT department_name FROM departments WHERE departments.department_id = employees.department_id) 为每个员工执行一次，返回对应的部门名称。
外部查询 SELECT first_name, last_name, ... FROM employees 返回每个员工的姓名和部门名称。

示例3：在FROM子句中使用不相关子查询

假设我们想要查询每个部门的平均工资。可以使用不相关子查询来实现：

SELECT 
    department_name, 
    AVG(salary) AS avg_salary
FROM 
    (SELECT 
         departments.department_name, 
         employees.salary
     FROM 
         employees
     JOIN 
         departments ON employees.department_id = departments.department_id) AS dept_salaries
GROUP BY 
    department_name;

运行结果：

department_name	avg_salary
HR	50000.00
IT	65000.00
Finance	80000.00

解释：

子查询 (SELECT departments.department_name, employees.salary FROM employees JOIN departments ON employees.department_id = departments.department_id) 返回一个包含部门名称和工资的临时表 dept_salaries。
外部查询 SELECT department_name, AVG(salary) AS avg_salary FROM dept_salaries GROUP BY department_name 对临时表进行分组，计算每个部门的平均工资。

示例4：在HAVING子句中使用不相关子查询

假设我们想要查询平均工资高于 IT 部门平均工资的部门。可以使用不相关子查询来实现：

SELECT 
    department_name, 
    AVG(salary) AS avg_salary
FROM 
    employees
JOIN 
    departments ON employees.department_id = departments.department_id
GROUP BY 
    department_name
HAVING 
    AVG(salary) > (SELECT AVG(salary) FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT'));

运行结果：

department_name	avg_salary
Finance	80000.00

解释：

子查询 (SELECT department_id FROM departments WHERE department_name = 'IT') 返回 IT 部门的 department_id，即 2。
子查询 (SELECT AVG(salary) FROM employees WHERE department_id = 2) 返回 IT 部门的平均工资，即 65000.00。
外部查询 SELECT department_name, AVG(salary) AS avg_salary FROM employees JOIN departments ON employees.department_id = departments.department_id GROUP BY department_name HAVING AVG(salary) > 65000.00 返回平均工资高于 65000.00 的部门。