Online College Courses for Credit

+
AS/ALIAS to Rename Tables and Columns

AS/ALIAS to Rename Tables and Columns

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a query that shows data from two tables with a foreign key relationship using aliased table names in the SELECT clause.

(more)
See More
Tutorial

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:


SELECT * 
FROM customer as c;

In essence, we’ve renamed the table as “c” instead of customer. This can be very useful when we start to join tables together to make our queries easier to read 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 much longer table names being used in the database. This would be much easier to follow. Let’s revisit our representative and department tables we created previously:


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 our expressions within the SELECT clause. This can be especially useful for expressions. You may remember our tutorials with aggregate functions where the results would just show the function name but to a user reviewing the data, it is not clear what that data represents.

For example, let us take a look back at one of the complex queries we had 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 column names to be used as we have spaces in the column names that we’ll be renaming:

Renaming Columns Example


This makes it much clearer what the columns represent.

Another example would be the emails of the customers and 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 resulting set, it is not clear which email represents the customer or the employee 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


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
The table and column aliases allow us to rename the tables to simplify queries and columns for output.

Source: Authored by Vincent Tran