mehta.saurabhj (3/1/2013)

---

I came across a certain query which was as follows:
Find employees having salary greater than avg salary of their department
The solution was :

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

But when I did it as

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

It showed me a different result can someone please explain the difference?

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 e.dep=e.dep); -- The subquery is comapring the value of dep to itself

mehta.saurabhj (3/1/2013)

---

@lynn thank you for your answer it helped me but i have a doubt which is that why does not this happen in case when the alias is outside the inner query i mean why doesn't it resolve the inner query as dep=dep or e.dep=e.dep??

mehta.saurabhj (3/1/2013)

---

I came across a certain query which was as follows:
Find employees having salary greater than avg salary of their department
The solution was :

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

But when I did it as

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

It showed me a different result can someone please explain the difference?

If, in the second query, you had dropped the alias from the table in the inner query you would have gotten the same answer as in the first query

mehta.saurabhj (3/2/2013)

---

Then how will the sql server decide which dep is of which query in this case?since both are dep's!