Lesson 1.6: what is the "FROM flights f" syntax?

#1

Here's the sample code, which "assuming flight_id increments with each additional flight, we could use the following query to start to view a sample of carriers, flight ids, and sequence numbers":

SELECT carrier, id,
(SELECT COUNT(*)
FROM flights f
WHERE f.id < flights.id
AND f.carrier=flights.carrier) + 1
AS flight_sequence_number
FROM flights;

What is the "f" referencing? There are no tables or columns in the section with that name. Is it referencing the data in the row that's being passed to the subquery? If so, how? Does that FROM "know" it's part of a subquery and consequently allow a second argument?

Don't be afraid to talk about pointers and objects stuff ,if that would help. I have plenty of experience, just not with anything like SQL. Thanks in advance!

#2

@sarkreth,

You could write the SELECT like
`
SELECT carrier, id,
(SELECT COUNT(*)
FROM flights AS f <<===you can now refer to =flights=-TABLE by using =f=
WHERE f.id < flights.id
AND f.carrier=flights.carrier) + 1
AS flight_sequence_number
FROM flights;

#3

Hi
I am a bit confused with this exercise, too. Thanks for letting me know there is a hidden 'AS' there which is an alias of flights. I guessed 'f' is a table as we are using f.id, f.carrier etc. but did not know f is acting like an alias. So, thanks again.

However, I did not understand the logic used there: (f.id< flights.id AND f.origin = flights.origin)+1, how it's counting?

#4

I second this follow-up. The WHERE conditions make no sense now.

#6

I experimented a bit with the SQL editor on this site: http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all and I think I know what's going on. So you can try it yourself, I'll copy the code I used there, which does the same thing with different names. The "ORDER BY Country" is added just so the output looks a little nicer.

SELECT Country, CustomerID,
  (SELECT COUNT(*)
    FROM Customers CustomersInner
    WHERE CustomersInner.CustomerID < Customers.CustomerID
    AND CustomersInner.Country=Customers.Country) + 1
  AS customer_seq_num
FROM Customers ORDER BY Country;

What helped me was trying the subquery on its own. If you try to run 

SELECT COUNT(*)
      FROM Customers C
      WHERE C.CustomerID < Customers.CustomerID
      AND C.Country=Customers.Country

by itself, you get an error saying that there is no column "Customers.CustomerID". What "FROM Customers C" does is not only create the alias, but it also makes the "Customers" reference invalid. Because the SQL interpreter could not find Customers in the subquery, it tries to search the next outer query for a valid reference, but if there is no outer query, it fails. (It's like the symbol resolution in JavaScript, if you are familiar with that language.) Here, I added an alias to the outer query to make it easier to see this. The statement does the exact same thing.

SELECT Country, CustomerID,
  (SELECT COUNT(*)
    FROM Customers AS CustomersInner
    WHERE CustomersInner.CustomerID < CustomersOuter.CustomerID
    AND CustomersInner.Country=CustomersOuter.Country) + 1
  AS customer_seq_num
FROM Customers AS CustomersOuter ORDER BY Country;

If anyone who knows SQL more confidently than me still wants to weigh in on this, I'd appreciate it.

#7

Thanks sarkreth. The table in your link is much simpler, the IDs are in order and short which is very helpful to understand how the counting works. By the way, I find Lesson 5/7 is very important to understand this lesson (6/7) which says:
A row is processed in the outer query.
Then, for that particular row in the outer query, the sub query is executed. I think I somehow missed " for that particular row" bit. Now, it's clear to me.

Thank you once again.

#8