Correlated query

Question

Correlated query

b4u_all

SSC Veteran

Points: 204
More actions
June 24, 2013 at 1:48 am

#274474

hi this topic is related with correlated query
my query is related with "Write a select command using a corelated subquery that selects the names and numbers of all customers with ratings equal to the maximum for their city."
for this i tried
SELECT abc.`CNAME`,abc.`RATING`FROM customers AS abc
WHERE abc.`RATING`= (SELECT MAX(RATING) FROM customers WHERE ccity=abc.`CCITY` )
;
which is working fine
but later i tried
SELECT cname,rating FROM customers
WHERE rating= (SELECT MAX(RATING) FROM customers AS abc WHERE abc.`CCITY`=ccity )
;
which gives me different result
my question is what is difference between these two queries ? how they are executed exactly ? why they are showing different results

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

In the first query, with the abc., you're explicitly referencing the columns from the outer query, in the second without the alias, you're implicitly referencing the columns in the outer query, however the correlation condition in the second is comparing a column of the subquery (explicitly qualified with the abc.) with the exact same column of the subquery, hence it not working correctly.

It is good practice to always qualify all column names, especially when dealing with correlated subqueries

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dird SSCertifiable Points: 5081 More actions · Answer 2

SELECT cname,rating FROM customers

WHERE rating= (SELECT MAX(RATING) FROM customers AS abc WHERE abc.`CCITY`=ccity )

The default reference will be the local (sub)query. This means that your inner query (with the MAX) does not reference the outer table at all. e.g. if you had the data:

select 'bob' cname,4 rating ,'brum' ccity

union all select 'dave',7,'brum'

union all select 'sarah',14,'walsall'

Your inner query is equivalent to: SELECT MAX(RATING) FROM customers where 1=1;

If you do the first query you get 2 rows (dave from brum and sarah from walsall). If you do the 2nd query you only get sarah because the query isn't on a per city basis but WHERE 1=1.

This means it will only find the max of all cities; not per city. You have to name the outer table and include that in the subquery if you want to return the highest rating per city. Your first query was correct.

Test examples:

with t1 as (select 'bob' cname,4 rating ,'brum' ccity

union all select 'dave',7,'brum' union all select 'sarah',14,'walsall')

SELECT abc.CNAME,abc.RATING FROM t1 AS abc

WHERE abc.RATING= (SELECT MAX(RATING) FROM t1 WHERE ccity=abc.CCITY )

GO

with t1 as (select 'bob' cname,4 rating ,'brum' ccity

union all select 'dave',7,'brum' union all select 'sarah',14,'walsall')

SELECT cname,rating FROM t1

WHERE rating= (SELECT MAX(RATING) FROM t1 AS abc WHERE abc.CCITY=ccity )

Dird

b4u_all SSC Veteran Points: 204 More actions · Answer 3

Very much thanks to GilaMonster and Dird

now am clear about both

thanks