AS/ALIAS to Rename Tables and Columns

1. Table Aliases

A table alias enables us to assign tables or columns new names when a query is running. For a table in a SELECT statement, it could look like this:

SELECT *
FROM customer as c;

Essentially, we’ve renamed the table “c” instead of the customer. This can be very useful for making our queries easier to read when we start to join tables together, especially when we prefix our tables. For example:

SELECT album.album_id, artist.artist_id, track.track_id, album.title, artist.name, track.name
FROM track
INNER JOIN album USING (album_id)
INNER JOIN artist USING (artist_id)
ORDER BY album.album_id, artist.artist_id, track.track_id;

By using a shorter alias name for the tables, we can simplify the query, making it easier to read the column list:

SELECT al.album_id, ar.artist_id, t.track_id, al.title, ar.name, t.name
FROM track as t
INNER JOIN album as al USING (album_id)
INNER JOIN artist as ar USING (artist_id)
ORDER BY al.album_id, ar.artist_id, t.track_id;

key concept

When defining table aliases, you do not have to put the alias names in quotation marks unless they contain spaces. If you do use quotation marks, they must be double ones ("). Using single quotes will generate an error.

In our case, the table names are quite short. But imagine if much longer table names are used in the database. Alias names would be much easier to follow. Let’s revisit the representative and department tables we created previously to see an example:

CREATE TABLE representative ( representative_id INT PRIMARY KEY, first_name VARCHAR (30) NOT NULL, last_name VARCHAR (30) NOT NULL );
CREATE TABLE department ( department_id INT PRIMARY KEY, department_name VARCHAR (100) NOT NULL, manager_id INT, constraint fk_manager FOREIGN KEY (manager_id) REFERENCES
INSERT INTO representative (representative_id, first_name, last_name)
VALUES (1, 'Bob','Evans'), (2, 'Tango','Rushmore'), (3, 'Danika','Arkane'), (4, 'Mac','Anderson');
INSERT INTO department (department_id, department_name,manager_id)
VALUES (1, 'Sales', 1), (2, 'Marketing', 3), (3, 'IT', 4), (4, 'Finance', null), (5, 'Support', null);

If we needed to use the prefixes for the column names, a query to list the departments and their managers would look like this:

SELECT department.department_name, representative.first_name, representative.last_name
FROM representative
JOIN department ON representative.representative_id = department.manager_id;

Through the use of table aliases, we can shorten all of the instances where we have the representative or department table names:

SELECT d.department_name, r.first_name, r.last_name
FROM representative as r
JOIN department as d ON r.representative_id = d.manager_id;

term to know

Table Alias

An alternative name assigned to a table for use in a query's result set.

2. Column Aliases

We can also create aliases for column names within the SELECT clause. Column aliases can be especially useful for expressions. You may remember our lessons on aggregate functions, where the results would just show the function name. To a user reviewing the data, it may sometimes be confusing regarding what it represents.

For example, let's look back at one of the complex queries we created to find the max totals being greater than 15 and the customer_id being between 20 and 30:

SELECT customer_id, SUM(total), MAX(total)
FROM invoice
WHERE billing_country = 'USA'
GROUP BY customer_id
HAVING MAX(total) > 15
AND customer_id BETWEEN 20 AND 30;

The result set shows the following:

We could change each of the column names so that it is clearer what each of the expressions represents:

SELECT customer_id, SUM(total) as "Sum of Total", MAX(total) as "Max of Total"
FROM invoice
WHERE billing_country = 'USA'
GROUP BY customer_id
HAVING MAX(total) > 15
AND customer_id BETWEEN 20 AND 30;

Notice that in this case, we used double quotes around the alias column names since there are spaces in them:

This makes what the columns represent much clearer to the user.

Another example is the emails of the customers and the employees that support them:

SELECT customer.email, employee.email
FROM customer
INNER JOIN employee ON support_rep_id = employee_id;

Looking at the result set, it's unclear which email represents the customer's and the employee's emails, based on the column names. Here we can use both the table and alias to simplify the query and change the column names:

SELECT c.email as "Customer Email", e.email "Employee Email"
FROM customer AS c
INNER JOIN employee AS e ON support_rep_id = employee_id;

watch

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then, enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

term to know

Column Alias

An alternative name assigned to a column for use in a query's result set.