什么是mysql子查询

-- employees 表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary INT,
    department_id INT
);

-- departments 表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 插入示例数据
INSERT INTO departments (id, name) VALUES (1, 'HR'), (2, 'Engineering');
INSERT INTO employees (id, name, salary, department_id) VALUES 
    (1, 'Alice', 50000, 1),
    (2, 'Bob', 60000, 2),
    (3, 'Charlie', 55000, 1);

SELECT name 
FROM employees 
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department_id = 1
);

SELECT d.name AS department_name, e.name AS employee_name, e.salary 
FROM employees e 
JOIN departments d ON e.department_id = d.id 
WHERE e.salary = (
    SELECT MAX(salary) 
    FROM employees 
    WHERE department_id = e.department_id
);