select * from student e where salary > (select avg(salary) from student f where f.dep=e.dep);

select * from student  where salary > (select avg(salary) from student e where dep=e.dep);

SELECT *
FROM   student
WHERE  salary > (
           SELECT AVG(salary)
           FROM   student e
           WHERE  dep = e.dep -- This is equivalent to e.dep = e.dep, which is always TRUE
       );

quote:

---

Originally posted by James K

The second query is incorrect. This is because of the rules of name resolution. Just like in C# or any other language, when T-SQL sees a column name, it tries to resolve it from inside out. So in your second statement you are comparing dep column from the inner query to itself.[c

---

SELECT *
FROM   student s
WHERE  salary > (
           SELECT AVG(salary)
           FROM   student e
           WHERE  s.dep = e.dep 
       );