Find all Asian cities whose population is greater than population of every single Nordic country (at-CodePudding

I'm doing a task where I'm supposed to find all Asian cities whose population is greater than the population of every single Nordic country (attribute region). (The diagram is shown on the uploaded picture below). I'm pretty new to SQL and I just wanted to ask, what is the right way to connect tables with foreign keys (in this case country code and code) so that I can approach both tables and put my conditions.

So far, my approach is:

SELECT city.Name
FROM wwd.city
JOIN wwd.country
ON city.CountryCode = country.Code
WHERE country.Continent='Asia' AND city.population > (SELECT city.population
                                                      FROM wwd.city
                                                      WHERE country.Region = 'Nordic'

I'm not sure If I used command JOIN properly as well as is the command JOIN expected in the second SELECT part?

Image

CodePudding user response：

Try the following change:

SELECT
    city.Name
FROM
    wwd.city
    JOIN wwd.country ON city.CountryCode = country.Code
WHERE
    country.Continent = 'Asia'
    AND city.population > (
        SELECT
            max(city.population)
        FROM
            wwd.city
        WHERE
            country.Region = 'Nordic'
    )

Generally we want to compare in a 1:1 sort of way rather than directly comparing against a range.

CodePudding user response：

An alternative to the given answer is to use the ALL operator: https://www.w3schools.com/sql/sql_any_all.asp

SELECT
    city.Name
FROM
    wwd.city
    JOIN wwd.country ON city.CountryCode = country.Code
WHERE
    country.Continent = 'Asia'
    AND city.population > ALL (
        SELECT
            city.population
        FROM
            wwd.city
        WHERE
            country.Region = 'Nordic'
    )