CODEWITHSUNDEEP
sql
menteith
menteith

Reputation: 678

Nested select with clause with AS operator

I'm learning nested select and I've encountered a problem with AS operator within the second (i.e. nested select).

Please have a look at the following table (truncated):

+-------------+-----------+---------+------------+--------------+
|    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 |
+-------------+-----------+---------+------------+--------------+

The aim is to show the countries in Europe with a per capita GDP greater than that of United Kingdom's. (Per capita GDP is the gdp/population).

The following query is correct in terms of syntax but it will not give the correct result as it selects gdp instead of gdp/population:

SELECT name
FROM world
WHERE gdp/population >
    (SELECT gdp
     FROM world
     WHERE name = 'United Kingdom')
  AND continent = 'Europe';

One solution to correct this would be using gdp/population instead of gdp in nested select but the resulting query would be incorrect in terms of syntax. Why? I use MariaDB but I'd like the query to be not dependent on DBMS provider.

SELECT name
FROM world
WHERE gdp/population >
    (SELECT gdp AS gdp/population
     FROM world
     WHERE name = 'United Kingdom')
  AND continent = 'Europe';

Upvotes: 0

Views: 66

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15150

AS syntax is

SELECT expression AS ALIAS

So you got it the wrong way round, and the alias you are defining contains an illegal character (/). An alias is not required in this case, so you can simply do:

SELECT name
FROM world
WHERE gdp/population >
    (SELECT  gdp/population
     FROM world
     WHERE name = 'United Kingdom')
  AND continent = 'Europe';

Upvotes: 1

Related Questions