Major Description
Example:
SELECT employee_number, name
FROM employees AS Bob
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = Bob.department);
Basically we can optimize these queries into join queries, like:
SELECT employees.employee_number, employees.name
FROM employees INNER JOIN
(SELECT department, AVG(salary) AS department_average
FROM employees
GROUP BY department) AS temp ON employees.department = temp.department
WHERE employees.salary > temp.department_average;
