CODEWITHSUNDEEP
sqlsql-server
iCV
iCV

Reputation: 607

SQL: 'ALL' explanation needed

I'm trying to wrap my head around 'All' when used with an operator.

What I'm currently understanding is that the inner query returns a subset, on which the outer query uses ALL, how the outer query can tell that each

SELECT playerno, leagueno
FROM Players
WHERE playerno >= ALL(select playerno FROM Players WHERE leagueno IS NOT NULL)

I'm trying to figure out how this works. The inner query returns for example (1,2,3), then how does it know playerno should be bigger or equal to 3 and not just 2? I'm assuming ALL means that each value gets checked, which would also mean that playerno being bigger than 1 or 2 would be enough to satisfy the condition and therefore could be used in the WHERE-clausule, even if this is not what we want since we only want the biggest number.

Upvotes: 1

Views: 48

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175874

Query:

SELECT playerno, leagueno
FROM Players
WHERE playerno >= ALL(select playerno FROM Players WHERE leagueno IS NOT NULL)

is equivalent of:

SELECT playerno, leagueno
FROM Players
WHERE playerno >= (SELECT MAX(playerno) FROM Players WHERE leagueno IS NOT NULL)
-- assumption: playerno is defined as NOT NULL

It means that playerno has to ge greater or equal for each Playerno where league is defined.

Upvotes: 2

Related Questions