SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 a tricky query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ghostrider
Starting Member

India
11 Posts

Posted - 03/01/2013 :  13:44:00  Show Profile  Reply with Quote
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?

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 03/01/2013 :  13:54:11  Show Profile  Reply with Quote
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
       );
Go to Top of Page

ghostrider
Starting Member

India
11 Posts

Posted - 03/02/2013 :  00:41:15  Show Profile  Reply with Quote
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??

Edited by - ghostrider on 03/02/2013 00:58:36
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/02/2013 :  02:27:18  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000