13

I'm not professional in query writing, but wrote many from the time I began MySQL. Recently I noticed there is no need to type AS keyword in name aliasing.

SELECT name AS n

equals to 

SELECT name n

However I know that this ability is out from years ago. I've 2 questions around this subject:

  1. Is AS keyword redundant?

  2. Sometimes ago when I encountered with a custom query on a website with having no AS in aliasing, that its executing made MySQL service down, I changed the way of name aliasing with adding AS keyword and this little change made it work!

    What was the problem here?

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
revo
  • 47,783
  • 14
  • 74
  • 117
  • Similar to http://stackoverflow.com/questions/2454951/why-would-you-use-as-when-aliasing-a-sql-table - aliasing column names is syntactic sugar that makes the query a little bit easier to read. – FrustratedWithFormsDesigner Oct 24 '13 at 13:47
  • Probably you used a version of mysql that demanded the as keyword. – Mihai Oct 24 '13 at 13:49
  • @FrustratedWithFormsDesigner and also is its both use documented officially? – revo Oct 24 '13 at 13:50
  • I asked 2 question, for the sake of second one why close votes? – revo Oct 24 '13 at 13:50
  • 1
    @revo It doesn't make any sense for the MySQL service to shutdown when there is a syntactical error in a query. Btw, there is not even a syntactical error because `AS` is NOT required. – Mosty Mostacho Oct 24 '13 at 13:54
  • @MostyMostacho I didn't say mysql service had shut down. I said service went down, but still I needed to restart it to make it available. Also it wasn't about syntactical error because mysql didn't throw an error. – revo Oct 24 '13 at 13:59
  • You should address your second question to the `service` provider. – Mosty Mostacho Oct 24 '13 at 14:07
  • @revo: I really can't explain what you experienced in your second question. Possibly there was *already* problem with the service that was only noticed when your an the query. I'd have to *see* and *reproduce for myself* something like that to believe it and explain it (mind you, I haven't used MySQL for a long time - a MySQL guru might know this one). – FrustratedWithFormsDesigner Oct 24 '13 at 14:22

1 Answers1

24

For the answer to 1), the mysql documentation http://dev.mysql.com/doc/refman/5.0/en/select.html:

A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. For example:

SELECT CONCAT(last_name,', ',first_name) AS full_name   FROM mytable
ORDER BY full_name;

The AS keyword is optional when aliasing a select_expr with an identifier. The preceding example could have been written like this:

SELECT CONCAT(last_name,', ',first_name) full_name   FROM mytable
ORDER BY full_name;

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 example, in the following statement, columnb is treated as an alias name:

SELECT columna columnb FROM mytable;

For this reason, it is good practice to be in the habit of using AS explicitly when specifying column aliases.

It is not permissible to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section C.5.5.4, “Problems with Column Aliases”.

This also very similar syntactically for tables:

A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:

SELECT t1.name, t2.salary FROM employee AS t1, info AS t2   WHERE
t1.name = t2.name;

SELECT t1.name, t2.salary FROM employee t1, info t2   WHERE t1.name =
t2.name;

For the Answer to 2) I don't think there is anyway you can tell without a full description of the software versions, query being run and error messages received.

ModulusJoe
  • 1,416
  • 10
  • 17
  • aliasing in my case was about table names and there were no error in executing query. but a good answer. – revo Oct 24 '13 at 14:02