Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

B.5.5.4 Problems with Column Aliases

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column:

SELECT SQRT(a*b) AS root FROM tbl_name
  GROUP BY root HAVING root > 0;
SELECT id, COUNT(*) AS cnt FROM tbl_name
  GROUP BY id HAVING cnt > 0;
SELECT id AS 'Customer identity' FROM tbl_name;

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name
  WHERE cnt > 0 GROUP BY id;

The WHERE clause determines which rows should be included in the GROUP BY clause, but it refers to the alias of a column value that is not known until after the rows have been selected, and grouped by the GROUP BY.

In the select list of a query, a quoted column alias can be specified using identifier or string quoting characters:

SELECT 1 AS `one`, 2 AS 'two';

Elsewhere in the statement, quoted references to the alias must use identifier quoting or the reference is treated as a string literal. For example, this statement groups by the values in column id, referenced using the alias `a`:

SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name
  GROUP BY `a`;

But this statement groups by the literal string 'a' and will not work as expected:

SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name
  GROUP BY 'a';

Download this Manual
User Comments
  Posted by Alessandro Vesely on October 1, 2010
Quoted fields often result from difficulties in ordering by expressions that involve an alias. For example

SELECT group, SUM(x) AS a, SUM(y) AS b, SUM(x)+SUM(y) AS 'a+b'
FROM tbl_name GROUP BY group ORDER BY `a+b`;

The column 'a+b' is only necessary because one cannot order by a + b. Backslashes may be needed if the statement is submitted from a shell. Easier names, like sum_of_a_and_b, are much less immediate to a human reader.

Sign Up Login You must be logged in to post a comment.