Aliasing with AS

Column names are built for typing, not for reading: dob, sal, percent. An alias gives a column a friendlier heading in the resultset, and leaves the table exactly as it is.

1Renaming a column

MySQL command line client

mysql> select name as "Student Name", percent + 5 as "Bonus Marks" from students where grade = 12;

+--------------+-------------+

| Student Name | Bonus Marks |

+--------------+-------------+

| Anjali | 96 |

| Sohan | 71 |

| Ravi | 88.75 |

| Bhim | NULL |

+--------------+-------------+

4 rows in set (0.00 sec)

The headings read as English. Nothing in the table changed — desc students would still show name and percent. An alias lives for the length of one query.

Why the second column needs an alias

percent + 5 is not a column, it is a calculation, so it has no name of its own. Without an alias MySQL heads the column with the expression itself — a heading reading percent + 5. Any time you calculate something, name it.

Look at Bhim's row while you are here. His percentage is NULL, and NULL + 5 is NULL, exactly as the concepts chapter promised. Aliasing a calculation does not change that — it only gives the column a heading.

2When the alias needs quotes

Quotes needed

When the alias contains a space or a symbol.

as "Student Name"

Quotes optional

When it is a single plain word.

as Marks

3AS itself is optional

Leave the word out and put the alias straight after the column. MySQL accepts it, and you will meet it in exam papers written this way:

MySQL command line client

mysql> select name Student, percent Marks from students;

+---------+-------+

| Student | Marks |

+---------+-------+

| Veena | 90.5 |

| Anjali | 91 |

| Sohan | 66 |

| Lhamu | 95.2 |

| Ravi | 83.75 |

| Karma | 78.5 |

| Diana | 88.25 |

| Bhim | NULL |

| Tom | 64.25 |

| Pema | NULL |

| Farhan | 72.8 |

| Nima | 59.4 |

+---------+-------+

12 rows in set (0.00 sec)

Write the AS anyway

select name Student and select name, Student differ by one comma and mean completely different things — the second asks for a column called Student that does not exist. Writing as makes the intention unmistakable to a reader, and to you.