SQL query optimization - avoiding duplicate JOIN operations when finding maximum value

Data and Databases
1

I’m working with two database tables: employees and departments. Each employee record contains a dept_id that links to a department in the departments table.

My basic JOIN query works fine:

SELECT surname, department_name, salary_avg
FROM employees INNER JOIN departments
ON employees.dept_id = departments.dept_id 
WHERE department_name = 'Engineering'

However, I need to find the employee with the highest salary specifically from the Engineering department. My first attempt failed:

SELECT surname, salary_avg FROM (
    SELECT surname, department_name, salary_avg
    FROM employees INNER JOIN departments
    ON employees.dept_id = departments.dept_id 
    WHERE department_name = 'Engineering'
) AS temp_result 
WHERE salary_avg = (SELECT MAX(salary_avg) FROM temp_result);

This gives an error saying temp_result table doesn’t exist.

I found a workaround by duplicating the entire JOIN:

SELECT surname, salary_avg FROM (
    SELECT surname, department_name, salary_avg
    FROM employees INNER JOIN departments
    ON employees.dept_id = departments.dept_id 
    WHERE department_name = 'Engineering'
) AS temp_result 
WHERE salary_avg = (SELECT MAX(salary_avg) FROM (
    SELECT surname, department_name, salary_avg
    FROM employees INNER JOIN departments
    ON employees.dept_id = departments.dept_id 
    WHERE department_name = 'Engineering'
) AS temp_result_two);

This works but seems inefficient. Is there a cleaner way to achieve this without repeating the JOIN operation twice?

2

Just use a window function with RANK() or ROW_NUMBER(). It’s way cleaner - you only JOIN once and grab the highest salary employee directly:

WITH ranked_employees AS (
    SELECT surname, salary_avg,
           RANK() OVER (ORDER BY salary_avg DESC) as salary_rank
    FROM employees INNER JOIN departments
    ON employees.dept_id = departments.dept_id 
    WHERE department_name = 'Engineering'
)
SELECT surname, salary_avg 
FROM ranked_employees 
WHERE salary_rank = 1;

This beats subqueries hands down - easier to read and usually faster with big datasets. RANK() automatically handles ties if multiple employees have the same top salary. If you need just one result when there’s a tie, swap RANK() for ROW_NUMBER().

3

why not just order by salary and use limit? this should work:

SELECT surname, salary_avg
FROM employees INNER JOIN departments
ON employees.dept_id = departments.dept_id 
WHERE department_name = 'Engineering'
ORDER BY salary_avg DESC
LIMIT 1;

way simpler than subqueries. do you have multiple employees with the same max salary tho? that’d change which approach works best.

4

you could also use a correlated subquery - keeps it all in one query without duplicating joins:

SELECT surname, salary_avg
FROM employees e INNER JOIN departments d
ON e.dept_id = d.dept_id 
WHERE department_name = 'engineering' 
AND salary_avg = (
    SELECT MAX(e2.salary_avg)
    FROM employees e2 INNER JOIN departments d2
    ON e2.dept_id = d2.dept_id
    WHERE d2.department_name = 'Engineering'
);

not as clean as CTE but it works