March 1, 2013 at 11:43 am
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?
March 1, 2013 at 11:47 am
You've moved the alias from the source query to the subquery so it's only looking at itself and not the encapsulating dataset.
March 1, 2013 at 11:55 am
Thank you for your reply but can you please explain by giving a more elleborate explaination It would be very helpful!!
March 1, 2013 at 12:00 pm
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?
This:
select *
from student
where salary > (select avg(salary) from student e where dep=e.dep);
Is the same as this:
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
March 1, 2013 at 1:45 pm
March 2, 2013 at 12:07 am
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??
Going back to this:
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?
The first query is a correlated subquery. Using the table aliases e and f you are explicitly telling SQL Server to compare the values dep from the outer query with the values of dep in the inner query.
The second query, by dropping the alias from the outer query, SQL Server is unable to tell which query you are referring to in the inner query so it joins to itself in the inner query. It really has no idea that the unaliased column dep is supposed to be from the outer query.
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. The take away here is to be sure to use table aliases when writing correlated subqueries.
March 2, 2013 at 12:13 am
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
Then how will the sql server decide which dep is of which query in this case?since both are dep's!
March 2, 2013 at 12:33 am
mehta.saurabhj (3/2/2013)
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
Then how will the sql server decide which dep is of which query in this case?since both are dep's!
I think I didn't fully explain that. Had you dropped the alias from the inner table and kept the alias from the outer table you would have gotten the same result as the original query.
If you drop the aliases off both tables, SQL won't know which tables the dep columns come from and you would get the same result you got from your original 2nd query.
March 2, 2013 at 12:44 am
Thank you for your explaination it cleared all the doubts!!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply