Table of contents

• Renaming a selected column header
• Referring to aliases
• Aliasing table names
• AS is optional

Renaming a selected column header

In the chapter on functions, you might have noticed that when SELECTing a result of a function, the header for that column – by default – was the function call itself. This tended to look messy:

SELECT name, UPPER(name), LENGTH(name)
FROM baby_names
LIMIT 1;

Using the AS keyword, we can alias the function calls in the SELECT clause to something…not so function-yish:

SELECT name, UPPER(name) AS upname, LENGTH(name) AS chars
FROM baby_names
LIMIT 1;

We can also re-name column identifiers to our liking:

SELECT name AS whatev, UPPER(name) AS upname, LENGTH(name) AS chars
FROM baby_names
LIMIT 1;

Referring to aliases

We might alias a column header for purely aesthetic reasons. But there's utility, too. Without aliases, this is how we would select the LENGTH of a name and sort in descending order by it:

SELECT name, LENGTH(name)
FROM baby_names
ORDER BY LENGTH(name) DESC
LIMIT 2;

However, if we define an alias for a column name passed to SELECT, we can then re-use that alias elsewhere in the statement:

SELECT name, LENGTH(name) AS x
FROM baby_names
ORDER BY x DESC
LIMIT 2;

We can also reuse an alias in the WHERE clause; the query below looks for the longest name among names which have been bestowed upon at least 50 babies in a given year, state, and sex:

SELECT name, count, LENGTH(name) AS x
FROM baby_names
WHERE count >= 50
ORDER BY x DESC, count DESC
LIMIT 1;

Aliasing table names

We can alias table names too, though there's really no effect for what we've done so far. But just be aware that it can be done because it will be very useful when we learn how to join multiple tables together:

SELECT *
FROM baby_names AS some_other_name_I_like;

AS is optional

I highly recommend that you always use AS for clarity of reading. But technically, the interpreter doesn't require it. So you will see code in the wild that looks like this:

SELECT name a, LENGTH(name) b
FROM baby_names
ORDER BY b DESC
LIMIT 2;

Omitting the AS saves us the time of typing in those two characters and removes a little clutter from the query – for the tradeoff, in my opinion, of readability. To paraphrase a well-known aphorism among programmers: "You will spend far more time reading your own code than writing it." So the time you save in writing your code will generally not offset the time lost when trying to re-read it and figuring out what the heck you were doing.