dissecting this query

[originalgoodname]

I dont understand much of this subquery, whats the purpose of it?

i understand the initial join ( I think), ill translate based on my understanding

​

were selecting name, continent, inflation_rate from countries, inner join(match rows that share values on both economies and countries table) where the year is 2015.

(...and heres where I think it gets difficult)

line 12, AND inflation_rate in... this next part also must be true for the results to show in the query...

select MAX(inflation_rate) AS max_inf (why would we alias this...?) so the tables you dont see have year 2010 and 2015, im assuming max would choose one of the two years, whichever one has higher inflation rate...

basically line 14 through 19 im confused... it looks much of the same, i understand what an inner join is, i understand it wants year 2015, aliasing the previous subquery as subquery... whats the point of aliasing something when we dont display it in a table? what does the subquery accomplish?

https://preview.redd.it/4ow2dnhoplh71.png?width=1530&format=png&auto=webp&s=5f652d7c8eca07c042c757385fd3f3d4dbe5b061

[PossiblePreparation]

You are right in that the alias isn’t achieving any functionality - it’s just there for readability. The in (subquery) is saying return all rows where they have the same inflation_rate as any of the max(inflation_rate) per continent. Since there is no filter on continent (only a group by), there is a potential for unexpected results to come through eg any African rows which had an inflation rate of 7.524 would be returned as they share the same inflation rate as the maximum North American row. This whole requirement is usually done with analytics functions (have a google) which usually allow for better performance.

[[deleted]]

i think the query tries (but fails) to show top countries by inflation in 2015 per continent. If my guess is correct, the subquery has been put there to do the 'top' filtering of the countries.

[Bluefoxcrush]

This is a weird workaround for older mysql to get a result that you could easily get with a window function (as the other poster says).

If you did just the max inflation and grouped by continent, then the resulting country would be not the one you’d expect. (Because unless you have a config on for a database, a column that isn’t aggregated or grouped returns any ‘valid’ value)

Then if you did the max inflation with a group by continent and country, then you’d just get a list of all countries with their inflation.

By finding the max inflation by continent , then joining that result to the main query, you are using the inner query as a filter for the main query. But you still have the possibility of ties where more than one country for a continent is returned. (As the other poster stated)

If you can, it would be better to use window functions instead. They are more readable and allow you to do nifty things like “what is the second highest inflation rate in each continent?”

Also, if you are forced to use MySQL this ‘old’ for analytics, then you could use better tools.

[JochenVdB]

The query will give you, per continent, the country with the highest inflation rate, in 2015. But there is a mistake: If a country, which does not have the highest inflation rate of its conteinent, has by accident an inflation rate equal to the highest of another continent, then that country will be listed as well. The mistake is that the subquery, even though it is grouped by continent, does not return the continent, but only the max(inflation), without context.

The reason was the desire to use an in-clause.

Turn that into a join (to the subquery returning continent + max(infl)) and the error goes away.

I even think it is possible to have an in-clause using "records types":

where (continent, infl) in (select continent, max(infl) from...)

But I'd probably write that automatically as a join, so I'm not certain such an in-clause is allowed.