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;
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:
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 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;
[MUSIC PLAYING] Aliases allow us to be able to rename columns as well as tables. It helps us shore up these queries. For example, in this case here, we have a select between the department table and the representative table, however, this can get quite lengthy when you have to type out department and representative each time.
To be able to create an alias for a particular table, beside the table name itself, we can just enter in the alias that we want to use. In this case here, I could just enter in d. For the representative, I'll just enter in r. So any time that we see representative for the table name, we can just replace that with an r. And anytime we see the department table, we can replace it with the d.
So you'll be able to see how quick that is to be able to visualize and make the changes to this. So if we run this, the results are going to be quite easy to set up. You can also set things up in terms of aliases for the columns as well.
In our case here, if we didn't want to have a lowercase department_name, we want to set up for a specific report, beside the specific column within the SELECT clause, we can just enter in as and then what we want to display it as. So in this case here, we just enter in double quotes "Department Name."
For the first name, we can enter in as "First Name." And then, under the last name, we can do an as "Last Name." Go ahead and run that. And so you'll be able to see that the column names have now changed to Department space Name. First Name changed, as well as Last Name has changed.
[music playing]
Source: Authored by Vincent Tran