Correlated subqueries II

My question is why does dropping the ‘f’ and ‘flights’ (such as and from the query result in the ‘flight_sequence_number’ being 1 for every row. But if you add those back each row has a distinct ‘flight_sequence_number’.

SELECT carrier, id, 
FROM flights as f
AND f.carrier=flights.carrier) + 1
 AS flight_sequence_number
FROM flights;

Hi Jon,
What lesson is this from? is there a link?

+1 generally means you’re trying to add 1 to a field.


If you remove the f and flights from <, the comparison will become WHERE id < id, which will always return False. Thus, the COUNT will be be 0 for each row. Since you add the + 1 outside the parentheses, the value of of each row in flight_sequence_number will always be the count (which is 0) + 1.

Hope that helps clear things up!