{"id":20750,"date":"2021-01-13T10:52:23","date_gmt":"2021-01-13T18:52:23","guid":{"rendered":"https:\/\/careerkarma.com\/blog\/?p=20750"},"modified":"2023-12-01T04:08:03","modified_gmt":"2023-12-01T12:08:03","slug":"sql-alias","status":"publish","type":"post","link":"https:\/\/careerkarma.com\/blog\/sql-alias\/","title":{"rendered":"SQL Aliases: A Complete Guide"},"content":{"rendered":"\n

An SQL alias is a reference name for a table or a column. They are used to shorten your queries if you have long table names. Both table and column aliases are defined using the AS keyword. An alias lasts until a query has executed. Aliases are not permanent.<\/em><\/p>\n\n\n\n

Have you ever become tired of writing out long column or table names in a complex query? It happens as you learn more about SQL. This is where aliases come to the rescue. An alias will help you shorten your query.<\/p>\n\n\n\n

In this guide, we talk about what SQL aliases are and why they are used. We walk through two scenarios of an alias in action to help you understand how to use SQL aliases. Without further ado, let\u2019s begin!<\/p>\n\n\n\n

Note: This tutorial has been written using PostgreSQL.<\/em><\/p>\n\n\n\n

What is an SQL Alias?<\/h2>\n\n\n\n

An SQL alias is like human a human alias. The SQL alias assigns a placeholder name to a table or a column in a table. Aliases are useful because they help make queries and their results more readable and easier to interpret.<\/p>\n\n\n\n

A good alias is one that can be understood as quickly as possible.<\/p>\n\n\n\n

The alias n<\/em> would be confusing to describe a column called name<\/em> if there was also a column called number_of_products.<\/em> Both start with the letter n<\/em>.<\/p>\n\n\n\n

fn<\/em> may be more appropriate because it takes the first letter of each word in full name<\/em>. This alias is less likely to cause confusion.<\/p>\n\n\n\n

Remember that your SQL queries may be used by someone else, not just yourself. You should make every effort to make your queries as intuitive as possible for all readers.<\/p>\n\n\n\n

SQL Aliases: Columns<\/h2>\n\n\n\n

You use aliases on column names. Column name aliases lets you change the name of a column in the output of a query.<\/p>\n\n\n\n

Let\u2019s take a look at two tables in our example database. These databases are called employees<\/em> and branches<\/em>.<\/p>\n\n\n\n
Employees<\/strong><\/td>
<\/td>		Branches<\/strong><\/td><\/tr>
name<\/td>title<\/td>branch_id<\/td>salary<\/td>
<\/td>		branch_id<\/td>branch_name<\/td><\/tr>
Geoff<\/td>Senior Sales Associate<\/td>1<\/td>38000<\/td>
<\/td>		1<\/td>San Francisco<\/td><\/tr>
Adam<\/td>Senior Sales Associate<\/td>2<\/td>38000<\/td>
<\/td>		2<\/td>Cambridge<\/td><\/tr>
Emma<\/td>Marketing Executive<\/td>2<\/td>50000<\/td>
<\/td>		3<\/td>Los Angeles<\/td><\/tr>
Luke<\/td>Marketing Assistant<\/td>3<\/td>32000<\/td>
<\/td>
<\/td>
<\/td><\/tr><\/tbody><\/table>\n\n\n\n

The employees table has four columns and four rows. The branches table has two columns and three rows.<\/p>\n\n\n\n

Let\u2019s write an <\/a>SQL SELECT s<\/a>tatement<\/a> that tells us the highest salary earned at each branch:<\/p>\n\n\n\n

SELECT branches.branch_name, MAX(employees.salary)\nFROM employees LEFT JOIN branches\nON employees.branch_id = branches.branch_id\nGROUP BY branches.branch_name;<\/pre><\/div>\n\n\n\n
We use the MAX() SQL aggregate function<\/a> to select the highest value in the \u201csalary\u201d column of the \u201cemployees\u201d table. We use an SQLLEFT JOIN statement<\/a> to connect the \u201cbranches\u201d and \u201cemployees\u201d tables together. This lets us see the return of each branch.<\/p>\n\n\n\n
Let\u2019s see what our query returns:<\/p>\n\n\n\n
branch_name<\/strong><\/td>max<\/strong><\/td><\/tr>
San Francisco<\/td>38000<\/td><\/tr>
Los Angeles<\/td>32000<\/td><\/tr>
Cambridge<\/td>50000<\/td><\/tr><\/tbody><\/table>\n\n\n\n
Our query works. The title names of our query are not very intuitive. For someone who does not write this, query \u201cmax\u201d may be confusing.<\/p>\n\n\n\n
We solve this by specifying column alias names:<\/p>\n\n\n\n
SELECT branches.branch_name AS "Branch Name", MAX(employees.salary) as "Highest Salary"\nFROM employees LEFT JOIN branches\nON employees.branch_id = branches.branch_id\nGROUP BY branches.branch_name;<\/pre><\/div>\n\n\n\n
We specify two aliases. \u201cbranch_name\u201d gets the alias \u201cBranch Name\u201d. Our MAX() aggregate function gets the alias \u201cHighest Salary\u201d. Let\u2019s run our query again:<\/p>\n\n\n\n
Branch Name<\/strong><\/td>Highest Salary<\/strong><\/td><\/tr>
San Francisco<\/td>38000<\/td><\/tr>
Los Angeles<\/td>32000<\/td><\/tr>
Cambridge<\/td>50000<\/td><\/tr><\/tbody><\/table>\n\n\n\n
The returning rows by our query are the same. The table headings change. Now, \u201cbranch_name\u201d is \u201cBranch Name\u201d and \u201cmax\u201d is \u201cHighest Salary\u201d.<\/p>\n\n\n\n
SQL Aliases: Tables<\/h2>\n\n\n\n
You can define an SQL alias on a table. This is useful if you are going to refer to a table many times in a query. Such an event often happens in more complex queries.<\/p>\n\n\n\n
When you query multiple tables, you need to specify the name of each table before the name of the columns. The table and column name are separated with a period (.). This ensures SQL knows exactly which table each statement refers to in your query.<\/p>\n\n\n\n
While this feature protects you from making your queries ambiguous, it makes them less readable. Thus, you should make sure your aliases are not too difficult to understand.<\/p>\n\n\n\n
Let\u2019s write a query that returns the name of every employee alongside the name of the branch at which they work:<\/p>\n\n\n\n
SELECT employees.name, branches.branch_name\nFROM employees LEFT JOIN branches\nON employees.branch_id = branches.branch_id;<\/pre><\/div>\n\n\n\n
Our query returns:<\/p>\n\n\n\n
name<\/strong><\/td>branch_name<\/strong><\/td><\/tr>
Geoff<\/td>San Francisco<\/td><\/tr>
Adam<\/td>Cambridge<\/td><\/tr>
Emma<\/td>Cambridge<\/td><\/tr>
Luke<\/td>Los Angeles<\/td><\/tr><\/tbody><\/table>\n\n\n\n
We query two tables: employees and branches. We use a LEFT JOIN to connect the value of \u201cbranch_id\u201d in both tables. This allows us to see the names of each branch with which an employee is associated.<\/p>\n\n\n\n
Our query is quite long. We repeat the words \u201cemployees\u201d and \u201cbranches\u201d twice. In a longer query, we have to use these words even more.<\/p>\n\n\n\n
We use aliases to make our query shorter. We need to use the AS keyword to create a table name as an alias in an SQL query:<\/p>\n\n\n\n
SELECT e.name, b.branch_name\nFROM employees AS e LEFT JOIN branches AS b\nON e.branch_id = b.branch_id;<\/pre><\/div>\n\n\n\n
This query returns the same output as our first query.<\/p>\n\n\n\n
We assign two aliases:<\/p>\n\n\n\n