The Takeaways

By the end of this tutorial, make sure you can answer these questions:

  • How to use the AS keyword. And how AS is optional (but recommended).

  • How to alias a column and table name.

  • How to alias the result of a function.

Get the data

For this lesson, download the following file, which is a SQLite database built from the U.S. Social Security Administration file of popular baby names:

Unzip it, and open the sqlite file using the SQLite client of your choice (e.g. DB Browser for SQLite)

Table of contents

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;
name UPPER(name) LENGTH(name)
Aaban AABAN 5

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;
name upname chars
Aaban AABAN 5

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;
whatev upname chars
Aaban AABAN 5

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;
name LENGTH(name)
Christianjoseph 15
Christopherjame 15

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;
name x
Christianjoseph 15
Christopherjame 15

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;
name count x
Michelangelo 74 12

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;
a b
Christianjoseph 15
Christopherjame 15

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.