Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

In SQL, what is the letter after a table name in a select statement?

Tags:

sql

In

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

what does a stand for?

Thanks.

like image 556
jackhab Avatar asked Jun 15 '09 15:06

jackhab


4 Answers

a is an alias for the table ADMIN

SQL Alias

like image 99
jitter Avatar answered Oct 10 '22 06:10

jitter


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

like image 34
onedaywhen Avatar answered Oct 10 '22 06:10

onedaywhen


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
like image 20
uncleO Avatar answered Oct 10 '22 07:10

uncleO


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

like image 37
Binary Worrier Avatar answered Oct 10 '22 06:10

Binary Worrier