Why do I neeed WHERE carrier = f.carrier


#1


https://www.codecademy.com/courses/sql-table-transformation/lessons/subqueries/exercises/correlated-i?action=lesson_resume


I can't understand what WHERE carrier = f.carrier does in this code.
How is 'carrier' and ' f.carrier' different?


 SELECT id, carrier, distance
FROM flights AS f
WHERE distance > (
 SELECT AVG(distance)
 FROM flights;


#2

That is how you join the subquery to the main query to match the carrier to the carrier.


#3

I didnt understand it really.


#4

I just got it myself, but the reason is that you don’t want to compare the flight distance of any flight to the overall average, but only to the average of a certain carrier. ( …you could also compare it to the average of flights from a certain company only, or those who start at a certain city maybe)

There are many averages this way, like MA = 167, or UAH = 731.33 … .

Now you want to only list the ids of those flights who are below the average for their certain carrier specification. (so, only “list those MA who are under 167”, then “those UAH who are under 731.33”)
That way the average changes on every row of the outer query, because the name of the carrier of every row changes, making it necessary to calculate the subquery again. The carrier = f.carrier then just ensures that you get the average of the carrier of the row you are currently in with the outer query.

.
Add:
Maybe a precalculated list of averages would be more merciful with the resources if it could be created somehow, so you don’t have to do that potentially lenghty query again and again when you already finished calculating some of the same results before. …But this is how it works for here and now.


#5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.