mysql select嵌套查询

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

SELECT department_id
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 10);

SELECT *
FROM employees
WHERE (department_id, salary) = (SELECT department_id, MAX(salary) FROM employees);

SELECT *
FROM (SELECT * FROM employees WHERE department_id = 10) AS subquery
JOIN departments ON subquery.department_id = departments.department_id;

-- 原始子查询
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 优化后的连接查询
SELECT e.*
FROM employees e
JOIN (SELECT AVG(salary) AS avg_salary FROM employees) AS avg_emp ON e.salary > avg_emp.avg_salary;