for eg...
SELECT *
FROM ( SELECT RANK() OVER (ORDER BY stud_mark DESC) AS ranking,
stud_id,
stud_name,
stud_mark
FROM tbl_student ) AS foo
WHERE ranking = 10
Here foo is present...actually what it does ?..
4 Answers
In this example, foo is a table alias. Here's how you'd use it:
SELECT foo.*
FROM ( SELECT RANK() OVER (ORDER BY ts.stud_mark DESC) AS ranking,
ts.stud_id,
ts.stud_name,
ts.stud_mark
FROM tbl_student ts) AS foo
WHERE foo.ranking = 10
SQL Server (and MySQL for that matter) will throw an error if you do not specify a table alias for a derived table (AKA inline view).
It is just an alias.
Aliases help you reduce the amount of text you may have to type out when writing queries.
For instance this:
SELECT customer.Name, customer.OpenDate FROM customer
Can be shortened to:
SELECT c.Name, c.OpenDate FROM customer c
In your example, it is a derived table (not a physical one) which now you can actually say:
SELECT foo.someField rather then SELECT *
It's a table alias/identifier for the derived query
Without it, you'll get an error because the derived table has no identifier
SELECT * FROM
( SELECT RANK() OVER (ORDER BY stud_mark DESC) AS ranking, stud_id, stud_name, stud_mark FROM tbl_student )
WHERE ranking = 10
It would be better to ask: What does the expresison after "AS" mean in SQL?
As Jon said, it's an alias. You can use it instead of the expression before the AS.
SELECT veryverylongname AS V FROM dummy_table AS D WHERE D.V = 10
Here you can see two aliases V for the field and D for the table.
You can also use it to alias complete SELECT-statements as in your example. The alias will then represent the table resulting from the sub-query, and will have the fields specifiy in the subquery after your SELECT-keyword.
