CODEWITHSUNDEEP
sqlpostgresql
Nona
Nona

Reputation: 5462

A subquery with the ALL operator

I'm trying to write a SQL (Postgres engine) query that answers the following question:

Which countries have a GDP greater than every country in Asia? [Give the name only.] (Some countries may have NULL gdp values)

Below is an abbreviated SQL table containing sample data.

+-------------+-----------+---------+------------+--------------+
|    name     | continent |  area   | population |     gdp      |
+-------------+-----------+---------+------------+--------------+
| Afghanistan | Asia      |  652230 |   25500100 |  20343000000 |
| Albania     | Europe    |   28748 |    2831741 |  12960000000 |
| Algeria     | Africa    | 2381741 |   37100000 | 188681000000 |
| Andorra     | Europe    |     468 |      78115 |   3712000000 |
| Angola      | Africa    | 1246700 |   20609294 | 100990000000 |
+-------------+-----------+---------+------------+--------------+

I wrote something like the following, which returns nothing (although I know this isn't the answer due to the online interactive guide I'm using):

SELECT name
FROM  world
WHERE gdp > ALL (SELECT gdp from world WHERE continent = 'Asia')
AND   continent<>'Asia'

What query should I use? I'm pretty new to SQL.

Upvotes: 2

Views: 178

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657727

Strictly speaking, if there is a single Asian country in your table with an unknown (NULL) GDP, then the correct answer must be:

"We do not know."

Accordingly, this query returns no row:

SELECT name
FROM   world
WHERE  gdp > ALL (SELECT gdp from world WHERE continent = 'Asia')
AND    continent <> 'Asia';

A NULL value in the set of Asian GDP's makes it impossible for the first WHERE expression to be TRUE, so the query can never return rows - which is the correct answer. There is no country that we know to fit the requirement. The query you had is correct.

The variant you squeezed into a comment comparing to max(gdp) answers a slightly different question:

Which countries have a GDP greater than the greatest known GDP in Asia?

Upvotes: 2

Bagus Trihatmaja
Bagus Trihatmaja

Reputation: 865

Based on @Tony 's answer, it should be like this:

  select w1.name 
  from world w1 
  where w1.gdp > (
    select max(coalesce(w2.gdp,0)) 
    from world w2 
    where w2.continent='Asia' ) 
  and w2.gdp is not null;

Why use max instead of sum? Because the question asked:

Which countries have a GDP greater than every country in Asia?

So finding the largest gdp in Asia will work and you don't have to compare with all of the countries in Asia.

I am sorry I can't comment on @Tony 's answer since my reputation is under 50.

Upvotes: 0

Tony
Tony

Reputation: 2754

It sounds like you want the sum of all Asian countries. If so, try something like this:

select w1.name 
from world w1 
where w1.gdp > (
   select sum(coalesce(w2.gdp,0)) 
   from world w2 
   where w2.continent='Asia' )

If not, and you want just the single Asian country with the highest GDP, replace sum with max. You don't need the additional continent <> 'Asia' because no Asian country can have a higher GDP than the returned value.

Upvotes: 0

Related Questions