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`;
This statement groups by the literal string
'a'
and does not work as you may expect:
SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name
GROUP BY 'a';