As keyword redundant?

[Mark Smith]

Does the as keyword add any syntactic meaning to a query or is it just
for readability.

For example, what is the difference between the following?

SELECT * FROM TableName as T;

SELECT * FROM TableName T;

Thanks

["Álvaro G. Vicario"]

Mark Smith escribi�:

There's no actual difference. Manual says:

"The AS keyword is optional when aliasing a select_expr. [...] However,
because the AS is optional, a subtle problem can occur if you forget the
comma between two select_expr expressions: MySQL interprets the second
as an alias name [...] For this reason, it is good practice to be in the
habit of using AS explicitly when specifying column aliases."

http://dev.mysql.com/doc/refman/5.1/en/select.html

(Scroll down to where it says "A select_expr can be given an alias using
AS alias_name".)

--
-- http://alvaro.es - �lvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci�n web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

[Captain Paralytic]

There is no difference, but try
SELECT * FROM (SELECT * FROM tablename) t

[Taliesin Nuin]

Álvaro G. Vicario wrote:
> Mark Smith escribió:

>> Does the as keyword add any syntactic meaning to a query or is it just
>> for readability.
>>
>> For example, what is the difference between the following?
>>
>> SELECT * FROM TableName as T;
>>
>> SELECT * FROM TableName T;
>
> There's no actual difference. Manual says:
>
> "The AS keyword is optional when aliasing a select_expr. [...] However,
> because the AS is optional, a subtle problem can occur if you forget the
> comma between two select_expr expressions: MySQL interprets the second
> as an alias name [...] For this reason, it is good practice to be in the
> habit of using AS explicitly when specifying column aliases."
>
> http://dev.mysql.com/doc/refman/5.1/en/select.html
>
> (Scroll down to where it says "A select_expr can be given an alias using
> AS alias_name".)

I think he was asking more generally. In the example given, no there is
no difference. And in many other cases, it is merely for readability.
There's even less need for using an alias in the above example than
normal because it just SELECTs everything (*) from all tables
(TableName). If you had four or five fields you wanted to select from
TableName but it also had a couple that you didn't want, then it would
be a lot more concise to supply:

SELECT t1.field1, t1.field2, t1.field3, t1.field4, t1.field5 FROM
tablename t1,

than to supply:

SELECT tablename.field1, tablename.field2, tablename.field3,
tablename.field4, tablename.field5 FROM tablename

HOWEVER, there are cases where you use AS for syntactic reasons. For
example with self JOINs and sub queries, you need them.

For example, the following is valid SQL:

SELECT t1.fielda, t2.fieldb FROM mytable t1 INNER JOIN mytable t2 ON
(t1.somefield = t2.anotherfield)

and there are scenarios where you want to do such a thing. Also you can
name subqueries and JOIN on them, e.g.

SELECT t1.fielda, sq1.fieldc FROM table1 t1 INNER JOIN (SELECT fieldb,
fieldc WHERE fieldf < 42) sq1 ON (t1.fieldd = sq1.fieldc)

These are pretty useless arbitrary examples, but they show where you
would actually use AS for reasons of actually constructing a query,
rather than for making a query more readable. The self join is something
you'll run into sooner or later and when you do, you'll find that a
table needs two different names (I'm starting to sound like T.S.Eliot).

Hope this, although brief, helps answer you question?

Taliesin Nuin.

[Taliesin Nuin]

Taliesin Nuin wrote:
>it would
> be a lot more concise to supply:
>
> SELECT t1.field1, t1.field2, t1.field3, t1.field4, t1.field5 FROM
> tablename t1,
>
> than to supply:
>
> SELECT tablename.field1, tablename.field2, tablename.field3,
> tablename.field4, tablename.field5 FROM tablename

ARGHH! I'm going to correct myself before someone else (rightly) does
so. Obviously it is more concise still to just write

SELECT field1, field2, field3, field4, field5 FROM tablename

However, if you weren't selecting from a single table, but instead from
a series of tables which may have duplicated column names, then you
would need to specify which table field1 et al. were from.

E.g.

SELECT t1.field1, t1.field2, t1.field3, t1.field4, t1.field5 FROM

tablename t1, othertablename t2

Sorry - the problem with oversimplification. : /

[Mark Smith]

LOL, yeah I got what you mean't.

And yes my original question is more than answered.

Thanks!