Correlated Subqueries I - carrier = f.carrier .. again


#1

<PLEASE USE THIS TEMPLATE TO HELP YOU CREATE A GREAT POST!>

<Below this line, add a link to the EXACT exercise that you are stuck at.>
Hello! It seems like a lot of people before me I am having issues by the signification carrier = f.carrier from Correlated Subqueries I introduction, here:
https://www.codecademy.com/courses/sql-table-transformation/lessons/subqueries/exercises/correlated-i?action=lesson_resume

I read other closed posts on it and I understood from them that:

  • carrier = f.carrier purpose is to enable to compare something else than only: “AVG(distance) of all rows of the flight table” - Versus - “distance of all rows of the flight table” (if not this weird piece of code would not be there)
  • carrier = f.carrier is comparing 2 (different??) flights tables by defining alias.

I however do not understand how these 2 flight tables are different…? in the codes i ran below i indeed see that the results are different.

I included below 4 pieces of code to help me try to understand (no success as you can see ^^):
-1/ the average flight distance as reference
-2/ the given code: the code of the introduction with addition in SELECT of the distance and carrier
-3/ a code from another post, giving explanation to the 2 flight table alias. Results are identical to code 2, good.
-4/ the code without the problematic piece: WHERE carrier = f.carrier

Results between the 2 codes (2/ and 4/) are indeed different and some output id of code 2 have a smaller distance than the AVG(distance).

thanks for any precision!

```

SELECT AVG(distance) FROM flights;

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

SELECT id, distance, carrier
FROM flights AS f1
WHERE distance > (
SELECT AVG(distance)
FROM flights AS f2
WHERE f2.carrier = f1.carrier) LIMIT 5;

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

<do not remove the three backticks above>

#2

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