MySQL/Language/Alias

From Wikibooks, open books for an open world
< MySQL‎ | Language
Jump to: navigation, search

An expression and a column may be given aliases using AS. The alias is used as the expression's column name and can be used with order by or having clauses. For e.g.

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

These aliases can be used in ORDER BY, GROUP BY and HAVING clauses. They should not be used in WHERE clause.

A table name can have a shorter name for reference using AS. You can omit the AS word and still use aliasing. For e.g.

 SELECT
     COUNT(B.Booking_ID), U.User_Location 
 FROM
     Users U
 LEFT OUTER JOIN
     Bookings AS B
 ON 
     U.User_ID    = B.Rep_ID AND
     B.Project_ID = '10'
 GROUP BY
     (U.User_Location)

Aliasing plays a crucial role while you are using self joins. For e.g. people table has been referred to as p and c aliases!

 SELECT
     p.name                                   AS parent,
     c.name                                   AS child,
     MIN((TO_DAYS(NOW())-TO_DAYS(c.dob))/365) AS minage
 FROM
     people AS p 
 LEFT JOIN
     people AS c 
 ON
     p.name=c.parent WHERE c.name IS NOT NULL
 GROUP BY
     parent HAVING minage > 50 ORDER BY p.dob;