Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ghostrider
Starting Member
11 Posts |
Posted - 2013-03-01 : 13:44:00
|
I came across a certain query which was as follows:Find employees having salary greater than avg salary of their departmentThe solution was :[CODE]select * from student e where salary > (select avg(salary) from student f where f.dep=e.dep);[/CODE]But when I did it as[CODE] select * from student where salary > (select avg(salary) from student e where dep=e.dep);[/CODE]It showed me a different result can someone please explain the difference? |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-01 : 13:54:11
|
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.SELECT *FROM studentWHERE salary > ( SELECT AVG(salary) FROM student e WHERE dep = e.dep -- This is equivalent to e.dep = e.dep, which is always TRUE ); |
|
|
ghostrider
Starting Member
11 Posts |
Posted - 2013-03-02 : 00:41:15
|
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
Thankyou fro your answer it helped me but i had a doubt that what exactly does "resolving column name from inside out mean??" can you please give an example and why does it doesn't resolve the same way when the alias is in outer query i mean why deosnt it resolve it as dep=dep?? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-02 : 02:27:18
|
that means it first looks for column name in current query context unless you specify an alias. so when it sees dep it will check whether current queryie SELECT AVG(salary) FROM student e WHERE dep = e.dephas a dep column and find that student has one so it assumes its dep from student tableRHS you've e.dep which will explicitly again point to same student table and hence it will be in effect same column compared to itself which is always true.But if you make it like thisSELECT *FROM student sWHERE salary > ( SELECT AVG(salary) FROM student e WHERE s.dep = e.dep ); it will clearly understand that you're referring to dep column in outer query's student table due to use of alias s------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|