-
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
-
-
-
Max
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|