6

In

SELECT a.NAME, a.NUMBER, a.STRING, a.RDB$DB_KEY FROM ADMIN a

what does a stand for?

Thanks.

Binary Worrier
  • 50,774
  • 20
  • 136
  • 184
jackhab
  • 17,128
  • 37
  • 99
  • 136

9 Answers9

29

a is an alias for the table ADMIN

SQL Alias

jitter
  • 53,475
  • 11
  • 111
  • 124
  • so why not simply SELECT NAME, NUMBER, STRING, RDB$DB_KEY FROM AMDIN? – jackhab Jun 15 '09 at 15:53
  • @Jack that is a perfectly valid query – Matthew Jones Jun 15 '09 at 15:56
  • 3
    @Jack: If you're referencing two tables (e.g., ADMIN_1, ADMIN_2) and they have columns in common (e.g. NAME), then your SELECT has to differentiate between the tables so it knows which one to pull from. So using a.NAME is shorter than ADMIN_1.NAME. – Adam V Jun 15 '09 at 15:57
3

The underlying concept is that of a ‘range variable’.

Chris Date and Hugh Darwen consider both the colloquial term ‘alias’ and the SQL Standard’s term ‘correlation name’ to be “inappropriate” and “seriously [misrepresenting] the true state of affairs”.

Hugh Darwen, “SQL: A Comparative Survey”:

You may have learned a different term for range variable, which was used by Codd in his early papers but not adopted by the SQL standard until 2003. In some SQL texts it is called alias but this is not at all appropriate, really, because that would imply that it is a table name and therefore denotes a table rather than a row. The SQL standard uses the equally inappropriate term correlation name (it doesn’t denote a correlation, whatever that might be), but only for the case where the name is explicitly given (via AS in the example) and not for the case where a simple table name doubles as a range variable name. In SQL:2003 range variable was adopted as a convenient single term to cover the more general case.

C. J. Date, “SQL and Relational Theory: How to Write Accurate SQL Code”:

a range variable in the relational model is a variable that "ranges over" the set of rows in some table (or the set of tuples in some relation, to be more precise). In SQL, such variables are defined by means of AS specifications in the context of either FROM or JOIN, as in the following example:

SELECT SX.SNO 
FROM   S AS SX 
WHERE  SX.STATUS > 15

SX here is a range variable that ranges over table S; in other words, its permitted values are rows of table S. You can think of the SELECT expression overall as being evaluated as follows. First, the range variable takes on one of its permitted values, say the row for supplier SNO = ‘S1’. Is the status value in that row greater than 15? If it is, then supplier number ’S1’ appears in the result. Next, the range variable moves on to another row of table S, say the row for supplier SNO = ‘S2’; again, if the status value in that row is greater than 15, then the relevant supplier number appears in the result. And so on

SQL requires SELECT expressions always to be formulated in terms of range variables; if no such variables are specified explicitly, it assumes the existence of implicit ones with the same names as the corresponding tables

Caveat: Many SQL texts refer to range variable names (or correlation names) as aliases, and describe them as if they were just alternative names for the tables they range over. But such a characterization seriously misrepresents the true state of affairs—indeed, it betrays a serious lack of understanding of what's really going on—and is strongly deprecated on that account.

Interestingly, LINQ correctly recognizes range variables e.g.

enter image description here

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
2

An alias for the table ADMIN. It's not necessary here, because you only have one table in your query.

When you have more than one table, and some of the columns are the same, then you need to distinguish between them. One way is to write the table name in front of the column name. E.g.,

Select ADMIN.Name, person.name from ADMIN, person where person.id = admin.id

To make this shorter, add aliases for the table names.

select a.Name, p.Name from ADMIN a, person p where person.id = admin.id
UncleO
  • 8,299
  • 21
  • 29
1

a is what is called a table alias. In the part of the query that says:

FROM ADMIN a

By placing "a" after the table name, you have created an alias that can now be used in place of the table name. Without the alias, you would need to use the table's full name in order to fully-qualify the column name(s) that you are referring to in the query.

Without the table alias, your query would look like this:

SELECT ADMIN.NAME, ADMIN.NUMBER, ADMIN.STRING, ADMIN.RDB$DB_KEY FROM ADMIN

Although since you are only selecting columns from a single table, the table name (or alias) actually isn't needed at all in this example.

Brandon Wood
  • 5,347
  • 4
  • 38
  • 31
1

A is an alias for the table.

You can change a to any valid identifier, it isn't dependant on the underlying schema. Usually it's used to differentiate fields from different tables, saves you typeing the full table name each time (makes the SQL easier to read with a short alias).

It isn't actually required in the sample you gave, 

SELECT NAME, NUMBER, STRING, RDB$DB_KEY FROM AMDIN

should work just as well

Binary Worrier
  • 50,774
  • 20
  • 136
  • 184
1

The query is using a like that just so that you don't have to write ADMIN.NAME, ADMIN.NUMBER, etc etc. If you have fifteen fields on your table and your table has a name like VPCPDEEE it gets very tiresome to type the same table name over and over.

Tad Donaghe
  • 6,625
  • 1
  • 29
  • 64
0

A is a shorthand notation (term: alias) for the table ADMIN

Matthew Jones
  • 25,644
  • 17
  • 102
  • 155
0

a = ADMIN

Equivalent: 

SELECT ADMIN.NAME, ADMIN.NUMBER, ADMIN.STRING, ADMIN.RDB$DB_KEY FROM ADMIN
NinethSense
  • 8,824
  • 2
  • 23
  • 23
0

The a is used as an alias for the ADMIN table.

When it is correct to use an alias, and of what form that alias should take can raise some strong opinions from writers of sql.

Heres a couple of stackoverflow questions on the subject.

When to use SQL Table Alias

SQL Table Aliases - Good or Bad?

Community
  • 1
  • 1
Paul Rowland
  • 8,244
  • 12
  • 55
  • 76