Getting ANY of the data

Question

Post reply

Getting ANY of the data

Steve Jones - SSC Editor

SSC Guru

Points: 736478
More actions
December 9, 2024 at 12:00 am

#4475611

Comments posted to this topic are about the item Getting ANY of the data

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

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

below86 SSChampion Points: 11538 More actions · Answer 1

Why would you want to code this as an ANY? Why not code it with the IN?

WHERE bc.BeerName IN (SELECT b2.BeerName FROM dbo.Beer AS b2);

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

I'll admit I was struggling a bit to wrap a question around ANY. Suggestions welcome.

below86 SSChampion Points: 11538 More actions · Answer 3

I never knew ANY Or SOME were an option, and after reading the description I don't think I would ever code it with these. I would just use the IN. Not sure why we need more than one way to do the same thing.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Ed B Hall of Fame Points: 3669 More actions · Answer 4

I think that ANY, SOME and ALL have the advantage that you can use comparisons like less than or greater than. Imagine the Beer table has cost and a country columns, maybe this would show the beers that are more expensive than the average beer costs of any/all countries. I'm sure there's a useful example somewhere but this is the best I could come up with.

SELECT  a.*
JOIN    dbo.Beer AS a
WHERE   a.Cost > ANY
--WHERE   a.Cost > ALL
    (   SELECT AVG(Cost) 
        FROM dbo.Beer
        GROUP BY Country)