Why this select selects no rows

Question

I am trying to select a highest earner in 'IT' department, but I keep selecting no rows. Salary and names are in table called employees while department_name is in a table named departments. Can anyone please explain why does this select no rows and how should I do it?

SELECT first_name, last_name, salary, department_name
FROM employees
JOIN departments on departments.department_id = employees.department_id
WHERE salary = (SELECT max(salary) FROM employees WHERE department_name = 'IT'); 

Answer 1

Why this select selects no rows?

Your query fails because there is no column department_name in the employees table. So your subquery does not do what you expect:

where salary = (SELECT max(salary) FROM employees WHERE department_name = 'IT'); 

If you want to do this with a subquery, you need to correlate it:

select e.first_name, e.last_name, e.salary, d.department_name
from employees e
inner join departments d on d.department_id = e.department_id
where 
    d.department_name = 'IT'
    and e.salary = (select max(e1.salary) 
                    from employees e1 
                    where e1.department_id = e.department_id);