bbc example

bbc_q3a.sql

create table bbc (
    region varchar(50) not null,
    name varchar(50) not null primary key,
    population integer
);
 
insert into bbc values ('Oceania', 'Australia', 20);
insert into bbc values ('Oceania', 'New Zealand', 4);
insert into bbc values ('Oceania', 'Solomon Islands', NULL);
insert into bbc values ('Asia', 'Japan', 50);
insert into bbc values ('Asia', 'China', 3000);
 
-- The query in question is from q 3a of http://sqlzoo.net/1a.htm
-- It is a correlated subquery to get the largest country from each 
-- region. The original author has used >0 to remove nulls from the
-- result set.
 
 
select region, name, population
from bbc x
where population >= all(
    select population
    from bbc y
    where y.region = x.region
    and population > 0   -- what is this line for?
);
 
select region, name, population
from bbc x
where population >= all(
    select population
    from bbc y
    where y.region = x.region
    --and population > 0   -- removing the line produces the wrong result
);
 
select region, name, population
from bbc x
where population >= all(
    select population
    from bbc y
    where y.region = x.region
    and population is not null -- rewriting the line gives an identical meaning 
                               -- while improving readability
);