Inner and outer select

In my SQL learning I have reached to the following question:

Find the largest country in each region
The table bbc has (name, region, population)

The answer is

SELECT region, name, population FROM bbc x
WHERE population >= ALL
(SELECT population FROM bbc y
WHERE y.region=x.region
AND population>0)

I couldn't understand it and need an explanation guys

Is it for sql server?

Yes it is for SQL server

The ALL keyword does a set comparison on the values of one column. Essentially, it validates and identifies the row with the largest value in that column.

Regarding your query, I assume that the name column is an appellation for country. If that is true, your query is equivalent to the following query:

SELECT region, name, max(population)
FROM bbc
WHERE population > 0
GROUP BY region, name

The purpose of the MAX and ALL keywords are very similar. Most all populations you can create using ALL , you can create with using MAX. I suppose a key difference is that MAX will return <=1 row per group, while ALL will/can return 0 or >=1 rows per group (for example, suppose one region had two countries with the same population.)

I find use for the SOME/ANY keyword. I do not use ALL.

I always appreciate additional insight.