Use Sophia to knock out your gen-ed requirements quickly and affordably. Learn more
×

AS/ALIAS to Rename Tables and Columns

Author: Sophia

what's covered
This tutorial explores using AS for aliases to rename tables and columns in two parts:
  1. Table Alias
  2. Column Alias

1. Table Alias

Table aliases allow 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;

In essence, we’ve renamed the table as “c” instead of 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;

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 representative (representative_id) );

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 manager 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;

Table Alias Example


2. Column Alias

We can also create aliases for column names within the SELECT clause. This can be especially useful for expressions. You may remember our tutorials on aggregate functions, where the results would just show the function name. To a user reviewing the data, it may not always be clear what that data 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:

Column Alias Example

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:

Renaming Columns Example


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;

Unclear Column Name Example

Looking at the result set, it's not clear which email represents the customer's email and which represents the employee's email, 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;

Clarifying Column Name Example


Video Transcript

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.

summary
Table and column aliases allow us to rename the tables to simplify queries and rename the columns for output.

Source: Authored by Vincent Tran