Difference between ALL and ANY

[manohars]

What's the difference between ALL and ANY?

Thanks in Advance,
Manohar

 

[yksvaan]

Reading bol I think I understand

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp

But can somebody explain, why this then returns rows, I think it should not:

USE pubs
SELECT t1.type, MAX(t1.advance) AS MADMAX
FROM titles t1
WHERE t1.type='business'
GROUP BY t1.type
HAVING MAX(t1.advance) >= ALL
(SELECT t2.advance
FROM titles t2
WHERE t1.type = t2.type

since MAX(t1.advance) / t2.advance is not true for all pairs

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[dbomrrsm]

As I see it what ALL is doing is comparing a value with a single column list - so you are comparing the max advance for business = 10125 with a list of all advances for business = 5000,5000,5000,10125.

So you are selecting the max from first query and returning it where it is greater than or equal to the list.

[blue]DBomrrsm[/blue]

 

[donutman]

No, he's restricting the subquery to business=10125 also. I'm surprised, however, that it is permissible to perform a correlated subquery in the Having clause since it has to be evaluated after the grouping is completed.
Manohars, why not try running it with t1.type replaced with 10125 in the subquery. BTW, there's no point to the Group By clause as written, but I consider this an academic question anyway.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[manohars]

Can do Karl.

Ultimately, we are yet to find the difference between ALL and ANY?

Thanks,
Manohar

 

[dbomrrsm]

Would it not be that the figure you are comparing can be selected if it is equals to greater than etc ANY in a list as against it having to be equals to greater than etc to ALL in the list.

Donutman - I did say that he was:

returning it where it is greater than or equal to the list.

not just greater than.

[blue]DBomrrsm[/blue]

 

[donutman]

I was refering to this:
"for business = 5000,5000,5000,10125."
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[dbomrrsm]

sorry donutman dont understand what you mean - the above query is looking to see if the max from first select is >= ALL of the LIST produced by the second select.

[blue]DBomrrsm[/blue]

 

[yksvaan]

My example was modified from bol: Select examples, section E
"A correlated subquery can also be used in the HAVING clause of an outer query"

Cheers

 

[vongrunt]

Answer to original post:

For both ALL and ANY syntax is the following:

<scalar_exp> <comparator> [ALL|ANY] (subquery)

So:

select case when 3 = ANY
(	select 1 union 
	select 2 union
	select 3
)
then 'Yep' else 'Nope' end

ANY returns true if at least one (any) element matches condition. If comparator is =, ANY is equivalent to IN().
ALL returns true if all values from subquery match condition.

 

[manohars]

Vongrunt! This works fine. Returns Yep for ANY and Nope for ALL. Is this the answer for that question. Does it mean that it would work only for = comparison operator?

Thanks,
Manohar

 

[vongrunt]

Query I posted only explains the logic behind ANY|ALL... there were no questions.

All comparison operators ( <=, >=, =, <> etc ) are valid. Try 'em.