Newbie question: Problem with results, sql, join, where, "<" operator

Question

TASK:

Show the last names of employees, employees salary, and their managers last names and salary. We are only interested in those employees who were hired before their managers and have salary below the average of their departments.

CODE:

select e.last_name, e.salary, e.hire_date, e.department_id,
       m.last_name, m.salary, m.hire_date 
from employees e  
join employees m  on (e.manager_id=m.employee_id) 
 where e.salary <(select avg(e.salary) 
                  from employees e  
                  where e.department_id=e.department_id) 
and e.hire_date < m.hire_date

PROBLEM:

I have a problem with results. Among them I got

• one employee with salary equal to department average (Rajs)
• one employee without department_id (Grant)
• one employee which works in a single person department (Whalen).

However, when I changed < operator between e.salary < (select avg(e.salary)... to opposite > (assuming that this time we are interested in those who have salary above department average), the results are correct.

I cannot understand why it works in this way? I've tried to workaround this problem by adding this line

 and e.salary<>(select avg(e.salary) 
               from employees e 
               where e.department_id=e.department_id)`

but it doesn't work. Can anybody help me understand what is going on or just show direction?

Here is my table:

Answer 1

It's a subtle problem. In your subquery you have aliased employees e which is same as the alias you used in the main query. This means the filter in the subquery e.department_id=e.department_id does not actually do what you think it does: Due to namespace scoping it actually collapses to 1=1. So you don't get the results you're expecting as the subquery is not correlated.

The solution is simple: use a different alias in the subquery, like this:

select e.last_name, e.salary, e.hire_date, e.department_id,
       m.last_name, m.salary, m.hire_date 
from employees e  
join employees m  on (e.manager_id=m.employee_id) 
 where e.salary <(select avg(e2.salary) 
                  from employees e2  
                  where e.department_id=e2.department_id) 
and e.hire_date < m.hire_date
;