Sometimes a column name returned in a query is different from what is expected or what might be needed for a script. As a simple example, consider a query that uses the MySQL function COUNT to return the number of records in a table:
SELECT COUNT(*) FROM Clients;
COUNT(*) -------- 12
While COUNT(*) might be a suitable column header for raw output, it wouldn't be so nice to work with as the key of an associative array. Instead, The AS modifier can be used to rename a results column.
SELECT COUNT(*) AS RecordCount FROM Clients;
RecordCount ----------- 12
AS only affects the column name of the returned results; it does not actually rename a table column stored within the database, but it can still be a powerful ally when writing complex SELECT queries. Consider the next example which uses the MySQL function CONCAT to combine column data.
SELECT CONCAT(LastName, ", ", FirstName) AS Name, CONCAT(Address, " ", CITY, ", ", State, " ", Zip) as Address FROM Clients ORDER BY Name ASC;
Name Address ------------------------------------------------------------- Adams, Joseph 214 Main St Seattle, WA 98102 Adams, Gwendolyn 205 W Third St Brownville, AL 35020 Brown, Michael 2336 NE Cranston Blvd Terre Haute, IN 47814 Chavez, Jose 14 Quailbush Dr Jasper, TN 37347 Copland, Dorothy 639 Spring St West Sacramento CA, 95691 Franklin, Susanna 293 Lamont Dr Tempe, AZ 85283 Johnson, Diane 82 Richardson Ave Fresno, CA 93702 LaBelle, Nathan 367 Pineview Ter Skaneateles, NY 13152 Smith, Aaron 9112 Main St San Diego, CA 92119 Smith, Harold 321 Elm St Portsmouth, RI 02871 Smith, Josh 52 Harding Ln Franklin, MA 02038 Wilson, Jessica 227 Washington Ave Irving, TX 75061
AS coerces the name of a column to something manageable or in some cases, intelligible. The column names would be quite messy indeed without AS!
