1

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 ?..

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502

4 Answers4

6

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).

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
4

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 *

JonH
  • 32,732
  • 12
  • 87
  • 145
  • 3
    Not only do aliases help reduce typing, but they're also essential for differentiating between two references to the same table within a query – Mark Baker Jun 01 '10 at 16:03
3

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
gbn
  • 422,506
  • 82
  • 585
  • 676
3

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.

Simon
  • 9,255
  • 4
  • 37
  • 54