Search code examples
mysqlsqlcorrelated-subquery

SQL ZOO List each continent and the name of the country that comes first alphabetically

Im confused why it could not be 

Select x.continent, x.name
From world x
Where x.name <= ALL (select y.name from world y where x.name=y.name)
ORDER BY name

Can anyone please explain to me why it has to be x.continent=y.continent and not x.name=y.name ?

Table

Solution

  • when you use x.name=y.name you are comparing country name from x with the country name from y if both instances have the same country name. That basically would just return you the full table x.

    You want to use x.continent=y.continent because you only want to compare the country name of an instance from x with the country name of an instance from y if they share the same continent.

    Let me illustrate this step by step with an example: Here we have a table world and I populated with some data:

    world: 

Select  x.continent, x.name
From world x
ORDER BY  name

continent       name
Asia            Afghanistan
Europe          Albania
Africa          Algeria  
Europe          Andorra
Africa          Angola
SouthAmerica    Bolivia
SouthAmerica    Brazil
Europe          Hungary
Asia            Japan
Africa          Nigeria
SouthAmerica    Peru
Asia            Taiwan

    when you execute this query without the WHERE clause in your subquery:

    Select  x.continent, x.name
From world x
Where x.name <= ALL (select y.name from world y)
ORDER BY name

    you get this

    continent   name
Asia        Afghanistan

    This is because the where clause filtered out all but one country 

    where x.name <= (Afghanistan,Taiwan,Japan,
                 Albania,Hungary,Algeria,Nigeria,Andorra,
                 Angola,Bolivia,Peru,Brazil)

    and namely, the country name who comes first alphabetically which is Afghanistan.

    but since we want to get the first country in each continent we will add x.continent=y.continent to our subquery

    Select  x.continent, x.name
From world x
Where x.name <= ALL (select y.name from world y where x.continent=y.continent)
ORDER BY name

    what is happening underneath is that now we are only comparing the country name of an instance from x with the country name of an instance from y if they share the same continent. So use continent of Asia for example:

    Japen gets filtered out because Japan <= All(Afghanistan,Taiwan,Japan) is false since Japan is not less or equal to Afghanistan (A comes before J)

    Taiwan gets filtered out because Taiwan <= All(Afghanistan,Taiwan,Japan) is false since Taiwan is not less or equal to Afghanistan.

    Afghanistan does not get filtered out because Afghanistan <= All(Afghanistan,Taiwan,Japan) is true since Afghanistan is equal to Afghanistan

    However, if you use x.name=y.name in your subquery then you are essentially comparing each country to itself and they will all get included in your final result set because all country name equals to the country name of itself.

    I hope this help and welcome to Stack Overflow. If this answer or any other one solved your issue, please mark it as accepted."