SQLzoo - ALL (allow)

Question

Hey guys. I've been doing this SQLzoo/wiki tutorial (which is great by the way), but I've stumbled upon the ALL (allow) command in SELECT within SELECT in exercise 5 and I just can't wrap my head around it. I failed to find satisfactory explanations on web. Does anyone comprehend this topic and would be able to tell me step by step how does one read it? I could not get it from any posts nor videos on YT.
If anyone would like to take a peek - here's the link https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial

Answer 1

yes, ALL operator will take them all and compare it to population.
so lets say we have 3 country for simplicity

A: 20
B: 50
C: 40

so imagine the subquery will take them all as a list [20,50,40] done we have the list, subquery stop here.

the >= operator will compare the current population with every single data on this list we are having which is 20 50 and 40.

now the outer query are executing and the database run tru the rows.

first. it'll check is population of A (20) is it more than or equal 20 50 or 40? no. 50 and 40 are bigger
2nd row. is the population of B (50) is more than or equal 20 50 or 40? yes! its more than 20 and 40 but also equal to 50. this will be part of the result.
3rd row. is population of C(40) is more than or equal 20 50 or 40,?no. 50 is bigger, not part of the result.

so the only result is B which is the largest

Answer 2

the most simple way i can describe is the nested SELECT will run first, then the result will be taken to be use by the outer SELECT

so the example are
SELECT name FROM world
WHERE population >
(SELECT population FROM world
WHERE name='Andorra')

SELECT population FROM world WHERE name='Andorra' will run first and become a value of its result. the result are 78115 so the outer SELECT will use it replacing the nested select

SELECT name FROM world
WHERE population > 78115

then the outer select run

Answer 3

Hey! Thank you for getting back at me.
Alright, but let's take a look at the code:

SELECT name
FROM world
WHERE population >= ALL (SELECT population
FROM world
WHERE population > 0)

This code supposedly returns the largest population in the world (according to the table on the provided site)

What does this ALL actually do? I read this subquery as listing every country in the world (bevause of the population > 0), but how does it lead to retrieving the population of China?

Answer 4

I'm really glad I asked. Now it is all more clear to me :)
I can now take further lessons step by step. Thanks Taste!