Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 a tricky query Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, March 01, 2013 11:43 AM
 Grasshopper Group: General Forum Members Last Login: Monday, March 25, 2013 9:15 AM Points: 22, Visits: 33
 I came across a certain query which was as follows:Find employees having salary greater than avg salary of their departmentThe 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?
Post #1425668
 Posted Friday, March 01, 2013 11:47 AM
 SSC-Addicted Group: General Forum Members Last Login: Yesterday @ 10:46 AM Points: 421, Visits: 777
 You've moved the alias from the source query to the subquery so it's only looking at itself and not the encapsulating dataset.
Post #1425671
 Posted Friday, March 01, 2013 11:55 AM
 Grasshopper Group: General Forum Members Last Login: Monday, March 25, 2013 9:15 AM Points: 22, Visits: 33
Post #1425680
 Posted Friday, March 01, 2013 12:00 PM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 11:17 PM Points: 21,617, Visits: 27,450
 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 departmentThe 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 studentwhere salary > (select avg(salary) from student e where dep=e.dep);`Is the same as this:`select *from studentwhere salary > (select avg(salary) from student e where e.dep=e.dep); -- The subquery is comapring the value of dep to itself`
Post #1425683
 Posted Friday, March 01, 2013 1:45 PM
 Old Hand Group: General Forum Members Last Login: Saturday, April 27, 2013 9:30 PM Points: 392, Visits: 384
 Well explained Lynn..... That is what exactly happening in these two queries... ===========================================Better try and fail than not to try at all...Database Best PracticesSQL Server Best Practices
Post #1425719
 Posted Friday, March 01, 2013 10:23 PM
 Grasshopper Group: General Forum Members Last Login: Monday, March 25, 2013 9:15 AM Points: 22, Visits: 33
 @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??
Post #1425817
 Posted Saturday, March 02, 2013 12:07 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 11:17 PM Points: 21,617, Visits: 27,450
 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 departmentThe solution was :`select *from student ewhere salary > (select avg(salary) from student f where f.dep = e.dep);`But when I did it as`select *from studentwhere 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.
Post #1425826
 Posted Saturday, March 02, 2013 12:13 AM
 Grasshopper Group: General Forum Members Last Login: Monday, March 25, 2013 9:15 AM Points: 22, Visits: 33
 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!
Post #1425827
 Posted Saturday, March 02, 2013 12:33 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 11:17 PM Points: 21,617, Visits: 27,450
 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.
Post #1425828
 Posted Saturday, March 02, 2013 12:44 AM
 Grasshopper Group: General Forum Members Last Login: Monday, March 25, 2013 9:15 AM Points: 22, Visits: 33
 Thank you for your explaination it cleared all the doubts!!!
Post #1425832

 Permissions