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;
Manage research, learning and skills at NCR Works. Create an account using LinkedIn to manage and organize your omni-channel knowledge. NCR Works is like a shopping cart for information -- helping you to save, discuss and share.