a tricky query - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

a tricky query

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 department
The 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   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
       );

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 query

ie
SELECT AVG(salary)
FROM student e
WHERE dep = e.dep
has a dep column and find that student has one so it assumes its dep from student table

RHS 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 this


SELECT *
FROM   student s
WHERE  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 MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources