Possible error in "5. Correlated Subqueries I"?


#1

Hi!

In the "Learn" section there is a piece of code to find all the flights with distance greater than average distance:

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

I modified it slightly by adding SELECT distance, ORDER BY id and LIMIT 10 so the result is more demonstrable:

SELECT id, distance
FROM flights AS f
WHERE distance > (
SELECT AVG(distance)
FROM flights
WHERE carrier = f.carrier)
ORDER BY id
LIMIT 10;

Now, in the result there are flights 402, 420, 610 etc with distance 558, 534 and 508 respectively. BUT if we check the actual average distance

SELECT AVG(distance) FROM flights;

The result for average distance is 759

So, I modified subqery by removing WHERE carrier = f.carrier

SELECT id, distance
FROM flights AS f
WHERE distance > (
SELECT AVG(distance)
FROM flights)
ORDER BY id
LIMIT 10;

And now result contains only flights with distances that are actually greater than 759.

So,
1) is that an error?
2) what is the assumed purpose of WHERE carrier = f.carrier? It seems to be superfluous and, in addition, it leads to erratic result.

Thanks!


#2

Average of all vs average for that carrier


#3